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.
Author |
Topic |
ryanlcs
Yak Posting Veteran
62 Posts |
Posted - 2013-01-02 : 04:36:49
|
HiI 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 thishttp://msdn.microsoft.com/en-us/library/ms191242(v=sql.105).aspx |
|
|
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,inputbuf01/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=0x03000e002a15851633ac91003aa10000010000000000000001/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 NULL01/02/2013 14:48:13,spid25s,Unknown,frame procname=adhoc line=1 stmtstart=206 stmtend=1412 sqlhandle=0x020000008192322bf6af01aa0c377f29badb64d0f42ab7f801/02/2013 14:48:13,spid25s,Unknown,executionStack01/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=12805601/02/2013 14:48:13,spid25s,Unknown,Proc [Database Id = 14 Object Id = 377820458]01/02/2013 14:48:13,spid25s,Unknown,inputbuf01/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=0x03000e002a15851633ac91003aa10000010000000000000001/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 NULL01/02/2013 14:48:13,spid25s,Unknown,frame procname=adhoc line=1 stmtstart=206 stmtend=1412 sqlhandle=0x02000000d60eb814ac8047653084cb962ef3c3b3c4fc927201/02/2013 14:48:13,spid25s,Unknown,executionStack01/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=12805601/02/2013 14:48:13,spid25s,Unknown,Proc [Database Id = 14 Object Id = 377820458]01/02/2013 14:48:13,spid25s,Unknown,inputbuf01/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=0x03000e002a15851633ac91003aa10000010000000000000001/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 NULL01/02/2013 14:48:13,spid25s,Unknown,frame procname=adhoc line=1 stmtstart=206 stmtend=1412 sqlhandle=0x02000000863f3d23803340c3702612e09fe24be34933352701/02/2013 14:48:13,spid25s,Unknown,executionStack01/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=12805601/02/2013 14:48:13,spid25s,Unknown,process-list01/02/2013 14:48:13,spid25s,Unknown,deadlock victim=process5e31b8801/02/2013 14:48:13,spid25s,Unknown,deadlock-list |
|
|
|
|
|
|
|