Author |
Topic |
InfraDBA
Starting Member
38 Posts |
Posted - 2011-07-12 : 06:30:34
|
Hi all, We are facing a problem in the production.There is a batch which runs once every week.It runs on multithreading.Different threads will update different rows for the same table. begin insert into TMDEBTP (TMADCD, TMGFCE, TMV7NB, TMGICE, TMHZTT, TMH0TT, TMHYTT, TMKFTT, TMHXTT, TMH2TT, TMH3TT, TMH5TT, TMH7TT, TMFFTY, TMUZDT, TMXDST, TMZZCF, TMAAVN, TMUEDT, TMACTM) select TLADCD, TLGFCE, TLV7NB, 2, ?, ?, ?, ?, ?, ?, ?, substring(?, 1,25), ?, ?, dbo.fn_ConvertZonedDecimalToInteger(?), 'KP', 'GBP', 'ICBS', ?, ? from TLDEBTP where TLHUTT = ?; update tldebtp set TLWWST = 'Y', TLWBNB = TLWBNB + 1, TLXBNB = TLXBNB + 1 where TLHUTT = ?; But the following deadlock occurred last time.I checked the stats are updated.Please see following error.Can you see belowdeadlock-list deadlock victim=process421c9b8 process-list process id=process4212868 taskpriority=0 logused=24488 waitresource=KEY: 5:72057599723896832 (9600482e19a5) waittime=312 ownerId=550287167 transactionname=implicit_transaction lasttranstarted=2011-07-05T10:21:18.290 XDES=0x31dd04370 lockMode=U schedulerid=2 kpid=4148 status=suspended spid=75 sbid=0 ecid=0 priority=0 transcount=2 lastbatchstarted=2011-07-05T10:21:18.863 lastbatchcompleted=2011-07-05T10:21:18.860 clientapp=Microsoft SQL Server JDBC Driver hostname=PPVTBSDBNKAPP06 hostpid=0 loginname=UKPREBANK\svc_dbmn_batch_pre isolationlevel=read committed (2) xactid=550287167 currentdb=5 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128058 executionStack frame procname=adhoc line=9 stmtstart=1610 stmtend=1890 sqlhandle=0x02000000bd549f23021e71cd3bda41de4f69ff013f100c5aupdate tldebtp set TLWWST = 'N', TLWBNB = TLWBNB + 1, TLXBNB = TLXBNB + 1, TLJITT = @P16 where TLHUTT = @P17; frame procname=unknown line=1 sqlhandle=0x000000000000000000000000000000000000000000000000unknown inputbuf(@P0 nvarchar(4000),@P1 nvarchar(4000),@P2 nvarchar(4000),@P3 nvarchar(4000),@P4 nvarchar(4000),@P5 nvarchar(4000),@P6 nvarchar(4000),@P7 nvarchar(4000),@P8 nvarchar(4000),@P9 nvarchar(4000),@P10 nvarchar(4000),@P11 nvarchar(4000),@P12 nvarchar(4000),@P13 nvarchar(4000),@P14 nvarchar(4000),@P15 nvarchar(4000),@P16 nvarchar(4000),@P17 nvarchar(4000))if (@P0 > 0) begin insert into TMDEBTP (TMADCD, TMGFCE, TMV7NB, TMGICE, TMHZTT, TMH0TT, TMHYTT, TMKFTT, TMHXTT, TMH1TT, TMH2TT, TMH3TT, TMH5TT, TMH7TT, TMFFTY, TMUZDT, TMXAST, TMZZCF, TMAAVN, TMUEDT, TMACTM) select TLADCD, TLGFCE, TLV7NB, 1, @P1, @P2, @P3, @P4, @P5, @P6, @P7, @P8, substring(@P9, 1,25), @P10, @P11, dbo.fn_ConvertZonedDecimalToInteger(@P12), 'N', 'GBP', 'ICBS', @P13, @P14 from TLDEBTP where TLHUTT = @P15; update tldebtp set TLWWST = 'N', TLWBNB = TLWBNB + 1, TLXBNB = TLXBNB + 1, TLJITT = @P16 where TLHUTT = @P17; end MAX DOP IS 1.It is SQL server enterprise edition with sp3 cu3.The tables have indexes in place.Can you please let me know of anything else which could have caused the issue? |
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-07-12 : 08:46:24
|
That's not the entire deadlock graph. Please post the whole thing.--Gail ShawSQL Server MVP |
 |
