Deadlock and locking in multi threading

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Administration

Deadlock and locking in multi threading

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 below

deadlock-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=0x02000000bd549f23021e71cd3bda41de4f69ff013f100c5a
update tldebtp set TLWWST = 'N', TLWBNB = TLWBNB + 1, TLXBNB = TLXBNB + 1, TLJITT = @P16 where TLHUTT = @P17;
frame procname=unknown line=1 sqlhandle=0x000000000000000000000000000000000000000000000000
unknown
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 Shaw
SQL 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=0x02000000bd549f23021e71cd3bda41de4f69ff013f100c5a
update tldebtp set TLWWST = 'N', TLWBNB = TLWBNB + 1, TLXBNB = TLXBNB + 1, TLJITT = @P16 where TLHUTT = @P17;
frame procname=unknown line=1 sqlhandle=0x000000000000000000000000000000000000000000000000
unknown
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=0x02000000bd549f23021e71cd3bda41de4f69ff013f100c5a
update tldebtp set TLWWST = 'N', TLWBNB = TLWBNB + 1, TLXBNB = TLXBNB + 1, TLJITT = @P16 where TLHUTT = @P17;
frame procname=unknown line=1 sqlhandle=0x000000000000000000000000000000000000000000000000
unknown
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=0x02000000bd549f23021e71cd3bda41de4f69ff013f100c5a
update tldebtp set TLWWST = 'N', TLWBNB = TLWBNB + 1, TLXBNB = TLXBNB + 1, TLJITT = @P16 where TLHUTT = @P17;
frame procname=unknown line=1 sqlhandle=0x000000000000000000000000000000000000000000000000
unknown
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 Shaw
SQL 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 code

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

It 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 Shaw
SQL 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 Shaw
SQL 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 Shaw
SQL 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.