How Do You Fix Deadlocks?įixing deadlocks can be very complicated. The processes would hold their locks forever if SQL Server did not step in very quickly and kill one process. While both can have a shared lock on the same data, neither can get an exclusive lock on it because of the shared lock the other is holding. When they find the data they want to update, both try to convert their shared lock to an exclusive lock.
#Page lock deadlock sql server update
Imagine that two processes attempt to update data at roughly the same time. (The lock does not necessarily have to lock the entire table, but it is much simpler to describe what happens if we just assume it is a table lock.) After the process finds the data it wants to update, it has to convert its shared lock into an exclusive lock to be able to change the data. By default, an update begins by the process taking a shared lock on the table. Let’s illustrate a deadlock by taking a very common example: a deadlock involving two processes that try to update rows on the same page of a table. If the application does not detect the deadlock and take appropriate action, then the data remains incorrect and the company may end up with an irate customer. The process might end up as the victim in a deadlock situation and the change it made rolled back. For example, a process might be updating the invoice table to mark an invoice as paid. The application must trap deadlocks and take appropriate action when a command is rolled back. For simplicity, we will discuss only deadlocks involving two processes blocking each other.)ĭeadlocks threaten data consistency because a command from the application has to be killed and the data rolled back. (Most deadlocks involve two processes, but in some cases there may be more. SQL Server chooses the victim by looking at the cost to roll back each process. SQL Server runs a system process that looks for deadlocks and rolls back one or another of the processes to allow the remaining process to proceed. The most important difference is that the process holding a lock that blocks other processes will eventually complete and release the lock so the waiting processes can proceed.Ī deadlock will not resolve itself. However blocking locks and deadlocks differ in a number of ways. The symptoms are similar and both are related to contention for database resources by multiple processes. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.Deadlocks and blocking locks are often confused. I’m offering a 75% discount to my blog readers if you click from here. If this is the kind of SQL Server stuff you love learning about, you’ll love my training. This is true despite there being more overall locks taken for the update: īut the addition of the index makes row level locks a more obvious choice for both queries. We don’t need a rowlock hint on the select.We don’t need a rowlock hint on the update.If we start the process over, but add this index: CREATE INDEX ix_dammit_please_just_work With the rowlock hint here, our query finishes. SELECT TOP (1)įROM dbo.Users AS u WITH(READPAST, ROWLOCK) We need to change the locking hints slightly to avoid that. Our read query is shooting itself in the foot by trying to lock pages rather than rows. Going back to look at the locks it’s trying to take, we’ll see this: What’s amusing here is that our select query will… Still be blocked! Rather than exclusive page locks, we’re taking an exclusive key lock, and only intent exclusive locks elsewhere. We’re now asking SQL Server to only lock rows if it can, for as long as it can. We’ll need to change our update query to do this: BEGIN TRAN We take a single exclusive page lock, which means our readpast query can’t skip it. If we use sp_WhoIsActive = 1 we’ll get this back: If I run this query, it’ll take out locks we don’t want (without an index on Reputation). What it leaves out is that your READPAST query may also need to try to take row level shared locks. The documentation for it says that it allows you to skip over row level locks (that means you can’t skip over page or object level locks). Locking hints can be really handy in these situations, especially the READPAST hint. Other times, you only wanna work on something if it’s not locked by something else. Sometimes, you only wanna work on one thing.
0 kommentar(er)
