Lock Scenarios Using sys.dm_tran_locks

Question

Post reply

Lock Scenarios Using sys.dm_tran_locks

Kenneth Igiri

SSCertifiable

Points: 5018
More actions
June 11, 2020 at 12:00 am

#3761456

Comments posted to this topic are about the item Lock Scenarios Using sys.dm_tran_locks
Br. Kenneth Igiri
https://kennethigiri.com
All nations come to my light, all kings to the brightness of my rising

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Misha_SQL SSCertifiable Points: 5407 More actions · Answer 1

Thank you for the article. I found it useful.

Listings 6 and 9 (it's the same code) are missing BEGIN TRANSACTION statement.

Kenneth Igiri SSCertifiable Points: 5018 More actions · Answer 2

Thanks so much for the feedback and corrects. I have updated it. I'm sure it sill reflect once the changes are approved.

Br. Kenneth Igiri
https://kennethigiri.com
All nations come to my light, all kings to the brightness of my rising

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 3

One comment. For your example #10, deleting a range of rows, you used the following

DELETE FROM EMPLOYEE WHERE FName LIKE '%Kokou%';

Because the search argument began with a %, it was not "Sargable" and would always require a scan for the execution plan. In addition, there are no statistics available as to how many rows would be returned from such a search, so SQL is forced to be conservative. That may be an alternate cause of the table lock.

Given an index over the [Fname] column a range of rows could have been described with 'Kokou%' (without a leading %). You may want to test such a case to see if it will avoid the escalation to a table lock.

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

Lock Scenarios Using sys.dm_tran_locks

Cookies on SQLServerCentral