Is lock grant in SQL Server First-In-First-Out (FIFO)?
Many people thought that lock grant in SQL Server was FIFO to avoid starvation problems. Think about it. If someone is able to get in front of you in a super market checkout queue, you may never be able to get out of the store.
My friend Santtu who is the lock manager expert told me that SQL Server is actually smarter than that. FIFO has one disadvantage. It does not always allow the maximum concurrency. The lock manager in SQL Server 2005 allows as much concurrency as possible without starvation. Here is an example:
Transaction T1 has an IX lock on table Foo. Transaction T2 runs a query on Foo and specifies the TABLOCK hint. T2 becomes blocked behind T1 because its S lock is not compatible with T1’s IX. Transaction T3 runs a select query on Foo without any hints – its IS request is granted immediately (i.e. before T2’s S request) because IS conflicts with neither IX nor S. However if transaction T4 attempts to run an update statement on Foo, it will become blocked behind T2 because its IX request is not compatible with T2’s S requests and because T2 made its request first, it has priority.
In SQL Server 2000, both T3 and T4 would have been blocked behind T2’s request.
