![]() Again that might work and performance might not be too awful. It is possible that adding a TABLOCK hint instead would be enough to solve the problem without having to big an effect on your performance.įinally, you could also try adding PAGLOCK, XLOCK or both PAGLOCK and XLOCK. If the second user also needs to insert on the same page, then they're in a deadlock.ĪFAIK, there's only one (simple) way to be 100% sure that you cannot get a deadlock with this procedure and that would be to add a TABLOCKX hint to the MERGE, but that would probably have a really bad impact on performance. īut if another user has also gotten to step 1 on the same row/page, then the first user will be blocked from the Update, and. When it has a row without a match, it will try to insert the new Index Row first so it will request a row/page write-lock. The MATCH part of the MERGE checks the index for matches, read-locking those rows/pages as it goes. OK, after looking everything over a couple of times, I think that your basic assumption was correct. The foreign key is set up to cascade only on delete, and there were no deletions from the parent table.There are at most 28 rows are in the I have traced back through the code, and I cannot see anywhere that we start a transaction here. ![]() (If you view the image in a new tab, it is readable. no deadlocks, minimum impact on multi-threaded performance)? What is the best way to resolve this situation (i.e. I assume that two different threads are locking rows (or pages) in different orders when they are validating the constraints, and thus are deadlocking. My understanding was that the "with (holdlock)" was necessary to prevent insert/update race condition errors. It is called from many different threads, and I am consistently getting deadlocks between different processes calling this same statement. ![]() Also, there is a non-clustered index on CompanyId asc, UserId asc. CompanyId is a foreign key to a parent table. Insert (CompanyId, UserId, MyKey, MyValue) newData.MyKey, newData.MyValue) ĬompanyId, UserId, MyKey form the composite key for the target table. I have the following procedure (SQL Server 2008 R2): create procedure tt_CoUserdata readonly
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |