Database locking is something that we should all have a good understanding of.  But like threading issues, locking issues can be difficult to troubleshoot and reproduce in different environments.  I recently came upon an issue with a batch process that opened a SqlDataReader to read a large number of data records.  This process was doing work on each record one at a time with the net effect that the SqlDataReader was open for 30 minutes or so.

    The default transaction isolation level in SqlServer is Read Committed, so that when this procedure was run, shared locks are acquired by the SqlDataReader's connection for a set of records.  This caused other processes to fail that wanted to update the same data that the SqlDataReader had locks on, or so we thought.  Are thought was then to change the isolation level the stored procedure to Read Uncommitted or no lock, so that we wouldn't be acquiring shared reader locks and blocking other process from updating.  But before we did this we wanted to demonstrate that Read Committed was an issue here.  To do this we had to figure out just what records were locked and try and update them.

    The complication in this matter is that there are several types of locks in SqlServer such as row locks, table locks, and page locks.  When we looked in query analyzer we saw something like the following when we ran the sp_lock command on spid 77:

    77    9    2135014687    1    PAG    1:3873230           S    GRANT

This id was obtained from the sp_who2 'active' command.  This indicates a page lock, so the command was locking a page of records.  Now in our schema for this table a page holds from 500 to 1000 records so potentially this many records were locked simulatenously.  This can be found from the command dbcc show_statistics which takes a table and index as arguments. 

    Now we wanted to update a record in this group that was blocked to demonstrate failure, because when we just updated a record that we guessed was locked it failed.  We found the following command which helped us read the contents that lived on that page to figure out what records were actually locked.  After looking around I found information on the DBCC Page command.  And issued a command like so,

DBCC TRACEON(3604,1)
DBCC PAGE('DEV', 1, 3873239,3)

The result included the information we needed to identify the records and try and update which failed as expected.  The Sql Server documentation on locking is a bit poor, IMO.  The confusion is with there description of the shared locks and update locks.  A read statement like we were using obtained shared page locks.  These allow others to simultaneously read the same records.  But another connection can also obtain an update lock.  This lock declares an intention to do an update, but you still can't update a record until all the shared locks on the record are gone and the update lock is converted to an exclusive lock. 

Some of the lessons learned were to be aware of locking and concurrency.  In particular, in some cases for large batch processes read uncommitted is appropriate.  Another solutions would have been to go through and read all the records from the SqlDataReader into memory without doing any other processing and then looping through an in memory structure after the reader and connection are closed.  This is essentially what a DataSet does for you behind the scenes.