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. Deadlocks and blocking locks are often confused.
0 kommentar(er)
