Convergence: SQL Server: Understanding locking

Waldo

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?

DenSter

This was probably the most useful SQL Server session, to get an understanding of the locking mechanism in SQL Server. There's not a whole lot that we can do about it, but now I might be tempted to start using the isolation level in my programming and see if that can make a difference.

Marije_Brummel

There is like almost nothing you can do about the isolation level in NAV.

The default for reading is readuncommitted and when you do LOCKTABLE it hints UPDLOCK. That is about it.

What is very interesting is that when using the wrong index or the wrong clustered index NAV locks more records than you might imagine.

What also can be very frustrating is lockescalaton. #-o

When you have enough memory in your SQL machine you can use the Always Rowlock option to prevent this.

Good lock. (luck)

DenSter

Hit F5, scroll down two lines past SYSTEM and you'll find TRANSACTIONTYPE. I have not read anything about this, but that's what I was thinking about doing when I was in that session.

Marije_Brummel

This is only a way to force either one of the two options

DenSter

So you are saying that we have like 5 isolation levels in C/AL code at our disposal, but when you actually use them, they don't trickle down to the SQL Server cursor?