However, if lock contention is an issue, a trivial solution is to enable read committed snapshot isolation. XEvents can also be used, but are a bit more tricky to get them going. Your best tool for investigation is Profiler, since the Lock:Acquired and Lock:Released events are traced with all the needed details. Shared locks are held long (for the duration of the transaction) only under Repeatable Read and Searializable Read isolation levels. 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. It depends on the statement being executed, and some execution plans will use one lock acquire/release strategy others will use another one. Unfortunately the behavior is quite complex, and not publicly documented. 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. 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". 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. 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."Īssuming we're talking about a row-level lock, with no explicit transaction, at default isolation level (Read Committed), what does " read operation" refer to?
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |