SQL Server cannot lock an arbitrary search criteria like “WHERE id=123”, because that would create a computational nightmare. Remember, because we’re in Serializable, we expect the row we saw to be there next time we need it, and to look just the way we found it.īut if we didn’t find a row, there’s nothing specific to lock, because locks can only be placed on keys (rows), pages, partitions, tables and schemas. 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. 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. _log.MERGE INTO dbo.ApplicationState WITH (HOLDLOCK) AS t Protected static void Execute(string connectionString, string commandString) TimeSpan delay = TimeSpan.FromSeconds(5) Could I add here any code to handle it?Ĭan I do this handling inside a catch block?īuilding on response, I present a general purpose retry wrapper method: private static T Retry(Func func) If (Connection.State = ConnectionState.Closed) If (DatabaseType != enumDatabaseType.SQLCOMPACT)Ĭommand.CommandTimeout = Connection.ConnectionTimeout If(DatabaseType != enumDatabaseType.ORACLE)Ĭommand.CommandText ="BEGIN " + query + " END " Here is the code for a method we use: public int ExecuteQuery(string query) ![]() We want the deadlock victim to retry perhaps after some time delay, but I don't know if it is possible. Part of our team is working on the sql code and transactions to minimize these events, but I'm thinking about making this Database class robust against deadlock situations. We are experiencing serious problems with Deadlock situations in SQL Server 2000. For instance, when I need to execute a procedure, I call Database.ExecuteProcedure(procedureName, parametersAndItsValues). I have a class 'Database' that works as a wrapper for ADO.net.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |