Alternative for NOLOCK in HANA

[yash_rastogi31]

Hi,

Does anyone know the alternative in HANA for a SQL query containing WITH (NOLOCK) phrase?

For example, if the SQL query is:

                                         SELECT DocNum FROM ORIN WITH (NOLOCK) WHERE DocEntry = N'1'

then what is the corresponding statement in HANA?

Regards,

Yash

[former_member184768]

Hi Yash,

I don't think HANA allows Dirty reads. Please refer to the SQL script documenation (Press F1 in HANA Studio and search for SET TRANSACTION).

The current transaction isolation levels are as follows:

<isolation_level> ::= ISOLATION LEVEL <level>

<level> ::= READ COMMITTED | REPEATABLE READ | SERIALIZABLE

Is there a reason why you need to read the uncommitted transactions.

Regards,

Ravi

[yash_rastogi31]

Hi Ravi,

Thanks for the reply.

But in our scenario, we need to dirty read from a table and then insert data in the same table, and for this NOLOCK is required.

Regards,

Yash

[former_member184768]

Hi Yash,

Do you have a real business requirement for this. Such implementation could lead to data inconsistency in the system. If the dirty transaction gets rolled back, it could lead to incorrect data which is based on the dirty data.

Dirty read is not generally recommended from the data consistency perspective. Anyway, I think in HANA it is not yet supported.

Regards,

Ravi

[yash_rastogi31]

Hello Ravi,

I agree that dirty read may lead to data inconsistency, but in my scenario I just need it.

I don't think we can do anything in case HANA doesn't support it.

Regards,

Yash

[lbreddemann]

Hi Yash,

correct, SAP HANA doesn't support dirty reads.

Could you please shed some light on the actual use case?

I'd really be interested in learning a proper use case for this, when multiversion concurrency is available.

- Lars

[tomstone098]

The need for WITH (NOLOCK) is simply to not bog down the lock manager with "shared read locks" which could prevent another users ability to get an exclusive page or table lock.