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
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Deadlock occur during concurrent INSERT

Author  Topic 

ryanlcs
Yak Posting Veteran

62 Posts

Posted - 2013-01-02 : 04:36:49
Hi

I had a deadlock issue, and after some trace, found that it is due to concurrent insert that caused the deadlock. The nature of the system is there is multiple thread running, and updating to 1 table at same time.

How can I overcome this? I cannot avoid running multiple thread, so what other alternative I have at coding level?

Thanks.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2013-01-02 : 09:05:46
Enable TraceFlag 1204 and 1222 and post the result.

Read this
http://msdn.microsoft.com/en-us/library/ms191242(v=sql.105).aspx
Go to Top of Page

ryanlcs
Yak Posting Veteran

62 Posts

Posted - 2013-01-02 : 19:30:33
This is the result, pls note that the table and column name has been renamed, due to some policy constraint.

In this case, 3 statements are inserting data to same table, and from the deadlock graph, the error message indicating error occured on the index of the table.

Pls advice.

01/02/2013 14:48:13,spid25s,Unknown,inputbuf
01/02/2013 14:48:13,spid25s,Unknown,EXEC(@sql)
01/02/2013 14:48:13,spid25s,Unknown,frame procname=tableAA line=56 stmtstart=5470 stmtend=5492 sqlhandle=0x03000e002a15851633ac91003aa100000100000000000000
01/02/2013 14:48:13,spid25s,Unknown,INSERT INTO tableAA(Column1, Column2, COlumn3) SELECT Column1, Column2, COlumn3 FROM tableSource_101 (NOLOCK) WHERE SDate IS NOT NULL AND STime IS NOT NULL AND EDate IS NOT NULL AND ETime IS NOT NULL
01/02/2013 14:48:13,spid25s,Unknown,frame procname=adhoc line=1 stmtstart=206 stmtend=1412 sqlhandle=0x020000008192322bf6af01aa0c377f29badb64d0f42ab7f8
01/02/2013 14:48:13,spid25s,Unknown,executionStack
01/02/2013 14:48:13,spid25s,Unknown,process id=process9609048 taskpriority=0 logused=0 waitresource=KEY: 15:72057742721089536 (798da85b5cba) waittime=3078 ownerId=710200386 transactionname=user_transaction lasttranstarted=2013-01-02T14:47:34.023 XDES=0x17f3bb560 lockMode=RangeS-U schedulerid=1 kpid=8012 status=suspended spid=92 sbid=0 ecid=0 priority=0 trancount=2 lastbatchstarted=2013-01-02T14:47:34.027 lastbatchcompleted=2013-01-02T14:47:34.023 clientapp=.Net SqlClient Data Provider hostname=RQDB-L8 hostpid=560 loginname=testdb isolationlevel=read committed (2) xactid=710200386 currentdb=14 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128056
01/02/2013 14:48:13,spid25s,Unknown,Proc [Database Id = 14 Object Id = 377820458]
01/02/2013 14:48:13,spid25s,Unknown,inputbuf
01/02/2013 14:48:13,spid25s,Unknown,EXEC(@sql)
01/02/2013 14:48:13,spid25s,Unknown,frame procname=tableAA line=56 stmtstart=5470 stmtend=5492 sqlhandle=0x03000e002a15851633ac91003aa100000100000000000000
01/02/2013 14:48:13,spid25s,Unknown,INSERT INTO tableAA(Column1, Column2, COlumn3) SELECT Column1, Column2, COlumn3 FROM tableSource_404 (NOLOCK) WHERE SDate IS NOT NULL AND STime IS NOT NULL AND EDate IS NOT NULL AND ETime IS NOT NULL
01/02/2013 14:48:13,spid25s,Unknown,frame procname=adhoc line=1 stmtstart=206 stmtend=1412 sqlhandle=0x02000000d60eb814ac8047653084cb962ef3c3b3c4fc9272
01/02/2013 14:48:13,spid25s,Unknown,executionStack
01/02/2013 14:48:13,spid25s,Unknown,process id=process963abc8 taskpriority=0 logused=0 waitresource=KEY: 15:72057742721089536 (ac05c129c32d) waittime=14428 ownerId=710279332 transactionname=user_transaction lasttranstarted=2013-01-02T14:47:59.007 XDES=0x80048af0 lockMode=RangeS-U schedulerid=6 kpid=7112 status=suspended spid=136 sbid=0 ecid=0 priority=0 trancount=2 lastbatchstarted=2013-01-02T14:47:59.010 lastbatchcompleted=2013-01-02T14:47:59.007 clientapp=.Net SqlClient Data Provider hostname=RQDB-L6 hostpid=2420 loginname=testdb isolationlevel=read committed (2) xactid=710279332 currentdb=14 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128056
01/02/2013 14:48:13,spid25s,Unknown,Proc [Database Id = 14 Object Id = 377820458]
01/02/2013 14:48:13,spid25s,Unknown,inputbuf
01/02/2013 14:48:13,spid25s,Unknown,EXEC(@sql)
01/02/2013 14:48:13,spid25s,Unknown,frame procname=tableAA line=56 stmtstart=5470 stmtend=5492 sqlhandle=0x03000e002a15851633ac91003aa100000100000000000000
01/02/2013 14:48:13,spid25s,Unknown,INSERT INTO tableAA(Column1, Column2, COlumn3) SELECT Column1, Column2, COlumn3 FROM tableSource_402 (NOLOCK) WHERE SDate IS NOT NULL AND STime IS NOT NULL AND EDate IS NOT NULL AND ETime IS NOT NULL
01/02/2013 14:48:13,spid25s,Unknown,frame procname=adhoc line=1 stmtstart=206 stmtend=1412 sqlhandle=0x02000000863f3d23803340c3702612e09fe24be349333527
01/02/2013 14:48:13,spid25s,Unknown,executionStack
01/02/2013 14:48:13,spid25s,Unknown,process id=process5e31b88 taskpriority=0 logused=0 waitresource=KEY: 15:72057742721089536 (d312e7370332) waittime=3078 ownerId=710251089 transactionname=user_transaction lasttranstarted=2013-01-02T14:47:44.603 XDES=0x63c2feb50 lockMode=RangeS-U schedulerid=13 kpid=6940 status=suspended spid=99 sbid=0 ecid=0 priority=0 trancount=2 lastbatchstarted=2013-01-02T14:47:44.607 lastbatchcompleted=2013-01-02T14:47:44.603 clientapp=.Net SqlClient Data Provider hostname=RQDB-L6 hostpid=2420 loginname=testdb isolationlevel=read committed (2) xactid=710251089 currentdb=14 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128056
01/02/2013 14:48:13,spid25s,Unknown,process-list
01/02/2013 14:48:13,spid25s,Unknown,deadlock victim=process5e31b88
01/02/2013 14:48:13,spid25s,Unknown,deadlock-list
Go to Top of Page
   

- Advertisement -