Concurrency Series: Common Concurrency Issues with Applications
As part of my role as a program manager in SQL Server Engine, one of the areas I am reponsible for is the concurrency related features in SQL Server. If you have been involved with database applications as a develeoper or as a DBA in keeping the application running, I am sure you will agree that blocking is one of the most interesting and challenging problems. I have seen cases where an application was running fine yesterday but it starts blocking suddenly. The culprits can be many such as lock escalation or query plan changes or just increased seasonal user workload.
If you look at the concurrency theory, it is actually quite simple for most part. SQL Standard defines 4 isolation levels (read uncommitted, read committed, repeatable read and serializable). While SQL Standard does not dictate how these isolation levels need to be implemented, but most common implementation is to build them using 'Locks'. SQL Server provides you a choice using either the lock based or row versioning based (non blocking) implementation for the 'default' read committed isolation level.
Each vendor defines different lock types and corresponding lock compatibility matrix. The locking modes/compatibility matrix is intutive in most cases. For example, it is easy to understand that two transactions that need to modify the same row need to be serialized (i.e. one transaction must complete before other is allowed to modify the row). You may ask 'oh really?' what if one transaction is modifying column1 and other transaction is modifying column2, we should allow them to proceeed? This can be possible if locking granularity at a column level is supported but that won't come for free (i.e. takes more resources and complexity). As you can guess, there is trade off betweek concurrrency and resource consumption. If you use lower locking granularity, you will get more concurrency but with more overhead and possibly more deadlocks.
Before I get buried in these details, I want to spell out the modest goal of my subsequent blog(s) on this topic. I want to take some very simple examples to illustrate the common blocking issues I have seen with customers and suggest some ways how these can be addressed. I will try to propose solutions, when possible, that don't require any application changes. But clearly, this is not always possible. Hopefully once we are done with the series of blogs on concurrency, I will consolidate the learning into some general guidelines.