Locking in SQL Server is not physical. Understanding such logical concepts takes a lot of time and baffles you a lot. I'm one of such victims. I'll try to make your work easy by writing what I got from my effort.
Locking is necessary to avail protection on the objects (table). It is applied when a particular operation is performed. These locks are applied at different levels in the database engine.
The following are some main types of locks applied by SQL Server frequently,
As the name says, Shared Lock (S) is a lock which can be shared by any number of transactions. It is used for just reading the data from a table. Suppose a transaction TRAN1 reads some data from a table then it applies a Shared Lock (S) on it. While TRAN1 is reading the data, another transaction TRAN2 can also read the same data by sharing the lock applied by TRAN1. Any number of transactions can read the same data by sharing the same lock.
Shared Lock (S) is applied at different levels depending on the query predicate used in the transaction. If the query predicate contains Indexed Column (Clustered or Non-Clustered or any other) then Shared Lock (S) is applied at Row Level while Intent Shared Lock (IS) is placed at Object and Page Levels. I'll write about Intent Locks in coming paragraphs of this article. Suppose the query predicate contains non-indexed column and transaction reads the data then Shared Lock (S) is applied at Object Level.
A Shared Lock (S) allows only shared locks of other transactions to be applied on the same row and it doesn't allow any other locks like Exclusive or Update.
Shared Lock (S) is released as soon as the data is read. It doesn't depend on the ending of transaction. Though the transaction is yet to be committed or rolled back it is released. So other locks like Exclusive (X) can be applied at this time.
Exclusive Lock (X) acts as a possessive wife. Shared Lock (S) allows other shared locks to be applied but Exclusive Locks (X) do not allow even another Exclusive Lock (X) to be applied. It is applied when a transaction tries to modify data in a table.
Update Lock (U) can be taken as a mixture of above both. It is used to avoid deadlock situation. It is applied when a transaction executes UPDATE statement.
Suppose a transaction TRAN1 is trying to update data as follows:
Intent Locks are different from above locks. These are applied only when query predicate contains Indexed Column. When an indexed column is contained in the query predicate, Intent Locks are applied at Object and Page levels.
Intent Exclusive Lock (IX) shares the same functionality as Exclusive Lock (X) with a difference that it is applied at Object and Page levels. It also allows other locks to be applied on the same Object and Page by other transactions. It is compatible with only other Intent Locks but not with any normal lock.
Intent Update Lock (IU) shares the same functionality as Update Lock (U). It is applied at Object and Page levels. It is compatible with intent locks only. If another transaction tries to apply one more Intent Update Lock (IU) then it allows that transaction to apply on the same page and object.
Shared with Intent Exclusive Lock (SIX) is placed at Object Level. For SIX to be applied, a single transaction should perform read as well as update operations on a table.
The following are some main types of locks applied by SQL Server frequently,
1. Shared Lock (S)
2. Exclusive Lock (X)
3. Update Lock (U)
4. Intent Shared Lock (IS)
5. Intent Exclusive Lock (IX)
6. Intent Update Lock (IU)
7. Shared with Intent Exclusive Lock (SIX)
These locks are applied automatically by SQL Server depending upon the type of columns that are being modified and the query that is running.
SQL Server applies locks at 3 levels mainly
1. Row Level
2. Page Level
3. Object Level (Table Lock)
To get information about the levels of locks, we need to know about the type of locks in advance. Now I'll go theoretically about the basics of Locking. I'll show practically about them in my coming posts. But just keep an important point in mind, Locking is a logical concept which cannot be observed like normal features. So, to see Locking, Transactions must be used.
To get information about the levels of locks, we need to know about the type of locks in advance. Now I'll go theoretically about the basics of Locking. I'll show practically about them in my coming posts. But just keep an important point in mind, Locking is a logical concept which cannot be observed like normal features. So, to see Locking, Transactions must be used.
Shared Lock (S)
As the name says, Shared Lock (S) is a lock which can be shared by any number of transactions. It is used for just reading the data from a table. Suppose a transaction TRAN1 reads some data from a table then it applies a Shared Lock (S) on it. While TRAN1 is reading the data, another transaction TRAN2 can also read the same data by sharing the lock applied by TRAN1. Any number of transactions can read the same data by sharing the same lock.
Shared Lock (S) is applied at different levels depending on the query predicate used in the transaction. If the query predicate contains Indexed Column (Clustered or Non-Clustered or any other) then Shared Lock (S) is applied at Row Level while Intent Shared Lock (IS) is placed at Object and Page Levels. I'll write about Intent Locks in coming paragraphs of this article. Suppose the query predicate contains non-indexed column and transaction reads the data then Shared Lock (S) is applied at Object Level.
A Shared Lock (S) allows only shared locks of other transactions to be applied on the same row and it doesn't allow any other locks like Exclusive or Update.
Shared Lock (S) is released as soon as the data is read. It doesn't depend on the ending of transaction. Though the transaction is yet to be committed or rolled back it is released. So other locks like Exclusive (X) can be applied at this time.
Exclusive Lock (X)
Exclusive Lock (X) acts as a possessive wife. Shared Lock (S) allows other shared locks to be applied but Exclusive Locks (X) do not allow even another Exclusive Lock (X) to be applied. It is applied when a transaction tries to modify data in a table.
Suppose a transaction TRAN1 tries to modify the data keeping the indexed column in the query predicate then it applies an Exclusive Lock (X) at the row level and Intent Exclusive Lock (IX) at Object and Page Levels. At this situation no other transaction can apply neither an Exclusive Lock (X) nor a Shared Lock (S). The applied Exclusive Lock (X) is released only when the transaction TRAN1 is ended with a COMMIT or a ROLLBACK. If any other transaction tries to apply an Exclusive Lock (X) then it has to wait until TRAN1 is completed.
Exclusive Lock (X) causes deadlock between the transactions which results in the performance degradation in the server.
Update Lock (U)
Update Lock (U) can be taken as a mixture of above both. It is used to avoid deadlock situation. It is applied when a transaction executes UPDATE statement.
Suppose a transaction TRAN1 is trying to update data as follows:
BEGIN
TRAN
UPDATE
mySampleTable
SET
ID2=104
WHERE
ID1=100
Now before updating ID2, the query optimizer has to read the row where ID1 = 100. While reading the row, it applies an Update Lock stating that this row needs to be updated. After reading this Update Lock (U) is converted into an Exclusive Lock (X) which states that no other transaction can do anything on the row. While reading the row, Update Lock (U) allows other transactions to apply a Shared Lock (S) for reading the data. All this operation is done in minute fraction of a second. So, you can't see specifically in result pane that an update lock is applied unless you force the optimizer to keep lock. Even this happens with SELECT statement.
Update Lock (U) cannot be held by optimizer. It needs to be escalated to an Exclusive Lock (X).
Intent Shared Lock (IS)
Intent Locks are different from above locks. These are applied only when query predicate contains Indexed Column. When an indexed column is contained in the query predicate, Intent Locks are applied at Object and Page levels.
Intent Shared Lock (IS) functionality is same as Shared Lock (S) but the difference is, it allows other Intent Locks like Intent Exclusive (IX) or Intent Update Lock (IU) to be applied on same Object and Page though it is still in hold. But an Intent Lock is compatible with another Intent Lock only. If you try to apply normal lock on the object which Intent Lock is holding, it won't allow until it is released.
Intent Exclusive Lock (IX)
Intent Exclusive Lock (IX) shares the same functionality as Exclusive Lock (X) with a difference that it is applied at Object and Page levels. It also allows other locks to be applied on the same Object and Page by other transactions. It is compatible with only other Intent Locks but not with any normal lock.
Intent Update Lock (IU)
Intent Update Lock (IU) shares the same functionality as Update Lock (U). It is applied at Object and Page levels. It is compatible with intent locks only. If another transaction tries to apply one more Intent Update Lock (IU) then it allows that transaction to apply on the same page and object.
Shared with Intent Exclusive Lock (SIX)
Shared with Intent Exclusive Lock (SIX) is placed at Object Level. For SIX to be applied, a single transaction should perform read as well as update operations on a table.
Suppose TRAN1 reads some rows from a table, updates some rows of it then an SIX is placed at Object Level, IX is placed at Page Level and an X is placed at Row Level. SIX allows only Intent Locks at Object and Page levels but doesn't allow normal locks like other Intent Locks.
_________________________________________________________________________________
_________________________________________________________________________________
This is the theoretical explanation about Locking which I understood. I'll try to explain Locking concepts with examples in my coming posts. Follow the official documentation by Microsoft on Locking.
No comments:
Post a Comment