SQL Server: Locking cost, concurrency cost and concurrency issues [Discussion on Transactions - III]

Continuing notes related to the discussion....

Another area that was discussed is, how SQL Server places locks for transactions and whether the cost of transaction goes up based on the locking mode used. What exactly happens when we query, when we execute an action query? By default, SQL Server decides what type of locking mode should be used for our query, whether it should be shared or exclusive, and the type of resources should be locked: RID, Pages, Tables, Database, etc.

If SQL Server has to maintain many number of locks, the maintenance cost goes up and it might affect to the performance too. However this might increases the concurrency. For example, assume that we update 6,000 records in a table. Considering many factors, SQL Server might first obtain fine-grained-locks (row or page level locks) only for records that are being updated. This makes sure that other records in the table are not locked and can be accessed by other users, increasing the concurrency, reducing the concurrency cost. However, the cost of maintaining 6,000 locks may be very high. If SQL Server detects that the cost is higher than expected, it tries to escalate the fine-grained-locks to more coarse-grained-locks (table level locks), decreasing the the locking cost and increasing the concurrency cost (more and more users will be blocked). Below diagram shows this;

This code shows how locks are obtained for a transaction that updates 5,000 records;

-- starting a transaction
BEGIN TRAN;

-- update 4000 records
UPDATE TOP (4000) InternetSalesTest
 SET DiscountAmount = SalesAmount * (5/100)
WHERE MONTH(OrderDate) = 5;

-- check locks
sp_lock;

Since only records that are being updated are locked, users can access other records without any issue. This increases the concurrency reducing the concurrency cost. However this increases locking cost.

When 6,000 records are getting updated;

-- rollback the previous transaction
ROLLBACK TRAN;

-- start a new transaction
BEGIN TRAN;

-- update 6,000 records
UPDATE TOP (6000) InternetSalesTest
 SET DiscountAmount = SalesAmount * (5/100)
WHERE MONTH(OrderDate) = 5;

-- check locks
sp_lock;

Since the entire table is locked now, it decreases concurrency increasing concurrency cost. No other users can access the table though locking cost is low now.

This is changeable however it is always recommended not to change the default behavior unless you know what you exactly do.

We discussed about concurrency issues too. There are four well known issues related to concurrency which can be addressed with changing locking mechanism by applying Isolation Level. There are Dirty read, Lost updates, Inconsistency Analysis, and Phantom reads.

I have written an article on this in 2011, you can have a look on it at: http://www.sql-server-performance.com/2007/isolation-levels-2005/