SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Deadlock occur during concurrent INSERT		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

ryanlcs
Yak Posting Veteran

61 Posts
Posted - 01/02/2013 :  04:36:49  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
7173 Posts
Posted - 01/02/2013 :  09:05:46  Show Profile  Reply with Quote
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

61 Posts
Posted - 01/02/2013 :  19:30:33  Show Profile  Reply with Quote
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
Edited by - ryanlcs on 01/02/2013 19:33:35
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000