OraFAQ Forum: General » Read consistency in Oracle and SQL Server

Home » Other » General » Read consistency in Oracle and SQL Server

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Read consistency in Oracle and SQL Server [message #472268]

Thu, 19 August 2010 10:14

John Watson
Messages: 8968
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:

Code: [Select all] [Show/ hide]

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

Report message to a moderator

Re: Read consistency in Oracle and SQL Server [message #472269 is a reply to message #472268]

Thu, 19 August 2010 10:33

Michel Cadot
Messages: 68749
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Previous Topic:	Oracle sues Google over Java in Android
Next Topic:	plsql deveoper role in production support

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Mar 11 07:49:43 CDT 2025