How does SQL Server ReadCommitted Isolation Level really work?
I found two inconsistent descriptions IMHO about how it works ReadCommitted
, as in the MSDN documentation:
-
https://msdn.microsoft.com/en-us/library/ms173763.aspx
Indicates that operators cannot read data that has changed but has not been committed by other transactions.
-
https://technet.microsoft.com/en-us/library/aa259216%28v=sql.80%29.aspx Indicates that shared locks are kept while reading data to avoid messy reads, ...
I'm confused: in the case ReadCommitted
, SQL Server puts locks while you are READ
or while you are WRITE
uncommitted data? Please clarify who knows for sure.
source to share
Always writes X-lock. Is always. This will ensure the rollbacks work. Rollback requires known and stable data.
Read About: RC ensures that no uncommitted records are visible to RC readers. RC can be thought of as adopting short-lived S-locks when reading data. However, there is one exception: SQL Server is optimized in that it does not accept S-locks on rows that are on unmodified pages. This means that you can read lines under RC that are locked by X in other transactions (!). This fits with the contract that you don't read uncommitted data just fine.
Example. If there is a transcript of a record that has modified any subset of the database, then you are 100% sure that RC readers will not accept those changes until the record is complete.
RC guarantees are so weak that they can usually only be used in cases where you essentially don't care about data consistency.
source to share