Let’s create that and try to recreate our original deadlock situation… What about the following index… CREATE INDEX ix_Name_Surname_INCLUDE If we can make it easier for SQL to find that row, we might be able to avoid that block and in turn, avoid the deadlock situation. It can’t go any further at that point so ends up getting blocked. So what that means is that SQL is scanning the clustered index from top to bottom until it hits the locked row. Let’s quickly check the execution plan and see what’s happening…Ī clustered index scan. Now lets run our SELECT statement from the second session and see what happens.
Sql deadlock resolve update#
The first thing that happens is the UPDATE statement takes out a lock on the row it’s updating. Now for the big question, can we solve this using an index? Before we look at that, let’s have a look at what’s going on inside our ‘name’ table during this situation. Let’s run the following from session 2 SELECT Street, CityĪ pretty straight forward deadlock scenario, I’m not going to explain that here, there’re plenty of resources explaining how and why this happens out there. That’s to be expected of course, session 2 is holding an exclusive lock on ‘Name’ thanks to the UPDATE that it’s not yet committed. Now, back to the first session and I’ll run the following select statement… SELECT ForeName, Surname
A pretty standard situation in SQL and not at all sinister. We’re now in a place where we’ve got two sessions each holding an exclusive row lock in their respective tables.
Sql deadlock resolve code#
On the first session, I’ll run the following code BEGIN TRANSACTION Now I’ll open up two sessions on that database and create a simple deadlock situation. I’ll then populate the pair of them with 500 rows. I’m going to start by creating a couple of rather simple tables. Let’s create a rather simple, contrived deadlock situation. So, can we solve a deadlocking issue with an index? The reaction I got was along the lines of, ‘What, how can in index solve a deadlocking issue?’
A while back, I was having a conversation about a deadlocking issue and suggested that an index could perhaps help solve it.
0 kommentar(er)
