It is clear that the above solution optimizes for updates, and assumes that a key you're trying to write to will already exist in the table as least as often as it doesn't. Some folks would love to see all blocking removed from all scenarios, but some of that is blocking you absolutely want for data integrity. That you're getting lots of deadlock victims or key violation errors, but they're happening quickly, is not a good performance metric. Note that overall throughput on a highly concurrent system might suffer, but that is a trade-off you should be willing to make. In both cases, the writer who won the race loses their data to anything the "loser" updated after them. The other will have to wait until the locks are released to even check for existence, and be forced to update. If the key doesn't exist, one session will "win" and insert the row.If the key exists and two sessions try to update it at the same time, they'll both take turns and update the row successfully, like before.In this case, one path only incurs a single index operation. Combining this logic with the high-level operations that would have to happen in the database, it is slightly simpler: In the best case, if you are updating a row that already exists, it will be more efficient to only locate that row once. It's a little more code, but it's 1000% safer, and even in the worst case (the row does not already exist), it performs the same as the anti-pattern. SERIALIZABLE is used to protect against changes to the underlying data throughout the transaction (ensure a row that doesn't exist continues to not exist).UPDLOCK is used to protect against conversion deadlocks at the statement level (let another session wait instead of encouraging a victim to retry).Why do we need two hints? Isn't UPDLOCK enough? t (, val ) VALUES, ) END COMMIT TRANSACTION t WITH ( UPDLOCK, SERIALIZABLE ) SET val = WHERE = IF = 0 BEGIN INSERT dbo. 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):īEGIN TRANSACTION UPDATE dbo. Make sure you read all the comments on all three posts, too. What To Avoid If You Want To Use MERGE – where he confirmed once again that there are still plenty of valid reasons to continue avoiding MERGE.Be Careful with the Merge Statement – where he checked his enthusiasm about MERGE and,.Mythbusting: Concurrent Update/Insert Solutions – where he acknowledged that leaving the initial logic in place and only elevating the isolation level just changed key violations to deadlocks.Michael Swart has also treated this subject multiple times: "Many people are saying…"ĭan 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 expect a column to act like a key, make it official and add a constraint. But if you are lulled into a sense of security that the IF EXISTS check protects you from duplicates (or key violations), that is a surprise waiting to happen. Deadlocks and exceptions can be handled easily with things like error handling, XACT_ABORT, and retry logic, depending on how frequently you expect collisions. That last one is the worst, IMHO, because it's the one that potentially corrupts data. insert duplicate key values if that column isn't properly constrained.raise key violation errors that shouldn't have happened or,.deadlock because of incompatible locks.If the key doesn't exist, but both sessions pass the existence check the same way, anything could happen when they both try to insert:. Paul White talks about the internal mechanics in greater detail here, and Martin Smith talks about some other nuances here. This isn't a problem on its own, and is how we should expect a system with concurrency to work.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |