When are shared read locks released?
It's pretty interesting to watch actually, you may want to fire up profiler and trace the lock acquisition/release of some simple queries. I did this awhile back, it was something like:acquire page 1acquire row 1acquire row 2release row 1acquire row 3release row 2acquire page 2release page 1...
I may not be 100% correct, but that was basically the approach. So the lock is released after the row is read, or maybe more correctly it is after the next rows lock is acquired. I suspect this may have to do with keeping a consistent state for traversal.
I don't believe that it's acquiring two page level locks at the same time. I think it only appears in profiler that way because the events happen so quickly. if it occurs like you suspect, there would always be two page level locks, but when running a large query with shared lock, I sometimes see two page level locks and sometimes one through this query:
SELECT *FROM sys.dm_tran_locksWHERE request_session_id = <SPID>So, what I think is happening is:
- acquire: db shared lock, table shared lock, page shared lock
- page is read... simultaneous release lock on page AND acquire lock on next page
The result of two is that sometimes in the sys.dm_tran_lock query. I'm seeing two PAGE locks and sometimes one and a few times three.. depends on what occurs faster during simultaneous actions.
Regarding concrete documentation, I found this:
If READ_COMMITTED_SNAPSHOT is set to OFF (the default on SQL Server), the Database Engine uses shared locks to prevent other transactions from modifying rows while the current transaction is running a read operation. The shared locks also block the statement from reading rows modified by other transactions until the other transaction is completed. The shared lock type determines when it will be released. Row locks are released before the next row is processed. Page locks are released when the next page is read, and table locks are released when the statement finishes.