|
InfraDBA
Starting Member
38 Posts |
Posted - 2011-07-12 : 09:20:11
|
Sure.. |
 |
|
InfraDBA
Starting Member
38 Posts |
Posted - 2011-07-12 : 09:25:12
|
deadlock-list deadlock victim=process424ec58 process-list process id=process4230718 taskpriority=0 logused=1309696 waitresource=KEY: 5:72057599723896832 (960080c0baa7) waittime=78 ownerId=550263924 transactionname=implicit_transaction lasttranstarted=2011-07-05T10:16:39.737 XDES=0x102e19040 lockMode=U schedulerid=5 kpid=2512 status=suspended spid=80 sbid=0 ecid=0 priority=0 transcount=2 lastbatchstarted=2011-07-05T10:20:25.873 lastbatchcompleted=2011-07-05T10:20:25.870 clientapp=Microsoft SQL Server JDBC Driver hostname=Hostname1 hostpid=0 loginname=Login1 isolationlevel=read committed (2) xactid=550263924 currentdb=5 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128058 executionStack frame procname=adhoc line=9 stmtstart=1610 stmtend=1890 sqlhandle=0x02000000bd549f23021e71cd3bda41de4f69ff013f100c5aupdate tldebtp set TLWWST = 'N', TLWBNB = TLWBNB + 1, TLXBNB = TLXBNB + 1, TLJITT = @P16 where TLHUTT = @P17; frame procname=unknown line=1 sqlhandle=0x000000000000000000000000000000000000000000000000unknown inputbuf(@P0 nvarchar(4000),@P1 nvarchar(4000),@P2 nvarchar(4000),@P3 nvarchar(4000),@P4 nvarchar(4000),@P5 nvarchar(4000),@P6 nvarchar(4000),@P7 nvarchar(4000),@P8 nvarchar(4000),@P9 nvarchar(4000),@P10 nvarchar(4000),@P11 nvarchar(4000),@P12 nvarchar(4000),@P13 nvarchar(4000),@P14 nvarchar(4000),@P15 nvarchar(4000),@P16 nvarchar(4000),@P17 nvarchar(4000))if (@P0 > 0) begin insert into TMDEBTP (TMADCD, TMGFCE, TMV7NB, TMGICE, TMHZTT, TMH0TT, TMHYTT, TMKFTT, TMHXTT, TMH1TT, TMH2TT, TMH3TT, TMH5TT, TMH7TT, TMFFTY, TMUZDT, TMXAST, TMZZCF, TMAAVN, TMUEDT, TMACTM) select TLADCD, TLGFCE, TLV7NB, 1, @P1, @P2, @P3, @P4, @P5, @P6, @P7, @P8, substring(@P9, 1,25), @P10, @P11, dbo.fn_ConvertZonedDecimalToInteger(@P12), 'N', 'GBP', 'ICBS', @P13, @P14 from TLDEBTP where TLHUTT = @P15; update tldebtp set TLWWST = 'N', TLWBNB = TLWBNB + 1, TLXBNB = TLXBNB + 1, TLJITT = @P16 where TLHUTT = @P17; end process id=process4244478 taskpriority=0 logused=25928 waitresource=KEY: 5:72057599723896832 (9600482e19a5) waittime=93 ownerId=550281114 transactionname=implicit_transaction lasttranstarted=2011-07-05T10:20:25.327 XDES=0x247760370 lockMode=U schedulerid=7 kpid=4224 status=suspended spid=88 sbid=0 ecid=0 priority=0 transcount=2 lastbatchstarted=2011-07-05T10:20:25.553 lastbatchcompleted=2011-07-05T10:20:25.553 clientapp=Microsoft SQL Server JDBC Driver hostname=hostname1 hostpid=0 loginname=login1 isolationlevel=read committed (2) xactid=550281114 currentdb=5 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128058 executionStack frame procname=adhoc line=9 stmtstart=1610 stmtend=1890 sqlhandle=0x02000000bd549f23021e71cd3bda41de4f69ff013f100c5aupdate tldebtp set TLWWST = 'N', TLWBNB = TLWBNB + 1, TLXBNB = TLXBNB + 1, TLJITT = @P16 where TLHUTT = @P17; frame procname=unknown line=1 sqlhandle=0x000000000000000000000000000000000000000000000000unknown inputbuf(@P0 nvarchar(4000),@P1 nvarchar(4000),@P2 nvarchar(4000),@P3 nvarchar(4000),@P4 nvarchar(4000),@P5 nvarchar(4000),@P6 nvarchar(4000),@P7 nvarchar(4000),@P8 nvarchar(4000),@P9 nvarchar(4000),@P10 nvarchar(4000),@P11 nvarchar(4000),@P12 nvarchar(4000),@P13 nvarchar(4000),@P14 nvarchar(4000),@P15 nvarchar(4000),@P16 nvarchar(4000),@P17 nvarchar(4000))if (@P0 > 0) begin insert into TMDEBTP (TMADCD, TMGFCE, TMV7NB, TMGICE, TMHZTT, TMH0TT, TMHYTT, TMKFTT, TMHXTT, TMH1TT, TMH2TT, TMH3TT, TMH5TT, TMH7TT, TMFFTY, TMUZDT, TMXAST, TMZZCF, TMAAVN, TMUEDT, TMACTM) select TLADCD, TLGFCE, TLV7NB, 1, @P1, @P2, @P3, @P4, @P5, @P6, @P7, @P8, substring(@P9, 1,25), @P10, @P11, dbo.fn_ConvertZonedDecimalToInteger(@P12), 'N', 'GBP', 'ICBS', @P13, @P14 from TLDEBTP where TLHUTT = @P15; update tldebtp set TLWWST = 'N', TLWBNB = TLWBNB + 1, TLXBNB = TLXBNB + 1, TLJITT = @P16 where TLHUTT = @P17; end process id=process424ec58 taskpriority=0 logused=25380 waitresource=KEY: 5:72057599723896832 (9600482e19a5) waittime=78 ownerId=550281260 transactionname=implicit_transaction lasttranstarted=2011-07-05T10:20:25.933 XDES=0x8021f450 lockMode=U schedulerid=8 kpid=5900 status=suspended spid=84 sbid=0 ecid=0 priority=0 transcount=2 lastbatchstarted=2011-07-05T10:20:26.513 lastbatchcompleted=2011-07-05T10:20:26.510 clientapp=Microsoft SQL Server JDBC Driver hostname=hostname1 hostpid=0 loginname=login1 isolationlevel=read committed (2) xactid=550281260 currentdb=5 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128058 executionStack frame procname=adhoc line=9 stmtstart=1610 stmtend=1890 sqlhandle=0x02000000bd549f23021e71cd3bda41de4f69ff013f100c5aupdate tldebtp set TLWWST = 'N', TLWBNB = TLWBNB + 1, TLXBNB = TLXBNB + 1, TLJITT = @P16 where TLHUTT = @P17; frame procname=unknown line=1 sqlhandle=0x000000000000000000000000000000000000000000000000unknown inputbuf(@P0 nvarchar(4000),@P1 nvarchar(4000),@P2 nvarchar(4000),@P3 nvarchar(4000),@P4 nvarchar(4000),@P5 nvarchar(4000),@P6 nvarchar(4000),@P7 nvarchar(4000),@P8 nvarchar(4000),@P9 nvarchar(4000),@P10 nvarchar(4000),@P11 nvarchar(4000),@P12 nvarchar(4000),@P13 nvarchar(4000),@P14 nvarchar(4000),@P15 nvarchar(4000),@P16 nvarchar(4000),@P17 nvarchar(4000))if (@P0 > 0) begin insert into TMDEBTP (TMADCD, TMGFCE, TMV7NB, TMGICE, TMHZTT, TMH0TT, TMHYTT, TMKFTT, TMHXTT, TMH1TT, TMH2TT, TMH3TT, TMH5TT, TMH7TT, TMFFTY, TMUZDT, TMXAST, TMZZCF, TMAAVN, TMUEDT, TMACTM) select TLADCD, TLGFCE, TLV7NB, 1, @P1, @P2, @P3, @P4, @P5, @P6, @P7, @P8, substring(@P9, 1,25), @P10, @P11, dbo.fn_ConvertZonedDecimalToInteger(@P12), 'N', 'GBP', 'ICBS', @P13, @P14 from TLDEBTP where TLHUTT = @P15; update tldebtp set TLWWST = 'N', TLWBNB = TLWBNB + 1, TLXBNB = TLXBNB + 1, TLJITT = @P16 where TLHUTT = @P17; end resource-list keylock hobtid=72057599723896832 dbid=5 objectname=dbo.TLDEBTP indexname=TLDEBTP_PK id=lock1f1c77900 mode=X associatedObjectId=72057599723896832 owner-list owner id=process424ec58 mode=X waiter-list waiter id=process4230718 mode=U requestType=wait keylock hobtid=72057599723896832 dbid=5 objectname=dbo.TLDEBTP indexname=TLDEBTP_PK id=lock200104d00 mode=X associatedObjectId=72057599723896832 owner-list owner id=process4230718 mode=X waiter-list waiter id=process4244478 mode=U requestType=wait waiter id=process424ec58 mode=U requestType=wait |
 |
