This prevents other processes from changing this exact row until we’re finished with it. If the Seek hits an existing row, SQL Server will place a key lock on that specific row.
#Sql deadlock with nolock update#
If that were to happen, we would get an unexpected outcome, like update a row that no longer exists, or inserting a row that someone else has already created.
In a database with high transaction throughput, there’s always the risk that the table could change in the short time after we perform the Seek, but before we execute the Merge. A tiny fraction of a millisecond later, the outcome of that Seek determines if we perform an UPDATE or an INSERT in the Clustered Index Merge operator to the left. That’s the Clustered Index Seek in our case. This explains pretty well what the MERGE statement does:įrom right to left, the first order of business is to establish if the row exists or not. Here’s what our MERGE statement looks like in the execution plan. With Read Committed, locks are placed on objects that you modify, in order to prevent other processes from reading something that you may or may not choose to commit later on.īut Serializable places locks even on things you look at – because if you’ve looked at it, SQL Server makes sure that it looks the same if you come back and look at it later on. To maintain an isolation level’s promise, and certainly that of the Serializable isolation level, SQL Server needs to place locks. If you want to dig a little deeper, check out Klaus Aschenbrenner’s posts on Lock Escalation, Update Locks, and Intent lock. “I promise that what you saw will still be true if and when you look again.”ĭisclaimer: Simplifications ahead. The “ Serializable” isolation level is the strictest of them all.It roughly translates to “To somebody, this may have been accurate at the time, but we don’t know if that transaction will even be committed.” You may have played around with “ Read Uncommitted” (the NOLOCK hint).The default “ Read Committed” isolation level, basically means: “I promise that what I showed you was accurate and committed at the time.”.I have a whole post on isolation levels, but you can think of isolation levels as the server’s “SLA”, a promise if you will, to the client application. The HOLDLOCK hint tells SQL Server to use the Serializable transaction isolation level. One of the generally accepted recommendations to avoid most of these issues is to add a WITH (HOLDLOCK) hint to the target table. For a very simple use-case like the one we’re working on here, we’ll probably be fine. My own takeaway from this post is that if you’re on an older SQL Server version, or if you do funky things like filtered indexes or indexed views, you should carefully review the list if there are issues that might impact you. Years ago, Aaron Bertrand compiled an excellent blog post with all the reported issues with the MERGE statement (there are many), and a surprising amount of them are marked as “Won’t fix” by Microsoft – either because it’s “By design” or because it’s just plain-old not important enough for Microsoft to fix them. You’ll notice that there’s a HOLDLOCK hint on the target table. MERGE INTO dbo.ApplicationState WITH (HOLDLOCK) AS t
0 kommentar(er)
