Locks
------
Locks are the objects that helps in access synchronization for objects like Schema , database , tables , extents ect .
Types of locks
---------------
Shared (S) locks
-----------------
allows us to read the data by running the select queries .During the time Shared lock exist no other transaction can modify the data being read .The good part it that these locks are released as soon as the select completes even though the transaction might still be running .But there is an exception .This does not hold true if the isolation level is Repeatable Read or serializable .Why ???? because the Shared locks are not released and are kept till the transaction commits or rolls back .
Test
-----
1st Qquery window
-------------------
Create table Shared_test(sh int)
go
declare @sht int
set @sht=50
while @sht >0
begin
Insert into shared_test values (@sht)
set @sht=@sht-1
end
go
begin tran
select * from shared_test
2nd Qquery window
------------------
sp_lock
go
/*you will notice that there is no lock on that object*/
drop table shared_test
go
/*The drop will be successful*/
Again 1st Qquery window
-----------------------
Rollback
go
set transaction isolation level repeatable read
go
Create table Shared_test(sh int)
go
declare @sht int
set @sht=50
while @sht >0
begin
Insert into shared_test values (@sht)
set @sht=@sht-1
end
go
begin tran
select * from shared_test
Again 2nd Qquery window
-------------------------
sp_lock
go
/*you will notice that there is no lock on that object*/
drop table shared_test
go
Result:
---------
You will see that there are a lot of shared locks on the RID (row identifier for each row)and the Drop will be blocked (try doing sp_lock from another window to see the SCH-M lock in wait state).
Compatibility
--------------
Shared locks are compatible only with Shared , Intent Shared and update locks.
Exclusive(X) locks
-------------------
Its the most strict lock .Unless you are in read-uncommitted isolation level or using NOLOCK hint you will be blocked...
Compatibility
---------------
Exclusive lock will only be compatible with SCH-S(Shared Schema) and RI-N(Insert Range Null) locks.
Update (U) locks
-----------------
Before understanding about the update locks let me tell you that whenever we update , insert or delete we do two operations : Searching where to insert update or delete and doing that action .
Seaching is done using Shared locks and completing the action of insert , update or delete will be done by the exclusive lock (both covered above).
Other important point that you have read above (search where (",)..) that in isolation level higher than read committed the shared locks are kept and not released till the end of transaction .So Imagine that there is no UPDATE locks and you and your friend both are updating x rows to a different values in the same table from 2 different client machines , at the same time and in isolation level repeatable read (we will learn about Isolation levels in sometime ) .I forgot to mention that the transaction is not committed.since the shared locks are compatible with shared locks , you both will be granted the shared locks ....fine..
Now , the X mode lock of A friend is not compatible with S mode lock of another friend named B ..so Blocking occure as X mode is not able to get converted to S mode .Similarly , the same will happen with B friend as well (I will leave it up to you to guess)...and this will cause deadlock..
Update lock will ensure that No two transactions can try to update the same resource at the same time ...further till the update resource is doing the searching it will show you as U in sp_lock .The moment it tries to modify the row , U mode disappear and X mode is shown.
Test :
I am sure you still have the same two queryanalyzer windows open with repeatable read isolation level.
1st Query Window
------------------
There is already a select going on .But for your understanding I am typing the same again :
begin tran
select * from shared_test
2nd Query Window
-----------------
If you have not yet stopped the previous transaction of dropping the table , then stop it .
run this command
begin tran
update Shared_test set sh=(sh*2) where sh > 35
It will be blocked for sure
1st Query Window
------------------
sp_lock
go
Result:
---------
among other locks, you will see(modified for better view) :
51 RID 1:18773:0 X CNVT
51 TAB IX GRANT
51 PAG 1:18773 IX GRANT
51 RID 1:18773:0 U GRANT
53 RID 1:18773:0 S GRANT
53 PAG 1:18773 IS GRANT
Here ,
18873 is the page id
1 is the datafile
0 is the row id
1)
SPID 53 took IS lock on page 18773 and then S lock on rowid 0 fearing if someone tries to modify the page should be blocked ....agreed
2)
SPID 51 took U lock row id 0 of page 18773 which is allowed as S and U locks are compatible.Remember internally its also S lock at this moment .The moment it tries to change something it will be converted to X lock and will no forther be U lock any more ---> 51 then moved ahead and before trying to take the X lock on the RID it first took the IX lock on the page (in case someone drops the object which its trying to modify)which is granted because IS and IX are compatible .
But when U lock on RID 1:18773:0 tried to convert in to X lock , It got blocked and you can see CNVT ..
But this test was not exactly the example that I gave when I was talking about the U locks
So lets try what i said ...2 updates on the same table and same rows ...
Test
-----
make sure you rollback all the transactions on the 2 QA windows and remove all the query text .
1st Window
------------
Set transaction isolation level repeatable read
go
begin tran
update Shared_test set sh=(sh*2) where sh > 35
2nd window
------------
Set transaction isolation level repeatable read
go
begin tran
update Shared_test set sh=(sh*2) where sh > 35
1st Window
------------
sp_lock
Results:
-----------
51 741577680 PAG 1:18773 IU GRANT
51 741577680 RID 1:18773:0 U WAIT
51 741577680 TAB IX GRANT
53 741577680 TAB IX GRANT
53 741577680 PAG 1:18773 IX GRANT
53 741577680 RID 1:18773:0 X GRANT
1)
53 took IX on page before X on rowid (I will explain later on why IX and IS are taken) which is right and then IX on table
2)
53 also took IX on table (IX and IX are compatible) and IU on page (IX and IU are compatible) .But U and X are not compatible and hence blocking.Had there been no U it would have been a deadlock ....
Like this do some testing on your own and gain some knowlwdge..
Referance :
Lock Compatibility (Database Engine):
http://msdn.microsoft.com/en-us/library/ms186396(SQL.90).aspx
SP_LOCK :
http://msdn.microsoft.com/en-us/library/aa238824(SQL.80).aspx
Happy learning !!!!!
No comments:
Post a Comment