Dead lock in production
-
Hi,
We got a deadlock today during our daily data load into data warehouse database. Can you please advise what is causing this issue and how I can fix it?
2017-10-12 04:20:21.58 spid41s deadlock-list
2017-10-12 04:20:21.58 spid41s deadlock victim=processf7c7e27848
2017-10-12 04:20:21.58 spid41s process-list
2017-10-12 04:20:21.58 spid41s process id=processf7c7e27848 taskpriority=0 logused=0 waitresource=KEY: 6:281474978938880 (217b5ca7a8bb) waittime=2013 ownerId=84899009 transactionname=user_transaction lasttranstarted=2017-10-13T04:20:19.417 XDES=0xf842c4f900 lockMode=S schedulerid=4 kpid=14752 status=suspended spid=230 sbid=0 ecid=0 priority=0 trancount=1 lastbatchstarted=2017-10-13T04:20:19.560 lastbatchcompleted=2017-10-13T04:20:19.553 lastattention=1900-01-01T00:00:00.553 clientapp=SAP Data Services Engine hostname=HOST1 hostpid=10976 loginname=abc\ssrv isolationlevel=read committed (2) xactid=84899009 currentdb=6 lockTimeout=4294967295 clientoption1=671090784 clientoption2=128056
2017-10-12 04:20:21.58 spid41s executionStack
2017-10-12 04:20:21.58 spid41s frame procname=mssqlsystemresource.sys.sp_columns_100 line=138 stmtstart=12020 stmtend=17532 sqlhandle=0x0300ff7f78a964e1b3024a01ada6000001000000000000000000000000000000000000000000000000000000
2017-10-12 04:20:21.58 spid41s select
2017-10-12 04:20:21.58 spid41s TABLE_QUALIFIER = s_cov.TABLE_QUALIFIER,
2017-10-12 04:20:21.58 spid41s TABLE_OWNER = s_cov.TABLE_OWNER,
2017-10-12 04:20:21.58 spid41s TABLE_NAME = s_cov.TABLE_NAME,
2017-10-12 04:20:21.58 spid41s COLUMN_NAME = s_cov.COLUMN_NAME,
2017-10-12 04:20:21.58 spid41s DATA_TYPE = s_cov.DATA_TYPE,
2017-10-12 04:20:21.58 spid41s TYPE_NAME = s_cov.TYPE_NAME,
2017-10-12 04:20:21.58 spid41s "PRECISION" = s_cov.PRECISION,
2017-10-12 04:20:21.58 spid41s "LENGTH" = s_cov.LENGTH,
2017-10-12 04:20:21.58 spid41s SCALE = s_cov.SCALE,
2017-10-12 04:20:21.58 spid41s RADIX = s_cov.RADIX,
2017-10-12 04:20:21.58 spid41s NULLABLE = s_cov.NULLABLE,
2017-10-12 04:20:21.58 spid41s REMARKS = s_cov.REMARKS,
2017-10-12 04:20:21.58 spid41s COLUMN_DEF = s_cov.COLUMN_DEF,
2017-10-12 04:20:21.58 spid41s SQL_DATA_TYPE = s_cov.SQL_DATA_TYPE,
2017-10-12 04:20:21.58 spid41s SQL_DATETIME_SUB = s_cov.SQL_DATETIME_SUB,
2017-10-12 04:20:21.58 spid41s CHAR_OCTET_LENGTH = s_cov.CHAR_OCTET_LENGTH,
2017-10-12 04:20:21.58 spid41s ORDINAL_POSITION
2017-10-12 04:20:21.58 spid41s inputbuf
2017-10-12 04:20:21.58 spid41s Proc [Database Id = 32767 Object Id = -513496712]
2017-10-12 04:20:21.58 spid41s process id=processf7dca9eca8 taskpriority=0 logused=1868 waitresource=KEY: 6:562949956108288 (56e8affc712e) waittime=2013 ownerId=84899238 transactionname=user_transaction lasttranstarted=2017-10-13T04:20:19.560 XDES=0xf90fed2408 lockMode=X schedulerid=4 kpid=4012 status=suspended spid=242 sbid=0 ecid=0 priority=0 trancount=2 lastbatchstarted=2017-10-13T04:20:19.560 lastbatchcompleted=2017-10-13T04:20:19.550 lastattention=1900-01-01T00:00:00.550 clientapp=SAP Data Services Engine hostname=HOST1 hostpid=18164 loginname=abc\ssrv isolationlevel=read committed (2) xactid=84899238 currentdb=6 lockTimeout=4294967295 clientoption1=671090784 clientoption2=128056
2017-10-12 04:20:21.58 spid41s executionStack
2017-10-12 04:20:21.58 spid41s frame procname=adhoc line=1 sqlhandle=0x01000600846ac907f0f9f7d0fa00000000000000000000000000000000000000000000000000000000000000
2017-10-12 04:20:21.58 spid41s DROP TABLE "DBO"."TMP_DI_HP_GLCOVGTERM"
2017-10-12 04:20:21.58 spid41s inputbuf
2017-10-12 04:20:21.58 spid41s DROP TABLE "DBO"."TMP_DI_HP_GLCOVGTERM"
2017-10-12 04:20:21.58 spid41s resource-list
2017-10-12 04:20:21.58 spid41s keylock hobtid=281474978938880 dbid=6 objectname=ADMIN.sys.sysschobjs indexname=clst id=lockfa05f30980 mode=X associatedObjectId=281474978938880
2017-10-12 04:20:21.58 spid41s owner-list
2017-10-12 04:20:21.58 spid41s owner id=processf7dca9eca8 mode=X
2017-10-12 04:20:21.58 spid41s waiter-list
2017-10-12 04:20:21.58 spid41s waiter id=processf7c7e27848 mode=S requestType=wait
2017-10-12 04:20:21.58 spid41s keylock hobtid=562949956108288 dbid=6 objectname=ADMIN.sys.syscolpars indexname=nc id=lockfa05447680 mode=U associatedObjectId=562949956108288
2017-10-12 04:20:21.58 spid41s owner-list
2017-10-12 04:20:21.58 spid41s owner id=processf7c7e27848 mode=S
2017-10-12 04:20:21.58 spid41s waiter-list
2017-10-12 04:20:21.58 spid41s waiter id=processf7dca9eca8 mode=X requestType=convert -
The error log might give a hint but it is not enough information to solve the problem, we need more information.
At the first glance, it looks like you have an implicit conversion on a join column which locks the whole table. - Eirikur Eiriksson - Saturday, October 14, 2017 12:31 AMThe error log might give a hint but it is not enough information to solve the problem, we need more information.
At the first glance, it looks like you have an implicit conversion on a join column which locks the whole table.Hey, Ed Wagner! Here's yet another symptom of implicit conversions for your list of reasons to avoid such things.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply