SQL Server needs to be able to manage data with many different transactions happening that are reading, updating, inserting, and deleting. Whenever SQL looks at data, it needs to put a lock on the record or object to maintain data integrity and allow stable “concurrency” while the database is being used. There are several different locks that can be put on several fundamental different objects within SQL Server. Let’s look at them.
A lock that is used when reading data. The lock is “shared” with other concurrent queries to read as well but cannot be updated without a query hint such as NO LOCK. A Shared Lock will prevent DML statements from running against that record or object until that transaction has been completed. This allows concurrency to happen, multiple queries can access that record and return it back as a record for a report or internal query.
Exclusive locks are used whenever a DML statement is running as we as blocks reads on this lock. This prevents data integrity breakdowns when updates, deletes, and inserts are happening and concurrent read commands don’t see dirty data. Exclusive locks are especially important on the table level as deletes and inserts are being run. This lock completely locks down the database, table, or row though. This can become problematic if deadlocking is a risk.
This lock is used when DML statements are being run but reads can still happen. This prevents deadlocks in an instance where a read or other lock with concurrent transactions occurring, causing total record return breakdown. Take example Transaction N has a Shared Lock on Row 1 and Transaction Z has a Shared lock on Row 2. If N requests an Exclusive Lock on 2 and Z requests an Exclusive Lock on 1, these transactions no longer have anywhere to go. An Update lock doesn’t completely hold the row down as the Shared Lock can be completed by both Transactions.
Intent Locks are used to define the locking compatibility with the different locks and automatically tune SQL Server performance. Intent Locks will tell SQL Server what locks are on what Database, Table, or Row so it can make educated performance decisions without tying up Transactions. Say there is an Exclusive Lock on Row 123 and a new concurrent transaction requests a Shared Lock on Row 123. This isn’t going to work because you cannot have a Shared Lock on a row with an Excusive Lock (Exclusive Locks lock down the entire row for DML and reading). Without Intent Locks, there would be a lot of Locks clashing and causing concurrency and data integrity issues without any data even being damaged.
A Schema Lock happens when a database object on the table level is going through a DDL operation such as adding a column. Because these objects are dependent on the Schema, the Schema must be protected. With that said, locks that are lower than the Schema are not affected and will execute in the order that they are obtained and technically ranked (ex: adding a column on a table that is being queried by a WHERE clause that isn’t dependent on that new column while a Shared Lock is on the table).
Bulk Locks occur when a bulk load happens on a table and the TABLOCK hit is used. If so, concurrent bulk update transactions can be run at the same time and block any other transactions or bulk loads that are not using TABLOCK or table locking on bulk load.