1

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....

1
  • Interesting question. Unfortunately I don't know enough about the inner-workings of SQL Server to navigate the locks across sub-queries. Depending on your application and its needs you may be able to use the "WITH (NOLOCKS)" hint on some of those sub-queries (or the entire thing) to prevent locking at all. This assumes that dirty-reads would be acceptable for your application. Use with caution. May 25, 2010 at 20:21

3 Answers 3

2

Unfortunately the behavior is quite complex, and not publicly documented. It depends on the statement being executed, and some execution plans will use one lock acquire/release strategy others will use another one. but as a general rule S and IS locks are held for a short times and during the statement execution S locks may be acquired and released at quite a frenzied rate. Shared locks are held long (for the duration of the transaction) only under Repeatable Read and Searializable Read isolation levels.

Your best tool for investigation is Profiler, since the Lock:Acquired and Lock:Released events are traced with all the needed details. XEvents can also be used, but are a bit more tricky to get them going.

However, if lock contention is an issue, a trivial solution is to enable read committed snapshot isolation. When this is turned on, the Read Committed reads no longer acquire any lock, and get the locked data from the version store in tempdb. The downside is, of course, that the version store needs to be maintained and it adds workload to tempdb.

0

"As this assumes that the shared read locks are held till the complete select statement has finished"

The first line of that doc page says: "Microsoft® SQL Server™ 2000 has multigranular locking that allows different types of resources to be locked by a transaction." (http://msdn.microsoft.com/en-us/library/aa213039%28SQL.80%29.aspx)

So resources seem to be locked by transaction - your large select query with the various subqueries would be one transaction.

Also, one would think that a select statement would place locks on all objects involved at one time, so a reliable point in time reading would be made.

0

It means that the lock can be released before the statement or transaction is finished.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .