none
Could SQL Server lock partitioning cause deadlock? RRS feed

  • Question

  • Hi, We are running stored procedures on a 64 bit SQL Server 2005 (SP3) on top of a 16-core 64 bit Windows 2003 server. On one occasion, I nnoticed a deadlock in my server log. The resources involved are OBJECT: 7:9384248:0, and OBJECT: 7:9384248:8. I assume the last digit (0 and 8) are lock partition number. The wait-for graph told me that two processes each owned an X lock and tried to prompt an IX lock to X lock on the objects they don't own. The processes were performing an INSERT statement to the same table.

    I then studied the chapter regarding lock partitioning in Delaney's "SQL Server 2005: The Storage Engine". It seems to me that such behavior is possible because SQL Server storage engine broadcasts lock prompt request to each lock partition (for example, it will need to prompt each partition to type X).

    The occurence for our system to experience deadlock is extreme low, so this is a very rare incidence. It is not a show stopper at all (end user will just re-try). I am curious and hope to hear from someone (from SQL Server Storage Engine Team) for a confirmation. Thanks in advance for any helpful info.

    Tony

     

    Saturday, June 26, 2010 12:11 AM

Answers

  • The marked answer is incorrect.  The last digit is the lock partition, which you can verify by comparing the deadlock message in the errorlog with the deadlock xml in the system_health XEvents session, which explicitly indicates the lock partition.  eg:

    SELECT 
        CONVERT(xml, event_data).query('/event/data/value/child::*') AS DeadlockReport,
        timestamp_utc, datediff(s,timestamp_utc,GETUTCDATE()) sec_ago
    FROM sys.fn_xe_file_target_read_file(
            cast(SERVERPROPERTY('ErrorLogFileName') as varchar(max)) + 
            '\..\system_health*.xel', NULL, NULL, NULL)
    WHERE OBJECT_NAME like 'xml_deadlock_report';

    David


    Microsoft Technology Center - Dallas
    My blog

    Thursday, November 1, 2018 6:08 PM

