Locking and concurrency in SQL server 2008
In microsft SQL 2008 enterprise edition, readers block writers and writers block readers. What does it mean? Is it that on same set of records only one query be processed.
Software/Hardware used:
Software/Hardware used:
ASKED:
April 21, 2010 5:32 AM
UPDATED:
April 23, 2010 5:32 PM
Answer Wiki:
When you read data, the database puts a share lock on the affected resource (different levels exist). While a share lock exists on that resource, the data cannot be modified by other transactions. Usually (depending on the isolation level) share locks are released when the operation ends.
When a transaction modifies a resource, an exclusive lock is put on that resource and no other transactions can modify it. <b>Read operations could be permitted</b> if the 'read uncommited' isolation level is being used (or if the NOLOCK hint is applied to the read).
To see all answers submitted to the Answer Wiki: View Answer History.
But in Oracle it is mentioned that readers do not block writers and writers do not block readers.
Guess the above explaination holds true for Oracle.
In Oracle queries don’t acquire any locks, and thus they don’t have to wait to read any data, and for the same reason other transactions can modify any data being queried.
This could not be true when distributed transactions are involved.
Akansha – You should read some articles on Transaction Isolation Levels for Oracle and SQL Server, as each handles these a little different from the other.
Depending on system configuration settings, you can change the default behavior of the queries, ranging from fully serialized to “dirty read” (Oracle – similar to “nolock” in SQL Server). You can also set the isolation level by session and by individual transaction/query.
This topic is more complicated than can be discussed here. For a great discussion of Oracle isolation levels, Tom Kyte has “the” reference here:
http://www.oracle.com/technology/oramag/oracle/05-nov/o65asktom.html
For SQL Server, the SQL Server books online covers the topic quite well, or you can start with Microsoft’s MSDN reference on this topic:
http://msdn.microsoft.com/en-us/library/aa259216(SQL.80).aspx
Any broad statement, like “readers don’t block writers” is only true for specific settings of the transaction isolation levels AND when the queries are being done outside of an actual multi-statement transaction.
Kccrosser, Oracle doesn’t need to permit “dirty reads” to provide non-blocking reads. Could you describe a situation where you would get a dirty read in Oracle (without distributed transactions involved) ?
Also, as no locks are acquired to read (unless the FOR UPDATE clause is used), I would say that the read operation itself will not block other transactions from modifying the same data even if the read is executed inside a muli-statement transaction.
Carlosdl – you are correct. They say that memory is the 2nd thing to go…
We did use “set isolation dirty read” with Informix (some years ago), and we currently heavily use “(nolock)” with SQL Server to avoid lock escalation and blocking (after thorough analysis of course), but we didn’t need to do much with isolation levels with Oracle.
I have always liked Oracle best, but everything I work with today is SQL Server, so most of my Oracle comments are from memory, and I should double-check those more thoroughly.
Hi
Can you help me understand what method of concurrency is defined on SQL between two users FIFO or some other logic