1

I've read on Microsoft's site http://msdn.microsoft.com/en-us/library/ms173763.aspx

that Sql Server doesn't request locks when reading data, except when a database is being recovered.

Does it mean that Sql Server using READ_COMMITTED_SNAPSHOT/SNAPSHOT ISOLATION doesn't use shared locks at all? How is that possible?

For example, if there are 2 transactions. First transaction T1 wants to update some row. Second transaction T2 starts reading same row (this transaction is copying him to some output buffer, response buffer or whatever it's called in Sql Server). At the same time transaction T1 starts updating that row (it created versioned row first).

Isn't there a possibility that transaction T2 will read uncommited data? Remember, transaction T2 started copying that row before T1 made update, so there is no exclusive lock on that row.

Is this situation even possible and how can this be avoided without setting shared lock on that row during copying of it's data?

Marka
  • 377
  • 1
  • 4
  • 17

2 Answers2

3

Beside logical locks there are also physical latches to protect the database structures (particularly, in this example, pages). Latches protect any changes (modification of bits), irrelevant of isolation level. So even if the T1 does not acquire locks, it still needs to acquire a shared latch on the pages it reads, otherwise it would be victim to low level concurrent modifications done to the very structures it reads. T2 can modify the page containing the rows it modifies only if it obtains a page exclusive latch. Thus T1 can only see the image of the row either before T2 modified it (and therefore the row is the one T1 wants) or after T2 is complete with the modifications done to the row (and now T1 has to lookup the previous row image in the version store).

The latching protocol must be honored by all isolation levels, including read uncommitted and versioned reads (ie. snapshot and friends).

Remus Rusanu
  • 288,378
  • 40
  • 442
  • 569
  • So, every time some transaction is reading data it acquires latch on complete page during reading of data and release latch immediately after its completion? – Marka Nov 13 '12 at 10:43
  • Check out [chapter 1.4 in the Aries paper](http://www.cs.berkeley.edu/~brewer/cs262/Aries.pdf) – Remus Rusanu Nov 13 '12 at 10:56
0

Does it mean that Sql Server using READ_COMMITTED_SNAPSHOT/SNAPSHOT ISOLATION doesn't use shared locks at all? How is that possible?

It is possible because SQL Server is reading from a SNAPSHOT, which is not going to go through any change at all. It's already frozen at the state of the DB at the start of the current transaction, disregarding uncommitted transactions from other processes. This is done by SQL Server keeping a snapshot (row-versioned) copy of the record in tempdb for transactions to refer to, letting the current in-progress data/index page(s) get changed.

Isn't there a possibility that transaction T2 will read uncommited data? Remember, transaction T2 started copying that row before T1 made update, so there is no exclusive lock on that row.

The above narrative explains this already. But to illustrate (simplified):

Scenario 1:

T1: begin tran (implicit/explicit)
T1: read value (4)
T2: read value (4) -- *
T1: update value to (8)

* - This is the committed value at the time the T2 transaction started

Scenario 2:

T1: begin tran (implicit/explicit)
T1: read value (4)
T1: update value to (8)
     version of the row with the value (4) is made
T2: read value (4) -- * from the versioned row
T1: commit

* - (4) is [still] the *committed* value at the time the T2 transaction started
RichardTheKiwi
  • 105,798
  • 26
  • 196
  • 262
  • I'm not certain that when SNAPSHOT ISOLATION is turned on Sql Server copy 8K page when some row is changed. You probably refer to Database snapshot. He instead creates row version which is stored in tempdb. Any update on that row is made on original page. So, when select starts copying data, update will make change to the same location which select is copying. – Marka Nov 13 '12 at 10:27
  • I'm interested in 3rd scenario when T2 is actively reading row when T1 starts updating it. Is this scenario possible? – Marka Nov 13 '12 at 10:31
  • It's not possible. The row-versioned copy is made before the data is updated. At some point in time, both copies contain the same information, which is the correct information for a snapshot-isolated process to retrieve. – RichardTheKiwi Nov 13 '12 at 10:35