All replies

  • I do not believe the last digit is the lock partition number, but rather the index_id.

    Internal structures such as the lock partitions are not managed the same as user data, so it is not possible to deadlock on the lock partition structure itself.

    Without seeing more details about your deadlock, to me it looks like a normal deadlock situation.


    HTH, Kalen Delaney www.SQLServerInternals.com
    Saturday, June 26, 2010 3:16 AM
    Moderator
  • Thanks Kalen for your quick info!

    I have put at the bottom (of this reply) the log info for the deadlock (which happened once again in the weekend).

    Here is my interpretation to the log info:

    ProcA (processcbfc18) and ProcB (process8aa6718) are each trying to X-lock both "OBJECT: 7:1797581442:0" and "OBJECT: 7:1797581442:12".

    ProcA has an X lock to "OBJECT: 7:1797581442:0", and is waiting to place an X lock to "OBJECT: 7:1797581442:12".

    ProcB has an IX lock to "OBJECT: 7:1797581442:12", and is waiting to place an X lock to "OBJECT: 7:1797581442:0".

    The log has explicit phrases such as "lockPartition=12" or "lockPartition=0" so I start to doubt those suffix such as ":12" or ":0" are for lock partition.

    In addition, my sys.sysindexes shows object 1797581442 has 6 indices (two user indices and  four  _WA_sys type indices created by auto statistics). "indid" column in sysindexes start from 1 (not "0").

    I studied lock partition topic in your book and then start to doubt that at the moment when two transactions both want to place X locks on ALL lock partitions to a resource,  deadlock could occur in case each transaction is successful in partially locking some of the lock partitions (and waiting for the rest). Hope I will be wrong as SQL Server 2005 may have extra synchronization to suppress possible deadlocks at that point. Theoretically people could choose to have better concurrency at the expense of synchronization.

    Again, thanks for your time and help!

    ==== excerpt from server log ====

    06/28/2010 07:57:42,spid19s,Unknown,waiter id=processcbfc18 mode=X requestType=wait
    06/28/2010 07:57:42,spid19s,Unknown,waiter-list
    06/28/2010 07:57:42,spid19s,Unknown,owner id=process8aa6718 mode=IX
    06/28/2010 07:57:42,spid19s,Unknown,owner-list
    06/28/2010 07:57:42,spid19s,Unknown,objectlock lockPartition=12 objid=1797581442 subresource=FULL dbid=7 objectname=p_DB1.dbo.ChangeHistory id=lock45564c380 mode=IX associatedObjectId=1797581442
    06/28/2010 07:57:42,spid19s,Unknown,waiter id=process8aa6718 mode=X requestType=wait
    06/28/2010 07:57:42,spid19s,Unknown,waiter-list
    06/28/2010 07:57:42,spid19s,Unknown,owner id=processcbfc18 mode=X
    06/28/2010 07:57:42,spid19s,Unknown,owner-list
    06/28/2010 07:57:42,spid19s,Unknown,objectlock lockPartition=0 objid=1797581442 subresource=FULL dbid=7 objectname=p_DB1.dbo.ChangeHistory id=lock3fc5e0d80 mode=X associatedObjectId=1797581442
    06/28/2010 07:57:42,spid19s,Unknown,resource-list
    06/28/2010 07:57:42,spid19s,Unknown,Proc [Database Id = 7 Object Id = 6291082]
    06/28/2010 07:57:42,spid19s,Unknown,inputbuf
    06/28/2010 07:57:42,spid19s,Unknown,unknown
    06/28/2010 07:57:42,spid19s,Unknown,frame procname=p_DB1.dbo.objSaveChangeHistory line=40 stmtstart=1868 stmtend=2068 sqlhandle=0x030007008afe5f0030f94c01819a00000100000000000000
    06/28/2010 07:57:42,spid19s,Unknown,insert into ffPackageHistory (time<c/> stationid<c/>  EmployeeID) values (@CreationTime<c/> @StationID<c/>  @EmployeeID)
    06/28/2010 07:57:42,spid19s,Unknown,frame procname=p_DB1.dbo.uspSaveChangeHistory line=106 stmtstart=10680 stmtend=10982 sqlhandle=0x03000700e840e73e3657be008c9d00000100000000000000
    06/28/2010 07:57:42,spid19s,Unknown,executionStack
    06/28/2010 07:57:42,spid19s,Unknown,process id=process8aa6718 taskpriority=0 logused=1156 waitresource=OBJECT: 7:1797581442:0  waittime=2093 ownerId=1808671432 transactionname=user_transaction lasttranstarted=2010-

    06-28T07:57:40.490 XDES=0x1b88eb390 lockMode=X schedulerid=13 kpid=8292 status=suspended spid=503 sbid=0 ecid=0 priority=0 transcount=2 lastbatchstarted=2010-06-28T07:57:40.487 lastbatchcompleted=2010-06-

    28T07:57:40.487 clientapp=FF2.0 hostname=GDLF4675 hostpid=164 loginname=dlffr32540 isolationlevel=read committed (2) xactid=1808671432 currentdb=7 lockTimeout=4294967295 clientoption1=673185824 clientoption2=128056
    06/28/2010 07:57:42,spid19s,Unknown,Proc [Database Id = 7 Object Id = 6291082]
    06/28/2010 07:57:42,spid19s,Unknown,inputbuf
    06/28/2010 07:57:42,spid19s,Unknown,unknown
    06/28/2010 07:57:42,spid19s,Unknown,frame procname=p_DB1.dbo.objSaveChangeHistory line=40 stmtstart=1868 stmtend=2068 sqlhandle=0x030007008afe5f0030f94c01819a00000100000000000000
    06/28/2010 07:57:42,spid19s,Unknown,insert into ChangeHistory (time<c/> stationid<c/>  EmployeeID) values (@CreationTime<c/> @StationID<c/>  @EmployeeID)
    06/28/2010 07:57:42,spid19s,Unknown,frame procname=p_DB1.dbo.uspSaveChangeHistory line=106 stmtstart=10680 stmtend=10982 sqlhandle=0x03000700e840e73e3657be008c9d00000100000000000000
    06/28/2010 07:57:42,spid19s,Unknown,executionStack
    06/28/2010 07:57:42,spid19s,Unknown,process id=processcbfc18 taskpriority=0 logused=1156 waitresource=OBJECT: 7:1797581442:12  waittime=2093 ownerId=1808661861 transactionname=user_transaction lasttranstarted=2010-06

    -28T07:57:40.110 XDES=0x48cb38f70 lockMode=X schedulerid=1 kpid=105748 status=suspended spid=152 sbid=0 ecid=0 priority=0 transcount=2 lastbatchstarted=2010-06-28T07:57:40.100 lastbatchcompleted=2010-06-

    28T07:57:40.100 clientapp=FF2.0 hostname=GDLF5365 hostpid=2612 loginname=dlffr32540 isolationlevel=read committed (2) xactid=1808661861 currentdb=7 lockTimeout=4294967295 clientoption1=673185824 clientoption2=128056
    06/28/2010 07:57:42,spid19s,Unknown,process-list
    06/28/2010 07:57:42,spid19s,Unknown,deadlock victim=process8aa6718
    06/28/2010 07:57:42,spid19s,Unknown,deadlock-list

    Monday, June 28, 2010 6:59 PM
  • I am facing exactly the same problem, in a server with 24 CPUs.

    The deadlock always happens with two inserts in the same table, with one process owning partition ID = 0 with X lock and the other with a IX lock on a partition ID > 7 (number changes and has no index corresponding in sysindexes indid, so I guess it has to do with http://msdn.microsoft.com/en-us/library/ms187504.aspx). The deadlock occurs because the first process tries to obtain a X lock on that partition ID while the second process tries to obtain an X lock on the partition ID = 0.

    Is there any news about this problem?

    Tuesday, July 20, 2010 3:00 PM
  • Reopened. Can anyone assist?

    Also can you run deadlock graph and post it here (you need to store image first at: www.skydrive.com) ? Thanks.


    Kalman Toth, SQL Server & Business Intelligence Training; SQL 2008 GRAND SLAM
    Wednesday, July 21, 2010 5:48 AM
    Moderator
  • Tony, the last digit  of the resource string is the slot number which indicate the index of the record in a data page. lock partitioning will not cause dead lock. the dead lock is caused by circled reference

    Thanks

    John Huang http://sqlx.blogspot.com

    Friday, July 30, 2010 7:28 AM
  • The last  2 digit are lock partiotion number check this article and try the samples given in this article .

    http://msdn.microsoft.com/en-us/library/ms187504(v=sql.100).aspx

    Thanks

    Nelson

    WWW.PracticalSqlDba.com

    Thursday, April 12, 2012 12:36 PM
  • Even though this is an old post, thought this might help the people directed here from a search enginge.

    http://sqlindian.com/2012/07/07/deadlocks-involving-lock-partitions/

    Regards
    Roji Thomas
    http://www.sqlindian.com


    Roji. P. Thomas

    Tuesday, July 17, 2012 6:07 PM
  • I have

    wait_type                wait_resource

    LCK_M_IS               OBJECT: 22:1979154096:0

    I can finds the DB and table "dbo.ReplicationTopology". This is from IderaDM schema. I don't have index 0. I don't have deadlock.

    Wednesday, August 22, 2012 7:47 PM

  • scenario :
    s1(session 1): alter table tb1 (deadlock victim).
    s2: select sth from tb1 (high concurrency is needed for deadlock to happen )


    s1 who got sch-m lock on p3(partition lock on cpu core 3)is waiting for sch-m lock on p11,at the same time s2 who got sch-s lock on p11 is waiting for sch-s lock on p11,thus deadlock rise on p3 and p3 between s1 and s2.


    Lock Partitioning:
    "strict" lock (such as sch-M lock when alter table) need put lock on all cpu cores to ensure integrity by stop any core to do any work for any others. "strict" lock include all locks other than SCH-S, IS, IU, and IX, such as common S,X lock and only object locks(table level but not page/row level locks)can be partitioned. 

    Saturday, October 12, 2013 3:39 AM
  • The marked answer is incorrect.  The last digit is the lock partition, which you can verify by comparing the deadlock message in the errorlog with the deadlock xml in the system_health XEvents session, which explicitly indicates the lock partition.  eg:

    SELECT 
        CONVERT(xml, event_data).query('/event/data/value/child::*') AS DeadlockReport,
        timestamp_utc, datediff(s,timestamp_utc,GETUTCDATE()) sec_ago
    FROM sys.fn_xe_file_target_read_file(
            cast(SERVERPROPERTY('ErrorLogFileName') as varchar(max)) + 
            '\..\system_health*.xel', NULL, NULL, NULL)
    WHERE OBJECT_NAME like 'xml_deadlock_report';

    David


    Microsoft Technology Center - Dallas
    My blog

    Thursday, November 1, 2018 6:08 PM
  • Deleted
    Thursday, November 1, 2018 7:31 PM