Convergence: SQL Server: Understanding locking

WaldoWaldo Member Posts: 3,412
edited 2007-03-25 in SQL Performance
Luc and kriki asked me to post the convergence sessions about sql performance in this forum... :)

During this session, the speaker used a lot of diagrams and graphs, which is quite difficult to put into words ... .
There are numerous lock types:
X: exclusive
S: shared
IS: intended shared
Some locks are compatible with each other, some are not. E.g.: if you put an exclusive lock, you won't be able to read the data with even an "IS" lock. In "Books Online" you can find all locking types and its compatibility.

Furthermore a talk about Lock Escalation.
This is used to lower the number of locks taken by a transaction. Lock manager attampts to replace one transaction's many row or page locks with a lingle table lock! You can monitor Lock escalations with Hynek's SQLPerform Analysis tool ... which I use .

A hint he gave was: when reporting, make sure you're not locking! (Duh..) This should be done by the Isolation Level hint: NOLOCK ... But I doubt we can do this in C/SIDE, because those locking hints can be specified using SELECT, INSERT, UPDATE, DELETE ... But we can't "influence" these statements. Luckily, because his recommendation was not to use locking hints, and let SQL Server decide what to use.

Then, he began explaining (again) what a deadlock is. You probably know this: it's just that two users are waiting for each other. User A modifies X, User B modified Y, User A wants to modify Y, but is locked, user B wants to modify Y and is locked as well ... . It's as easy as that. SQL Server decides who the victim is .. But I missed that part by typing the above .

Locking and blocking:
Blocking occurs when two connections need incompatible locks on a resource (page, row, table, etc.) . Keep attention: blocking is normal! It's necessary to guarantee data integrity. When is it a problem? When users complain :wind: .
But how to you recognize Blocking Problems??
Sp_who2 ! There is a column called "blocked by" as well.

SQL Profiler is THE tool to analyze anything in SQL Server, also for blocking.
Small tip: A high duration and low read count, usually was a blocked read.

There are some common causes of blocking:
- Long running queries or transactions
- Inappropriate transaction or transaction isolation level
- Lock granularity too high (or too low)
- Compile blocking
- Not processing result quickly or completely (cursor to the database)

How to avoid blocking:
- Keep transaction short and in one batch
- No user interaction during transactions
- Rollback when canceling
- Proper indexing
- Beware of implicit transactions
- Process results quickly and completely
- Reduce isolation level to lowest possible

Demo time
One nice tip I got from him during the demo, was the fact when you have an index on the column you're getting .. SQL can set the row lock.
To look what a user is doing
Dbcc inputbuffer(SPID) --> givers the statement what the blocking user is executing
In SQL2K: Select * from sys.syslockinfo: little ugly to read
In SQL2K5, there are datamangement views (look them up in technet). Select * from sys.dm_tran_locks: These are easier to read .

Again, a lot of information in only one hour, but nice to get you going in the right way. I'm not really convinced of the fact that all this info is useful for NAV (locking types, locking hits ... It's all something we don't have under control) ... It'll just be a matter of how to avoid blocking , isn't it?

Eric Wauters
MVP - Microsoft Dynamics NAV
My blog


Sign In or Register to comment.