When are shared read locks released?
Asked Answered
S

3

12

When SQL Server Books online says that "Shared (S) locks on a resource are released as soon as the read operation completes, unless the transaction isolation level is set to repeatable read or higher, or a locking hint is used to retain the shared (S) locks for the duration of the transaction."

Assuming we're talking about a row-level lock, with no explicit transaction, at default isolation level (Read Committed), what does "read operation" refer to?

  • The reading of a single row of data?
  • The reading of a single 8k IO Page ?
  • or until the the complete Select statement in which the lock was created has finished executing, no matter how many other rows are involved?

NOTE: The reason I need to know this is we have a several second read-only select statement generated by a data layer web service, which creates page-level shared read locks, generating a deadlock due to conflicting with row-level exclusive update locks from a replication process that keeps the server updated. The select statement is fairly large, with many sub-selects, and one DBA is proposing that we rewrite it to break it up into multiple smaller statements (shorter running pieces), "to cut down on how long the locks are held". As this assumes that the shared read locks are held till the complete select statement has finished, if that is wrong (if locks are released when the row, or the page is read) then that approach would have no effect whatsoever....

Spindlelegs answered 2/6, 2009 at 21:7 Comment(2)
This is quite a technical question concerning the SQL Server Storage Engine. This post may be better served over at ServerFault.com as I know there are some very clued up SQL Server Administrators there.Engineman
This is a great question. I am migrating data from a large table and was wondering what the consequences would be of "streaming" all rows from the source to the destination with a single query.Ptyalin
E
4

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 1 acquire row 1 acquire row 2 release row 1 acquire row 3 release row 2 acquire page 2 release 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.

Excisable answered 2/6, 2009 at 21:15 Comment(4)
Thanks, by any chance, do you have a reference where this behavior is documented?Spindlelegs
@Charles, I have not come across any documentation that outlines this. But as you say, the profiler output is pretty conclusive :) I recommend you go through that exercise if you are curious.Excisable
@hainstech, I plan on doing exactly that, when I get a few spare minutes on my home office machine... Unfortunately, at this client's site, (gov agency) I do not have access to profiler on any running Sql Server)Spindlelegs
@IanWarburton I'm afraid I haven't used mssql or profiler in a few years, but IIRC I just looked at Lock:Acquired and Lock:Released events in profiler (learn.microsoft.com/en-us/sql/relational-databases/…) while running basic table queries.Excisable
A
1

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_locks
WHERE request_session_id = <SPID>

So, what I think is happening is:

  1. acquire: db shared lock, table shared lock, page shared lock
  2. 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.

Arango answered 13/1, 2012 at 7:47 Comment(0)
P
0

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.

Source: https://learn.microsoft.com/en-us/sql/t-sql/statements/set-transaction-isolation-level-transact-sql?view=sql-server-2017

Ptyalin answered 7/1, 2019 at 17:9 Comment(0)

© 2022 - 2024 — McMap. All rights reserved.