Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
SQL Server implements different types of locks that determine how concurrent query requests access the data. For example, imagine that one user wants to read the same record, the other wants to change it.
Will one be able to read the other while trying to change? Or will one change the other while reading?
You can find detailed information about lock types in the table below. The behavior of the lock types depends on the isolation level. Details of the Isolation Levels can be found in the following articles.
You can access detailed information on how lock types are prevented by others in the article named “Lock Compatibility On SQL Server“.
| Shared (S) Lock | It is just the kind of lock that is locked while reading data. For example Select. The lock is released as soon as the data reading is finished. I can say the most innocent lock variety. Usually, nobody even feels that this lock is put down. In “pessimistic concurrency control”, Shared Lock allows a record to be selected by more than one concurrent session. You may want to read the article “Optimistic and Pessimistic Concurrency Control“. |
| Update (U) Locks | It is a kind of lock that is put in place to prevent the deadlock problem. At the same time, “Update Lock” can be placed in the same resource only by one transaction. Transaction will update the Update Lock to Exclusive Lock when updating. |
| Exclusive (X) Locks | It is the type of lock that completely locks. When this lock is set, no other transaction can change that resource. Only select clauses that use the WITH (NOLOCK) hint can access this resource. WITH (NOLOCK) is a hint that is placed at the end of select statements and allows the select’s isolation level to behave like READ UNCOMMITTED. You can learn about the Isolation Levels from the links I shared at the beginning of the article. |
| Intent Locks | We have already told you that if a lock exists in one resource and another transaction wants to lock it, but the lock type of the first transaction that puts the lock is not compatible with the lock type of the second transaction, then the second transaction must wait for the first lock to finish. But while waiting, he puts an intent lock on the source. So it owns the next lock. And it prevents other transactions from locking the resource. There are several types of Intent Lock: Intent Shared (IS): It is the type of lock that indicates that you are reading some of the source that is currently in the locked state. When the lock is over, it puts the Shared Lock on the source and reads the data it needs. It does not remove Intent Shared Lock when it puts Shared Lock. When the job is finished, two locks will be removed. Intent Exclusive (IX): It is the type of lock that indicates that you are going to change some of the source that is currently in the locked state. When the lock is over, it puts the Exclusive Lock on the source and change the data it needs.It does not remove Intent Exclusive Lock when it puts Exclusive Lock. When the job is finished, two locks will be removed. Shared With Intent Exlusive (SIX): It is the type of lock that indicates that the source that is currently in the locked state will read the entirety and change some of it. |
| Schema | The lock type is set when there is an operation on the schema. |
| Bulk Update (BU) | BU is the lock type that is set when bulk copying is done with tablock hint. |
| Key-range | When serializable transaction isolation level is used, the lock type that locks the range of rows that a select fetches when a query selects. |
You can see the locks on the database with the command below. In Request Status, it writes GRANT or WAIT.
If the GRANT is written, then the transaction has the lock. If WAIT is written, it means waiting to put the lock.
1 2 3 4 5 | SELECT OBJECT_NAME(p.OBJECT_ID) AS TableName, resource_type, resource_description,request_mode,request_status,request_session_id FROM sys.dm_tran_locks l JOIN sys.partitions p ON l.resource_associated_entity_id = p.hobt_id WHERE l.resource_database_id = DB_ID() |
wonderfull wonderfull