|
InfraDBA
Starting Member
38 Posts |
Posted - 2011-07-12 : 10:51:36
|
Hi Gilamonster, Please reply. |
 |
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-07-13 : 03:53:05
|
Please be patient. I'm a SQL consultant with a large pile of paying work. I answer forum posts in my spare time and deadlock posts are time-consuming to figure out and answer.--Gail ShawSQL Server MVP |
 |
|
InfraDBA
Starting Member
38 Posts |
Posted - 2011-07-14 : 04:01:07
|
I don't want to push but I will really appreciate any help. |
 |
|
InfraDBA
Starting Member
38 Posts |
Posted - 2011-08-01 : 07:18:43
|
I am not sure if you could have managed to get some time? |
 |
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-08-06 : 17:22:24
|
Ok.... Long delay I know.All three of the processes involved in the deadlock are running the same codeif (@P0 > 0) begin insert into TMDEBTP (TMADCD, TMGFCE, TMV7NB, TMGICE, TMHZTT, TMH0TT, TMHYTT, TMKFTT, TMHXTT, TMH1TT, TMH2TT, TMH3TT, TMH5TT, TMH7TT, TMFFTY, TMUZDT, TMXAST, TMZZCF, TMAAVN, TMUEDT, TMACTM) select TLADCD, TLGFCE, TLV7NB, 1, @P1, @P2, @P3, @P4, @P5, @P6, @P7, @P8, substring(@P9, 1,25), @P10, @P11, dbo.fn_ConvertZonedDecimalToInteger(@P12), 'N', 'GBP', 'ICBS', @P13, @P14 from TLDEBTP where TLHUTT = @P15; update tldebtp set TLWWST = 'N', TLWBNB = TLWBNB + 1, TLXBNB = TLXBNB + 1, TLJITT = @P16 where TLHUTT = @P17; endIt appears (but I can't confirm) that the row being updated is a different row to the one being inserted. What's happening in the deadlock is that each of the three processes is taking an exclusive lock for the insert. They are then requesting an update lock for the update, but the lock being requested is already held by one of the inserts.A few possible things you can look at: Does that really need to be done like that (insert followed by update), is there no other way? The two are running in an implicit transaction (meaning a transaction begins with the first modification and must be explicitly committed). I don't see a commit in your code, check that the transaction is indeed been committed as soon as the update completes. If the two do not have to be done as an atomic operation, turn the implicit transactions off (it's a JDBC setting) JDBC is passing all the parameters as nvarchar (again, that's a JDBC default). If the column TLHUTT is actually a varchar, this means it's an implicit conversion and will result in an index scan rather than a more efficient index seek (Is TLHUTT the primary key? It look like it is)--Gail ShawSQL Server MVP |
 |
|
InfraDBA
Starting Member
38 Posts |
Posted - 2011-08-18 : 05:37:52
|
Thanks very much..So you want to rule out the possibilities for multi threading? |
 |
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-08-18 : 10:17:03
|
It's not directly caused by your multi-threading, but the deadlock probably wouldn't occur if the process was single-threaded.That said, look at the implicit conversions and implicit transactions first.--Gail ShawSQL Server MVP |
 |
|
namman
Constraint Violating Yak Guru
285 Posts |
Posted - 2011-08-18 : 22:47:41
|
Rebuild the index TLDEBTP_PK |
 |
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-08-19 : 06:26:13
|
Unlikely to help.--Gail ShawSQL Server MVP |
 |
|
InfraDBA
Starting Member
38 Posts |
Posted - 2011-08-19 : 07:16:02
|
I have asked the developers with the suggestions.I really appreciate the help. |
 |
|
|
|
|