CODEWITHSUNDEEP
sql-server
YMC
YMC

Reputation: 5462

How does SQL Server ReadCommitted Isolation Level really work?

I've found two IMHO inconsistent descriptions on how ReadCommitted works, both in MSDN documentation:

  1. https://msdn.microsoft.com/en-us/library/ms173763.aspx

    Specifies that statements cannot read data that has been modified but not committed by other transactions.

  2. https://technet.microsoft.com/en-us/library/aa259216%28v=sql.80%29.aspx Specifies that shared locks are held while the data is being read to avoid dirty reads, ...

I'm confused: in case of ReadCommitted, does SQL Server put locks while you READ or while you WRITE uncommitted data? Please clarify who knows for sure

Upvotes: 3

Views: 51

Answers (1)

usr
usr

Reputation: 171246

Writes always X-lock. Always. This is so that rollbacks are guaranteed to work. Rollbacks needs known and stable data.

About reads: RC guarantees that no uncommitted writes become visible to RC readers. RC can be thought of as taking short-lived S-locks on data being read. There is one exception to this, though: SQL Server has an optimization in that it does not take S-locks on rows that are on unmodified pages. This means that you can read rows under RC that are X-locked in other transactions(!). This fulfills the contract of not reading uncommitted data just fine.

Example: If there is a writing tran that has modified any subset of the database, it is 100% certain that RC readers will not perceive those changes until the writer commits.

The guarantees of RC are so weak that it usually can only be used in cases where you essentially don't care about data consistency deeply.

Upvotes: 1

Related Questions