Oracle 和 TSQL 语句 NO LOCK
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8711313/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Oracle and TSQL statement NO LOCK
提问by icecurtain
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.
我有这个 T-SQL 语句，但是 SQL Server 和 Oracle 也需要使用一个语句，当然 Oracle 不识别“with (nolock)”。有没有办法让这个语句可以在两个数据库上运行。通过 Oracle 忽略“with (nolock)”或仅 Sqlserver 使用这部分语句或一种方式或编码，两者都可以理解。
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.
Oracle 做的事情有点不同，所以我不必担心表锁。
回答by Martin Smith
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
您的查询正在做不同的事情。查询的语义应该是什么？它是否应该返回max(Key)包括未提交事务的影响，如您的 SQL Server 版本所示？如果是这样的话
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
select max(Key)
from MyTable;
Is the same semantics. The syntax should work fine on both AFAIK.
是相同的语义。语法应该在两个 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,READPASThints but then you are back to needing two different queries.
相反，如果您想要最后一次提交max(Key)，则需要将 SQL Server 数据库更改为默认情况下使用已提交读快照隔离，因此它的行为更类似于 Oracle。或者，您可以使用ROWLOCK,READPAST提示实现类似的语义，但随后您又需要两个不同的查询。
