CODEWITHSUNDEEP
sql-serverconcurrencyisolation-leveldatabase-deadlocks
QFirstLast
QFirstLast

Reputation: 239

Why does a Serializable Isolation level lead to deadlock and concurrency issues?

I know the theory that the reads with range locks & the write locks are held for the duration of the transaction in a serializable isolation level. I can understand someone making a statement 'All things being equal, Serializable Isolation MAY, BUT NOT NECESSARILY, result in lower concurrency than at any other isolation level'. But the way all blogs/documentation state it, a lower concurrency and higher deadlock seems to be a certainty. I am trying to understand why it should be so in a practical sense in my particular case. Here are questions that I have

  1. Should a serializable isolation level cause lower concurrency if the various connections are not accessing the same set of records ? I am assuming a 'finely tuned' OLTP system where every query is a 'point query' returning only a few rows and the optimizer coming up with great plans. I am also thinking in terms of data pages and not the index pages.

  2. Why would the deadlocks increase ? Are these due to the updates to index pages ? But these can happen at any isolation level, other than perhaps snapshot isolation. So why single out Serializable Isolation level ?

I will of course take the answer which says 'you never know when your execution plans go bad based on user input. you are making a bad situation worse by setting a higher than required isolation level. So set the highest isolation level which works the best for you'

Upvotes: 2

Views: 2647

Answers (1)

Ben Thul
Ben Thul

Reputation: 32737

The short answer is that you end up taking out more and different kinds of locks as you go from read uncommitted all the way up to serializable. And at a certain level, it makes sense; how else would the database engine be able to make the following guarantee?

Other transactions cannot insert new rows with key values that would fall in the range of keys read by any statements in the current transaction until the current transaction completes.

Further on in the documentation, it says straight out that range locks are taken out. So, if you do something like select * from dbo.yourTable where ID between 1 and 50, and there are currently only 10 rows in the table (say IDs 1-10), your application won't be able to:

  • Insert any data for IDs 11-50
  • Delete/Update any of 1-10

If you need it, you need it. But know what you're getting into; the shared range locks that get placed are incompatible (read "will cause blocking") with a lot of stuff. See the Lock Compatibility Matrix for full details.

Upvotes: 3

Related Questions