CODEWITHSUNDEEP
oracle-databaset-sql
icecurtain
icecurtain

Reputation: 675

Oracle and TSQL statement NO LOCK

select max(Key) from MyTable with(nolock)

I have this T-SQL statement but need to use one statement for both SQL Server and Oracle as well, of course the “with (nolock)” is not recognised by Oracle. Is there a way having this statement that will run on both databases. By either an Oracle ignoring the “with (nolock)” or only Sqlserver using this part of the statement or a way or coded that both with understand.

The reason why I am using No lock is because multiple users are accessing the same table and I need to find the max value during the transactions.

Oracle does things a bit differently so I do not have to worry about table locks.

Upvotes: 5

Views: 28390

Answers (1)

Martin Smith
Martin Smith

Reputation: 452988

Your queries are doing different things. What are the semantics of the query supposed to be? Is it supposed to return max(Key) including the effect of uncommitted transactions as your SQL Server version indicates? If so

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
select max(Key)
from MyTable;

Is the same semantics. The syntax should work fine on both AFAIK.

If instead you want the last committed max(Key) you would need to change the SQL Server database to use read committed snapshot isolation by default so it behaves more similarly to Oracle. Or alternatively you could achieve similar semantics with ROWLOCK,READPAST hints but then you are back to needing two different queries.

Upvotes: 5

Related Questions