Reputation: 5462
I've found two IMHO inconsistent descriptions on how ReadCommitted
works, both in MSDN documentation:
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.
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
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