Locking and concurrency in SQL server 2008

150 pts.
Tags:
Locking and concurrency in SQL
SQL Server 2008
SQL Server 2008 Locking
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.

Answer Wiki

Thanks. We'll let you know when a new response is added.

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).

Discuss This Question: 6  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Akansha
    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.
    150 pointsBadges:
    report
  • carlosdl
    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.
    68,585 pointsBadges:
    report
  • Kccrosser
    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.
    3,830 pointsBadges:
    report
  • carlosdl
    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.
    68,585 pointsBadges:
    report
  • Kccrosser
    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.
    3,830 pointsBadges:
    report
  • saijagat

    Hi

    Can you help me understand what method of concurrency is defined on SQL between two users FIFO or some other logic

    10 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following