UNION and deadlock

I once encountered a deadlock related with UNION.

Table tbl_DataWorking(T1) and tbl_DataHistory (T2) have similar structure. T2 is a very big table (>2b rows). T1 is very small.
There are two SPs:
usp_GET_Data (SP1): it has the statement:
INSERT INTO #tempData (StartTime,Value,…)
SELECT StartTime, Value,.. FROM tbl_DataHistory
UNION
SELECT StartTime, Value,.. FROM tbl_DataWorking

usp_SET_Data (SP2): it moves data from T1 to T2. It has the statements:
INSERT tbl_DataHistory  (StartTime,Value,…)
SELECT StartTime, Value,.. FROM tbl_DataWorking

TRUNCATE TABLE tbl_DataWorking

The following is the trace I got for the dead lock:

Deadlock encountered …. Printing deadlock information
2005-05-06 01:15:42.24 spid4    
2005-05-06 01:15:42.24 spid4     Wait-for graph
2005-05-06 01:15:42.24 spid4    
2005-05-06 01:15:42.24 spid4     Node:1
2005-05-06 01:15:42.24 spid4     TAB: 6:869578136 []            CleanCnt:1 Mode: IX Flags: 0x0
2005-05-06 01:15:42.24 spid4      Grant List 1::
2005-05-06 01:15:42.31 spid4        Owner:0x2cd2e480 Mode: IS       Flg:0x0 Ref:1 Life:00000000 SPID:64 ECID:0
2005-05-06 01:15:42.31 spid4        SPID: 64 ECID: 0 Statement Type: SELECT INTO Line #: 46
2005-05-06 01:15:42.31 spid4        Input Buf: RPC Event: usp_GetData;1
2005-05-06 01:15:42.31 spid4      Requested By:
2005-05-06 01:15:42.31 spid4        ResType:LockOwner Stype:’OR’ Mode: Schema-Mod SPID:60 ECID:0 Ec:(0x49AC3520) Value:0x42bcbfa0 Cost:(0/13904)
2005-05-06 01:15:42.31 spid4    
2005-05-06 01:15:42.31 spid4     Node:2
2005-05-06 01:15:42.31 spid4     KEY: 6:853578079:1 (be0296ece9a2) CleanCnt:1 Mode: X Flags: 0x0
2005-05-06 01:15:42.31 spid4      Grant List 1::
2005-05-06 01:15:42.31 spid4        Owner:0x42be3140 Mode: X        Flg:0x0 Ref:0 Life:02000000 SPID:60 ECID:0
2005-05-06 01:15:42.31 spid4        SPID: 60 ECID: 0 Statement Type: TRUNCATE TABLE Line #: 97
2005-05-06 01:15:42.31 spid4        Input Buf: Language Event: exec usp_SET_Data 0,’2005-05-22 10:00:00AM’
2005-05-06 01:15:42.31 spid4      Requested By:
2005-05-06 01:15:42.31 spid4        ResType:LockOwner Stype:’OR’ Mode: S SPID:64 ECID:0 Ec:(0x295FF560) Value:0x42bda900 Cost:(0/0)
2005-05-06 01:15:42.31 spid4     Victim Resource Owner:
2005-05-06 01:15:42.31 spid4      ResType:LockOwner Stype:’OR’ Mode: S SPID:64 ECID:0 Ec:(0x295FF560) Value:0x42bda900 Cost:(0/0)

6:869578136: 6 is the working database id, and is 869578136 the object id of T1,
6:853578079:1: 853578079 is the object id of T2, and 1 is the index (the PK) of T2

The reason of the dead lock is that SQL server does NOT keep the order of query in a UNION command. In this case, it executes SELECT FROM T1 first. The

following is the executing sequence:
Time      Command (SP1)                                     Command (SP2)
1                                                                      INSERT T2 (key level X lock)
2           SELECT FROM T2 (Wait SP2)
             UNION 
             SELECT FROM T1 ( shared lock granted)
3                                                                      TRUNCATE TABLE T1 (Sch-Mod lock required, waiting SP1 to release)
4:  Deadlock. SP1 waiting SP2 to release X lock on T2; SP2 waiting SP1 to release shared lock on T1
5:                                                                     Chosen as Victim.
6:              SP1 finishes
7:                                                                     Client resubmit the call and succeeded.

This dead lock can potentially be avoided by splitting the union into seperate select statements. So when SELECT FROM T2 is blocked by SP2, it will wait there until SP2 is committed.

So keeping in mind, SQL server deos not gurantee the execution order of the SELECT statement in a query with UNION, especially on a SMP machine. If the order is important, you’d better break the UNION to individual SELECT statements.

 

Advertisements
This entry was posted in SQL server. Bookmark the permalink.

3 Responses to UNION and deadlock

  1. Youjin says:

    Hi Peter! you see you weren\’t being lazy 🙂 I am in the South Bend IN. I couldn\’t really sleep because of new bed. So I was checking out your article! good Tip !! 🙂

  2. Youjin says:

    btw what is SMP machine?

  3. Peter says:

    SMP is Symmetric MultiProcessor. Today, most multi-cpu machines are of SMP architecture.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s