In this post I’m going to talk about locks on SQL Server. Locks are necessary, they are used in all operations in the database. Don’t get confused about blocking, locking and blocking are totally different.
When we talk about lock, doing something in the database, like an update and select though will cause a type of lock. The select stantement has a lock operation called shared lock. This means you can share reads with someone else and that may not cause blocks.
SQL Server has different kinds of lock modes, such as (S) Shared, (U) Update, (X) Exclusive, (I) Intent (Sch) Schema, Bulk Update and Key-Range.
- (S) Shared lock is used in read operations.
- (U) Update to avoid potential deadlock problem.
- (X) Exclusive prevent access to a resource by concurrent transactions.
- (I) Intent prevent other transactions from modifying the higher-level resource and improve the efficiency of the Database Engine in detecting lock conflicts at the higher level.
- (Sch) Schema uses schema modification (Sch-M) locks during a table data definition language (DDL) operation.
The following table shows the compatibility of the most commonly encountered lock modes.
|Existing granted mode|
|Intent shared (IS)||Yes||Yes||Yes||Yes||Yes||No|
|Intent exclusive (IX)||Yes||No||No||Yes||No||No|
|Shared with intent exclusive (SIX)||Yes||No||No||No||No||No|