x

blocked with waiting sch-m lock

hi, have three transactions:

tran1 (sessionid = 59):

   begin tran
    insert into test values(0,0)

tran2 (sessionid = 57):

  begin tran
    alter table test add c3 int

tran3 (sessionid = 55):

begin tran
insert into test values(1,1)

I use below statements to find out the lock informations:

SELECT DTL.resource_type,DTL.resource_description,DTL.resource_associated_entity_id,DTL.request_mode,DTL.request_type
,DTL.request_status,DTL.request_session_id
FROM sys.dm_tran_locks AS DTL
WHERE DTL.resource_type <> 'DATABASE'
ORDER BY DTL.request_session_id


SELECT DER.session_id,DER.request_id,DER.status,DER.command,DER.blocking_session_id,DER.wait_type,DER.wait_resource
FROM sys.dm_exec_requests AS DER
WHERE DER.session_id > 50

resource_type   resource_description       resource_associated_entity_id request_mode   request_type   request_status     request_session_id
--------------- -------------------------- ----------------------------- -------------- -------------- ------------------ ------------------
OBJECT                                     1957582012                    IX             LOCK           WAIT               55
OBJECT                                     1957582012                    Sch-M          LOCK           WAIT               57
PAGE            1:148                      72057594042646528             IX             LOCK           GRANT              59
RID             1:148:0                    72057594042646528             X              LOCK           GRANT              59
OBJECT                                     1957582012                    IX             LOCK           GRANT              59


session_id request_id  status      command          blocking_session_id wait_type      wait_resource
---------- ----------- ----------- ---------------- ------------------- -------------- --------------------------
55         0           suspended   INSERT           57                  LCK_M_IX       OBJECT: 5:1957582012:0 
57         0           suspended   ALTER TABLE      59                  LCK_M_SCH_M    OBJECT: 5:1957582012:0 

and my instance on a virtual pc only one cpu.

my questions is: why the wait status of tran2 is still 'WAIT'(it is blocked by tran1), but the tran3 is blocked by tran2?
more ▼

asked Apr 12, 2011 at 11:43 PM in Default

Grant Wu gravatar image

Grant Wu
13 1 1 2

(comments are locked)
10|1200 characters needed characters left

2 answers: sort oldest

The simple answer is because blocks are chained. At first, a transaction will look to see if there is an existing blocking chain waiting for the resources it needs. If there is, it get's added to the tail of the blocking chain. If not, then it checks to see if it can acquire all locks it needs. If it can't it starts a new blocking chain.

That's a huge over-simplification, but it gives you the general idea. So...

  • Tran1 comes along, holds the IX/RID/Page locks

  • Tran2 comes along, needs the locks but can't acquire them. There is no existing blocking chain so the blocking chain starts at that point.

  • Tran3 comes along, finds the blocking chain on the resources it requires, joins the queue.

Again - hugely over-simplified, but gives you an idea.
more ▼

answered Apr 13, 2011 at 03:23 AM

Matt Whitfield gravatar image

Matt Whitfield ♦♦
29.4k 61 65 87

+1 Thanks for making it sound simpler than I was!
Apr 13, 2011 at 03:44 AM Kev Riley ♦♦
Thanks for your reply. Is it also can say that is for avoid the "Lock Starvation"?
Apr 13, 2011 at 04:51 PM Grant Wu
(comments are locked)
10|1200 characters needed characters left

From [MSDN/BOL][1]

A granted request status indicates that a lock has been granted on a resource to the requestor. A waiting request indicates that the request has not yet been granted. The following waiting-request types are returned by the request_status column:

  • A convert request status indicates that the requestor has already been granted a request for the resource and is currently waiting for an upgrade to the initial request to be granted.

  • A wait request status indicates that the requestor does not currently hold a granted request on the resource.

So because tran2 has not been granted any resources yet, it is WAITing whereas tran3 has some resources so is BLOCKED

[1]: http://msdn.microsoft.com/en-us/library/ms190345.aspx
more ▼

answered Apr 13, 2011 at 12:50 AM

Kev Riley gravatar image

Kev Riley ♦♦
53k 47 49 76

Kev, Thank you for your answer! Sorry, i made a mistake, my means is that: in this case, the tran1 is hold this table(with IX lock), tran2 is blocked by tran1 and tran2 is waiting for a sch-m lock. tran2 have not been granted this table. I think tran3 should be done(insert operation) when this table only be locked by IX lock.
Apr 13, 2011 at 01:10 AM Grant Wu

Do you mean why isn't the IX from tran3 compatible with the IX from tran1?

A Sch-M lock is not compatible with IX, so tran2 is waiting - this puts a wait resource on the object. tran3 only wants another IX, but but is blocked by the wait already existing on the object.

Don't get too mixed up with locks and blocks, locks are a cause of blocking, but not all blocking is caused by locks
Apr 13, 2011 at 01:56 AM Kev Riley ♦♦
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x587

asked: Apr 12, 2011 at 11:43 PM

Seen: 1367 times

Last Updated: Apr 13, 2011 at 01:28 AM