blocked with waiting sch-m lock

Question

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?

Answer 1

0

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.

answered Apr 13 '11 at 03:23 AM

Matt Whitfield ♦♦
29.2k ● 56 ● 63 ● 87

+1 Thanks for making it sound simpler than I was!

Apr 13 '11 at 03:44 AM Kev Riley ♦♦

Thanks for your reply. Is it also can say that is for avoid the "Lock Starvation"?

Apr 13 '11 at 04:51 PM Grant Wu

(comments are locked)

Answer 2

0

From MSDN/BOL

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

answered Apr 13 '11 at 12:50 AM

Kev Riley ♦♦
46.1k ● 38 ● 43 ● 69

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 '11 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 '11 at 01:56 AM Kev Riley ♦♦

(comments are locked)

blocked with waiting sch-m lock

Follow this question

Related Questions