Closing the Loop on Locking

When optimizing an OLTP database, one of the key performance issues is to isolate the writes from the reads. We’ve been talking about using Immutable data as a way to keep reads from blocking writes, by reading without locks, etc. Today I want to talk about another method that works rather well.

Even if you use database locking, and have repeatable read locks on a table, you can have good performance if you are able to isolate the read locks from write locks, so that they don’t overlap. One way to do that is to create a database where you don’t update data. Ok, that sounds like the whole immutable concept we have been talking about, and it is pretty much the same.

But, I want to share an old war story about how we had to do this in the old days of SQL Server Version 6. In older versions of SQL Server there was no concept of row level locking. The only levels of locking available were page, extent and table. In Version 6 of SQL Server they introduced row level locking on insert transactions only.

I was working for a group that developed a high volume auto auction system based on SQL Server at a time when WinTel systems were not common for heavy loads. Our database was a bottleneck we solved with the help of a couple really smart DBAs (from whom I learned a ton of stuff). They first modified our schema so that we primarily did insert transactions. This had a huge benefit right off the bat. Prior to that, we increased our transactional tables to require an entire page each. Since page level locking was the smallest amount available, having a record size
of a page resulted in virtual page level locking.

Another thing that happens with any locking in a database is the escalation of locks to reduce overhead. If it starts out with row level locking, and the locks increase where the management of those locks takes too many resources, SQL Server will escalate that lock to the next level up. Row level locks escalate to a page, then to an extent, and finally to an entire table. So now you have two goals for optimization.

1) If you are reading a few records, try and design your system such that any locks will be at a row level, reducing the probability of contention with other queries.

2) If you are reading a lot of records, try and design your system such that those reads are not in the same area of disk where data is being written. This way, if the locks escalate to pages or extents, it is not blocking your writes at the same time.

Microsoft recommends having a clustered index on a sequential value for tables. They have optimized the engine for that design. If you think about locking you may find that this design helps with the separation of reads and writes. All new writes are at the end of the table. Most likely reads are for older data, which is earlier in the table, and possibly having no overlap with the new data being inserted. Now, if you design you database to prefer Inserts over Updates, you’ll find much less contention and deadlocks, at least in theory, with your read processes.

What about in memory tables or SSDs? Does that change things? Well it only changes things in that those hardware implementations run faster. However, you still have locking, and lock management. So, if you optimize your data within your SQL Server structures, regardless of the storage implementation, you reduce the amount of contention leading to Blocking or Deadlocks.



  • Mark Armstrong

    There was an article written by this guy named Ben Taylor on September 16, 2016 called “An Easier Way to Learn CQRS”.

    We always come full circle on this issue. This conversation on locking is basically the same conversation we’ve been having for decades. It’s just salted with immutability this time around.

    From CQRS to Azure Service Bus we find ourselves landing in the same place when talking about locking and that is the segregation of reads and writes.

    However you get there is up to you…

    • Ben

      It’s interesting that we keep coming around to the same problems. Still, Immutable data, and use of INSERTS only with versions instead of delete is really a different issue than CQRS. It enables optimized performance of CQRS from a schema standpoint. I would contend they are not the same thing…but compliment each other nicely.

      I have also found that many readers don’t go back and see what I have written historically. So, I return to topics from time to time, meeting the needs of new subscribers.

      That’s a great find!

      • Mark Armstrong


        Never meant to say they were the same thing. But CQRS does mean that the data models are different for querying and updating.

        I was referring more to the mitigation of locks. CQRS does a lot more for you in this regard. We sort of conflated locking with versioning and the conversation got kind of muddled.

        With CQRS you wouldn’t even need to use the versioning capabilities of SQL Server because you would bake that into your architecture.

  • Mark Anderson

    how to you redisgn a schema to be mostly inserts? Surely users still need to read data?