I believe that's automatic in mysql/mariadb: While I am sure that I am trying to create new record in TableA and TableB in respective transactions. Phantom rows can't exist here, so why use an isolation level that exists to prevent them? Recreating the SQL Server Deadlock. Each row in the table stores the last used ID LastID for the field named in IDName. Let us set up a table with some test data: The following two procedures are quite likely to embrace in a deadlock: The following loops should reproduce more than 20 deadlocks every time you run them. HiI had a deadlock issue, and after some trace, found that it is due to concurrent insert that caused the deadlock. Each new cumulative update for SQL Server contains all the hotfixes and all the security fixes that were included with the previous cumulative update. With standard locking mechanisms, I can never be so sure - adding an index or just getting another execution plan can cause deadlocks where before there were none. How to refuse to work when on vacation while keeping relationships as best as possible? I fixed a similar deadlock in a system last year by changing this: In general, selecting a COUNT just to determine presence or absence is quite wasteful. Your solution may be different, but you need to expose it to high concurrency, as I will demonstrate later.

Some folks would love to see all blocking removed from all scenarios, but some of that is blocking you absolutely want for data integrity. at the upserting multiple rows scenario, shouldn't be UPDLOCK, SERIALIZABLE hints used when inserting?

The nature of the system is there is multiple thread running, and updating to 1 table at same time.How can I overcome this? Sorry, I missed a key word (no pun intended) in your comment initially; I thought you were talking about the "single-row, insert first" approach. Ask Question Asked 10 years, 1 month ago. Enable TraceFlag 1204 and 1222 and post the result.Read thishttp://msdn.microsoft.com/en-us/library/ms191242(v=sql.105).aspx, 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. However, i'd be loathe to say for sure that you won't have any issues as there are just far too many variables By using our site, you acknowledge that you have read and understand our Cookie Policy, Privacy Policy, and our Terms of Service.

Dan Guzman talked about race conditions more than a decade ago in Conditional INSERT/UPDATE Race Condition and later in "UPSERT" Race Condition With MERGE. If you would rather optimize for inserts, knowing or guessing that inserts will be more likely than updates, you can flip the logic around and still have a safe upsert operation: There's also the "just do it" approach, where you blindly insert and let collisions raise exceptions to the caller: The cost of those exceptions will often outweigh the cost of checking first; you'll have to try it with a roughly accurate guess of hit/miss rate. As such it can be used to eliminate deadlocks. Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section. Use the following as a very basic illustration - you just need 2 connections open in SSMS. I've fixed many deadlocks in my career by simply adjusting to the following pattern (ditch the redundant check, wrap the sequence in a transaction, and protect the first table access with appropriate locking): Why do we need two hints? Why are so many coders still using Vim and Emacs? Is there a Linear Programming Library that natively supports fractions instead of floating point arithmetic? Can a landlord dictate what I put online? The short answer is it should be fine, people do this kind of thing all day, all the time. Thanks for your quick response. Thanks for response from @Colin'tHart. Transaction (Process ID ProcessId) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. So about same time as the whole procedure runs. In that case, I am trying to understand if there is a way to profile how long this resource was on hold so that thinking if I can work on improving the transaction elapsed time.