A deadlock occurs when one or many actions or SQL statements compete for data access and table updates or manipulation at the same time as another process or action. Since it works with a transaction, one process can't move forward or complete because the other process or action holds, and is waiting for a resource, hence a deadlock.
So, in a database, records of process one would attempt to update, but process two would like to update some of the same rows, or a conflict of another table in which the transaction occurs. Some form of data locking mechanism to manage and reduce conflict must happen. Code around the transaction can be added with a retry indicator of three, for example, so if there is a deadlock, it will retry, and after the third attempt, it would roll back or give the user an opportunity to retry.
Deadlocking is a common occurrence in multithreaded and multiprocessing operating systems, which are performing a variety of tasks and attempting to complete with positive results.
See the following example on how SQL transactions will end up in a deadlock. In this case, both transactions/sessions will be in a deadlock state. Session 2 will try to insert/write to the database and create a locking to get exclusive rights to protect, and ensure it can insert the record without any problem. Session 1 tries to write as well, which also causes a deadlock situation. Effectively, we have a situation where Session 1 and Session 2 do not want to lose access or control until the other leaves, which lets the deadlock continue, as shown here:
|
Session 1 |
Session 2 |
|---|---|
|
|
|
|
| |
|
| |
|
| |
|
| |
|
| |
|
|