Home » Other » General » Read consistency in Oracle and SQL Server
Read consistency in Oracle and SQL Server [message #472268] Thu, 19 August 2010 10:14 Go to next message
John Watson
Messages: 8622
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
Michel Cadot
Messages: 67997
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
Previous Topic: Oracle sues Google over Java in Android
Next Topic: plsql deveoper role in production support
Goto Forum:
  


Current Time: Wed Oct 20 10:44:59 CDT 2021