Read consistency in Oracle and SQL Server [message #472268] |
Thu, 19 August 2010 10:14 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
John Watson
Messages: 8932 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
I realize that this could be classed as a question that should be asked in a SQL Server forum, if so I'm sure a moderator will say so.
I have been told that a distinguishing factor between Oracle and SQL Server is that Oracle always follows the rules for read consistency, but SQL Server doesn't. For instance, if you want to update the salary of every employee who has salary less than his department's average so that it equals the average, then Oracle has no problem with read consistency for the correlated subquery, such as:
update emp e set sal=(select avg(a.sal) from emp a where a.deptno=e.deptno)
where sal < (select avg(a.sal) from emp a where a.deptno=e.deptno);
but if you run this in SQL Server, you may get different (and inaccurate) results depending on the order of the rows and the execution plan. Can anyone confirm this? On current or past versions of SQL Server?
Regards,
The SQL Server Nemesis
|
|
|
Re: Read consistency in Oracle and SQL Server [message #472269 is a reply to message #472268] |
Thu, 19 August 2010 10:33 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/102589.gif) |
Michel Cadot
Messages: 68653 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
It depends on the isolation level you choose for the query or session (in SQL Server).
In default mode, READ COMMITTED, SQL Server uses row shared locks to guarantee the "read committed" level, but the locks are taken at the moment the query execution reaches the row and not at the beginning of the statement execution.
But SQL Server also knows another READ COMMITTED level if you set the READ_COMMITTED_SNAPSHOT to ON, then you have the same result than Oracle read consistency (but implemented in a different way).
Regards
Michel
|
|
|