Where can I find a good introduction on SQL locking and transaction strategies

Question

I'm using locks and transactions a bit like a VBA excel programmer trying to write a multithreaded c++ server for the first time...

I've tried to ask my coworkers for advice, but while we're all quite good (or so we think) at designing complex databases, writing fast and efficient queries, using index and constraints when it's needed, and so on, none of us has a good knowledge of this topic.

All the online resources I've found are either syntaxical references, or dummy tutorials explaining that a transaction begins with 'begin tran' and ends with a commit or with a rollback.

I've browsed SO too without success.

What I'm looking for is a list of simple real world problems, along with the right way to solve them.

Example :

Let's say I've got a table with one Active bit column, and that I don't want to have two active rows at the same time. Of course, many processes can try to insert data at the same time.

• should I lock the whole table ?
• or maybe use a data constraint so that an insert of a second "Active" row will fail ?
• or use a transaction with the repeatable read isolation level ?

• or maybe write :

  update tbFoo set Active=0 insert into tbFoo (foo, Active) select 'foo',1 where not exists (select * from tbFoo where Active=1)

Please don't comment/answer on this specific problem and on my silly suggestions. I'm just trying to pinpoint the fact that I don't have a clue :)

Where can I find some good walkthroughs on simple yet relevant locking situations? If it makes a difference, I'm using SQL Server 2008

I'm also curious of knowing if other people feel the same way I do on this topics.

Answer 1

You need to get deep into the product specific documentation to understand this.

It is product specific, and what works in one data does not work in another. That said, for most cases careful choice of isolation level will be sufficient, it is only when you start to really push things that more use of query hints will be needed.

For SQL Server, "Inside SQL Server 2009" (MSPress) is where I got enough detail to know most of the time I do not need that level of detail (and also working with people with decades of Oracle specialisation).

Answer 2

Q&A from Chas Boyd

Kalen Delaney article

And of course, good old BOL

For SQL 2008, you can probably use MERGE to achieve the update and insert in one transaction.safe statement. I've not used it yet so I'd do one of these: - A trigger to check after the fact (so it's in the same transaction) - TABLOCKX lock for the MERGE

In this case though, check this thread (and my answer of course :)

Answer 3

The best bet in my opinion if you really need to understand how locks and transactions work in SQL server, is to find out how the SQL server engine works internally.

I found the book “Inside Microsoft® SQL Server® 2008: T-SQL Programming” to be the most comprehensive guide.

The section “Handle transactions, concurrency, and error handling” should help you with the specific issue you are having.

However i found that i learned little nuggets from all sections of the book, all of which have made life that bit easier for me when dealing with SQL server.

I know it seems almost nostalgic to buy a paper book these days, but this is the one i would recommend, as I found it difficult to get a comprehensive guide online!