When do shared read locks open?

Question

When SQL Server Books says that “Locked (S) resource locks are released as soon as the read operation completes , if the transaction isolation level is not set to repeat reading or higher, or the lock pointer is used to preserve the total (S) lock for the duration of the transaction.

Assuming we are talking about row level locking without an explicit transaction at the default isolation level (Read Committed), what does a read operation mean?

• Reading a single row of data?
• Reading one page of 8k IO?
• or until the complete Select statement in which the lock was created is completed, regardless of how many other rows are involved?

NOTE. The reason I need to know this is because we have several second read-only select statements created by a data-level web service that creates page-level shared read locks, creating a deadlock due to a row level conflict Exclusive update locks from prcoess replication that support server updates. The select statement is quite large, with many subselections, and one database administrator suggests rewriting it to break it into several smaller statements (shorter instances), "to reduce lock time." Since this assumes that the general read locks are maintained until the completed select statement completes, if this is not the case (if the locks are released while reading a line or page), then this approach will have no effect ...

Answer 1

It is very interesting to see actually, you can run the profiler and track the capture / release of blocking some simple queries. I did this a while ago, it was something like: acquire page 1 get row 1 get row 2 release row 1 get row 3 release row 2 acquire page 2 release 1 ...

I may not be 100% correct, but that was basically the approach. Thus, the lock is released after reading the line, or, perhaps more correctly, this happens after the lock of the following lines is received. I suspect this could be due to maintaining a consistent state for a workaround.

Answer 2

I do not believe that it simultaneously acquires two page-level locks. I think this only appears in the profiler because events happen so fast. if this happens as you suspect, there will always be two page-level locks, but when I run a large query with a common lock, I sometimes see two page-level locks, and sometimes one through this request:

SELECT * FROM sys.dm_tran_locks WHERE request_session_id = <SPID> 

So, I think what happens:

• acquire: db shared lock, shared table lock, shared page lock
• page read ... blocking simultaneous blocking on page And get blocking on next page

The result of the two is that sometimes in the sys.dm_tran_lock request. I see two PAGE locks, and sometimes one and several times three .. depends on what happens faster during simultaneous actions.