I have the following scenario:

in a table I called FilmGenre I have the following columns

GenreID as INT, PK, Identity column
Genre as Varchar(max)

the table is currently populated with 

GenreID Genre
=================
1 Action
2 Comedy
4 Drama
7 Horror
9 Romance
10 Sci-Fi
11 Thriller
16 Adventure
17 Sports
18 Kids
19 Family
20 Mystery
21 History

I want to update the last row using the following transaction:

BEGIN TRAN
UPDATE FilmGenre.Genre
SET Genre = 'Historical Documentary'
WHERE Genre LIKE 'History'

now notice that I didn't want to commit the transaction above, and lets assume that a new transaction occurs on the same table to update that very same row:

UPDATE FilmGenre.Genre
SET Genre = 'Historical'
WHERE Genre LIKE 'History'

Now the 2nd update transaction hangs since the 1st update transaction hasn't commited yet.

now my questions are on the above scenario

1. If the page that contains the row that has been modified by the 1st update transaction, how does the 2nd update transaction know of the old value of that row in the modified page?

the first transaction puts a lock on that row - exclusive lock - and the lock is not released until the first transaction is committed. That locks prevents other users from making changes\reading the values of that row - depending on the isolation levels the sessions\database is using.

so, the key is locks that handle all t

Thank you for your reply.

Sure I understand that the row would be locked. But my question was that how does the 2nd update tran know about the old value of the row when it has been modified by the 1st update because the 2nd update just hang waiting for the 1st update to commit/rollback the change on the row? Is the old value of the row stored somewhere for the 2nd update to reckon that that is the row that it has to update too?

• Edited by Paulos Elias 20 hours 24 minutes ago

thats what I was trying to say - the transaction has exclusive lock on that row - so, the second transaction will wait until that lock has been released. the second transaction does not really care about value being changed but just the fact that it cannot access the row due to the exculsive lock being by transaction 1 .. it cannot change the value only because of exculsive lock owned by transaction 1 and which get released after the transaction 1 is committed.

Thank you for your reply.

Sure I understand that the row would be locked. But my question was that how does the 2nd update tran know about the old value of the row when it has been modified by the 1st update because the 2nd update just hang waiting for the 1st update to commit/rollback the change on the row? Is the old value of the row stored somewhere for the 2nd update to reckon that that is the row that it has to update too?

• Edited by Paulos Elias Saturday, July 04, 2015 10:41 AM

Paulos, both update statements require a full scan to locate the row(s) to be updated because the only index on the table is the clustered primary key index and the primary key is not specified in the query. An update lock is acquired on the hash of the primary key value of each row accessed during the scan. If the row qualifies, this row lock is converted to an exclusive lock, the row updated, and the lock retained until the transaction completes.  If the row doesn't qualify, the update lock is immediately released and the scan proceeds to the next row. 

The second update statement in your example is blocked by the exclusive key lock held on the GenreID 21 primary key value hash, not by the before or after Genre value.  But don't generalize that this will always be the case.

I suspect that Genre should have a unique constraint to avoid duplicate Genre values.  Now let's change the column data type to a more appropriate type like varchar(30), add a unique constraint, and repeat the same update scenario.  The first update statement can now locate rows to be updated more efficiently by a seek using the unique constraint index.  Also, the update statement now acquires key locks on both the old and new Genere values, as well as the primary key.  The second update will again be blocked, but now for a different reason - the key lock on the old Genre value.

The imporant take away here isn't so much what keys are used for locking but the importance of proper data types, constraints and indexes.  A good data model will help maximize performance and concurrency.

thanks guys that was helpful

Sure I understand that the row would be locked. But my question was that how does the 2nd update tran know about the old value of the row when it has been modified by the 1st update because the 2nd update just hang waiting for the 1st update to commit/rollback the change on the row? Is the old value of the row stored somewhere for the 2nd update to reckon that that is the row that it has to update too?

Second update transaction does not knows what the old value is and why would it know, the transaction log knows (considering you have recovery model which logs all transaction) what was value before the page was changed and what was value after it changed.

The only task of 2nd transaction is to find the page holding the record , bring the page in memory if it is not in memory Latch the page and update it and generate log records correponding to this update. Now this log record will have all *necessary* information

The rest is already explained