January 9, 2018 at 11:32 am
Hi,
Can you please help me to find what is causing dead lock here and possible fix for it?
2017-12-28 04:18:52.93 spid36s deadlock-list
2017-12-28 04:18:52.93 spid36s deadlock victim=processf99f42d088
2017-12-28 04:18:52.93 spid36s process-list
2017-12-28 04:18:52.93 spid36s process id=processf99f42d088 taskpriority=0 logused=0 waitresource=KEY: 6:281474978938880 (a4234cc93674) waittime=3135 ownerId=658178711 transactionname=user_transaction lasttranstarted=2017-12-28T04:18:49.170 XDES=0xf6e3ec1900 lockMode=S schedulerid=2 kpid=15208 status=suspended spid=242 sbid=0 ecid=0 priority=0 trancount=1 lastbatchstarted=2017-12-28T04:18:49.730 lastbatchcompleted=2017-12-28T04:18:49.713 lastattention=1900-01-01T00:00:00.713 clientapp=SAP Data Services Engine hostname=EDW hostpid=7944 loginname=abc\EDWSRV isolationlevel=read committed (2) xactid=658178711 currentdb=6 lockTimeout=4294967295 clientoption1=671090784 clientoption2=128056
2017-12-28 04:18:52.93 spid36s executionStack
2017-12-28 04:18:52.93 spid36s frame procname=mssqlsystemresource.sys.sp_columns_100 line=138 stmtstart=12020 stmtend=17532 sqlhandle=0x0300ff7f78a964e1b3024a01ada6000001000000000000000000000000000000000000000000000000000000
2017-12-28 04:18:52.93 spid36s select
2017-12-28 04:18:52.93 spid36s TABLE_QUALIFIER = s_cov.TABLE_QUALIFIER,
2017-12-28 04:18:52.93 spid36s TABLE_OWNER = s_cov.TABLE_OWNER,
2017-12-28 04:18:52.93 spid36s TABLE_NAME = s_cov.TABLE_NAME,
2017-12-28 04:18:52.93 spid36s COLUMN_NAME = s_cov.COLUMN_NAME,
2017-12-28 04:18:52.93 spid36s DATA_TYPE = s_cov.DATA_TYPE,
2017-12-28 04:18:52.93 spid36s TYPE_NAME = s_cov.TYPE_NAME,
2017-12-28 04:18:52.93 spid36s "PRECISION" = s_cov.PRECISION,
2017-12-28 04:18:52.93 spid36s "LENGTH" = s_cov.LENGTH,
2017-12-28 04:18:52.93 spid36s SCALE = s_cov.SCALE,
2017-12-28 04:18:52.93 spid36s RADIX = s_cov.RADIX,
2017-12-28 04:18:52.93 spid36s NULLABLE = s_cov.NULLABLE,
2017-12-28 04:18:52.93 spid36s REMARKS = s_cov.REMARKS,
2017-12-28 04:18:52.93 spid36s COLUMN_DEF = s_cov.COLUMN_DEF,
2017-12-28 04:18:52.93 spid36s SQL_DATA_TYPE = s_cov.SQL_DATA_TYPE,
2017-12-28 04:18:52.93 spid36s SQL_DATETIME_SUB = s_cov.SQL_DATETIME_SUB,
2017-12-28 04:18:52.93 spid36s CHAR_OCTET_LENGTH = s_cov.CHAR_OCTET_LENGTH,
2017-12-28 04:18:52.93 spid36s ORDINAL_POSITION
2017-12-28 04:18:52.93 spid36s inputbuf
2017-12-28 04:18:52.93 spid36s Proc [Database Id = 32767 Object Id = -513496712]
2017-12-28 04:18:52.93 spid36s process id=processfc43044ca8 taskpriority=0 logused=7320 waitresource=KEY: 6:562949956108288 (dbf0ad2d8475) waittime=3131 ownerId=658180141 transactionname=DROPOBJ lasttranstarted=2017-12-28T04:18:49.780 XDES=0xf66de94e58 lockMode=X schedulerid=3 kpid=5260 status=suspended spid=248 sbid=0 ecid=0 priority=0 trancount=2 lastbatchstarted=2017-12-28T04:18:49.773 lastbatchcompleted=2017-12-28T04:18:49.757 lastattention=1900-01-01T00:00:00.757 clientapp=jTDS hostname=EDW hostpid=11024 loginname=abc\EDWSRV isolationlevel=read committed (2) xactid=658180141 currentdb=6 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128056
2017-12-28 04:18:52.93 spid36s executionStack
2017-12-28 04:18:52.93 spid36s frame procname=adhoc line=1 sqlhandle=0x01000600e3127f13c01a09fcf700000000000000000000000000000000000000000000000000000000000000
2017-12-28 04:18:52.93 spid36s DROP TABLE [CS_BILL_MONEY_RCVD_DWRK]
2017-12-28 04:18:52.93 spid36s inputbuf
2017-12-28 04:18:52.93 spid36s DROP TABLE [CS_BILL_MONEY_RCVD_DWRK]
2017-12-28 04:18:52.93 spid36s resource-list
2017-12-28 04:18:52.93 spid36s keylock hobtid=281474978938880 dbid=6 objectname=DH_Admin.sys.sysschobjs indexname=clst id=lockfb24aa7c80 mode=X associatedObjectId=281474978938880
2017-12-28 04:18:52.93 spid36s owner-list
2017-12-28 04:18:52.93 spid36s owner id=processfc43044ca8 mode=X
2017-12-28 04:18:52.93 spid36s waiter-list
2017-12-28 04:18:52.93 spid36s waiter id=processf99f42d088 mode=S requestType=wait
2017-12-28 04:18:52.93 spid36s keylock hobtid=562949956108288 dbid=6 objectname=DH_Admin.sys.syscolpars indexname=nc id=lockfc26229800 mode=U associatedObjectId=562949956108288
2017-12-28 04:18:52.93 spid36s owner-list
2017-12-28 04:18:52.93 spid36s owner id=processf99f42d088 mode=S
2017-12-28 04:18:52.93 spid36s waiter-list
2017-12-28 04:18:52.93 spid36s waiter id=processfc43044ca8 mode=X requestType=convert
January 9, 2018 at 1:06 pm
Here is my understanding of this deadlock. But I'm unable to get a solution to resolve this issue. Because of this, ETL load is failing and I have to re-run. Please advise.
Victim ID processf99f42d088
process id=processf99f42d088
Database:dh_admin
*****************************
spid=242 is running below query:
select
2017-12-28 04:18:52.93 spid36s TABLE_QUALIFIER = s_cov.TABLE_QUALIFIER,
2017-12-28 04:18:52.93 spid36s TABLE_OWNER = s_cov.TABLE_OWNER,
2017-12-28 04:18:52.93 spid36s TABLE_NAME = s_cov.TABLE_NAME,
2017-12-28 04:18:52.93 spid36s COLUMN_NAME = s_cov.COLUMN_NAME,
2017-12-28 04:18:52.93 spid36s DATA_TYPE = s_cov.DATA_TYPE,
2017-12-28 04:18:52.93 spid36s TYPE_NAME = s_cov.TYPE_NAME,
2017-12-28 04:18:52.93 spid36s "PRECISION" = s_cov.PRECISION,
2017-12-28 04:18:52.93 spid36s "LENGTH" = s_cov.LENGTH,
2017-12-28 04:18:52.93 spid36s SCALE = s_cov.SCALE,
2017-12-28 04:18:52.93 spid36s RADIX = s_cov.RADIX,
2017-12-28 04:18:52.93 spid36s NULLABLE = s_cov.NULLABLE,
2017-12-28 04:18:52.93 spid36s REMARKS = s_cov.REMARKS,
2017-12-28 04:18:52.93 spid36s COLUMN_DEF = s_cov.COLUMN_DEF,
2017-12-28 04:18:52.93 spid36s SQL_DATA_TYPE = s_cov.SQL_DATA_TYPE,
2017-12-28 04:18:52.93 spid36s SQL_DATETIME_SUB = s_cov.SQL_DATETIME_SUB,
2017-12-28 04:18:52.93 spid36s CHAR_OCTET_LENGTH = s_cov.CHAR_OCTET_LENGTH,
2017-12-28 04:18:52.93 spid36s ORDINAL_POSITION
**************************************************
spid=248 is running below query:
DROP TABLE [CS_BILL_MONEY_RCVD_DWRK]
******************************************************************
spid=242 is waiting for an Exclusive KEY lock on indexname=clst
(Spid 248 holds a conflicting Shared lock.)
spid=248 is waiting for a shared KEY lock on indexname=nc
(Spid 242 holds a conflicting Exclusive lock.)
January 9, 2018 at 2:58 pm
Hi,
It is not the first time you are asking the same question. From the provided information is not obvious what is happening on your server. Can you publish a XML deadlock graph for this deadlock?
January 10, 2018 at 2:56 am
This was removed by the editor as SPAM
January 10, 2018 at 3:06 am
Troubleshooting deadlocks is a time-consuming process, so you may find that others are reluctant to do this before you if you haven't done a bit of work on it yourself. Please work through this series of articles and post back if there's anything you don't understand.
John
January 10, 2018 at 2:24 pm
subramaniam.chandrasekar - Wednesday, January 10, 2018 2:56 AMgary1 - Tuesday, January 9, 2018 1:06 PMHere is my understanding of this deadlock. But I'm unable to get a solution to resolve this issue. Because of this, ETL load is failing and I have to re-run. Please advise.Victim ID processf99f42d088
process id=processf99f42d088
Database:dh_admin
*****************************
spid=242 is running below query:
select
2017-12-28 04:18:52.93 spid36s TABLE_QUALIFIER = s_cov.TABLE_QUALIFIER,
2017-12-28 04:18:52.93 spid36s TABLE_OWNER = s_cov.TABLE_OWNER,
2017-12-28 04:18:52.93 spid36s TABLE_NAME = s_cov.TABLE_NAME,
2017-12-28 04:18:52.93 spid36s COLUMN_NAME = s_cov.COLUMN_NAME,
2017-12-28 04:18:52.93 spid36s DATA_TYPE = s_cov.DATA_TYPE,
2017-12-28 04:18:52.93 spid36s TYPE_NAME = s_cov.TYPE_NAME,
2017-12-28 04:18:52.93 spid36s "PRECISION" = s_cov.PRECISION,
2017-12-28 04:18:52.93 spid36s "LENGTH" = s_cov.LENGTH,
2017-12-28 04:18:52.93 spid36s SCALE = s_cov.SCALE,
2017-12-28 04:18:52.93 spid36s RADIX = s_cov.RADIX,
2017-12-28 04:18:52.93 spid36s NULLABLE = s_cov.NULLABLE,
2017-12-28 04:18:52.93 spid36s REMARKS = s_cov.REMARKS,
2017-12-28 04:18:52.93 spid36s COLUMN_DEF = s_cov.COLUMN_DEF,
2017-12-28 04:18:52.93 spid36s SQL_DATA_TYPE = s_cov.SQL_DATA_TYPE,
2017-12-28 04:18:52.93 spid36s SQL_DATETIME_SUB = s_cov.SQL_DATETIME_SUB,
2017-12-28 04:18:52.93 spid36s CHAR_OCTET_LENGTH = s_cov.CHAR_OCTET_LENGTH,
2017-12-28 04:18:52.93 spid36s ORDINAL_POSITION
**************************************************
spid=248 is running below query:
DROP TABLE [CS_BILL_MONEY_RCVD_DWRK]
******************************************************************
spid=242 is waiting for an Exclusive KEY lock on indexname=clst
(Spid 248 holds a conflicting Shared lock.)spid=248 is waiting for a shared KEY lock on indexname=nc
(Spid 242 holds a conflicting Exclusive lock.)This looks like some contention issues within RAM, We can say this as a locking issue. May I know what version of SQL is it ? What is the isolation level setting of your db?
Please try to clean the procache in SQL main memory and refresh the execution plans.
We are using SQL Server 2016 SP1 EE and Isolation level is "read committed"
January 10, 2018 at 8:08 pm
This was removed by the editor as SPAM
January 11, 2018 at 10:41 am
John Mitchell-245523 - Wednesday, January 10, 2018 3:06 AMTroubleshooting deadlocks is a time-consuming process, so you may find that others are reluctant to do this before you if you haven't done a bit of work on it yourself. Please work through this series of articles and post back if there's anything you don't understand.John
Hi John,
I went through the link and I updated with my understanding. I'm not able to understand why system tables are causing deadlocks.
January 11, 2018 at 11:33 am
gary1 - Thursday, January 11, 2018 10:41 AMJohn Mitchell-245523 - Wednesday, January 10, 2018 3:06 AMTroubleshooting deadlocks is a time-consuming process, so you may find that others are reluctant to do this before you if you haven't done a bit of work on it yourself. Please work through this series of articles and post back if there's anything you don't understand.John
Hi John,
I went through the link and I updated with my understanding. I'm not able to understand why system tables are causing deadlocks.
Why does the table need to be dropped? If you can alter the process, can you try to truncate the table instead? Not the right solution but it might help de-escalate the locking situation.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply