Database deadlock can be defined as a situation when a data is locked by two or more data sessions. The lock on a particular data is requested by the database session. Each lock used on a database is entirely different from others. The time of the database sessions waiting to force the database lock is wasted. This particular scenario which forces database to wait is called as deadlock. Database deadlock in a system may result in database rollback.

How to prevent database deadlock?

Database deadlock in a system can be prevented by aggressively inspecting all operations in the database. Deadlock in a system will be analyzed and inspected by DBMS. If DBMS finds situation in which a deadlock might occur; all transactions and operations in the database will be stopped. The time stop ordering mechanism is also used to prevent and predetermine the deadlock situations.

• MySQL Transaction

The time stop ordering mechanism can be classified into,

• Wait-Die Scheme
• Wound-Wait Scheme

Wait - Die Scheme

In Wait - Die scheme, the transaction requested by a data item is held by conflicting lock of another transaction. One of the two following possibilities will occur

• If TS(Ti) < TS(Tj) - Tj is younger than Ti; Ti will request the conflict lock from Tj. Ti will wait till the availability of a data-item.
• If TS(Ti) > TS(tj) - tj is older than ti which forces the transaction of Ti to die. However Ti transaction is restarted later with random delay.

Younger transactions are killed in this scheme while older transactions are allowed to wait.

Wound - Wait Scheme

The data item used in this scheme will be forced under a lock while another transaction is being executed by the database. At least one of the following two possibilities will occur during Wound - Wait scheme.

• If TS (Ti) < TS (Tj) - Tj will be forced to rollback by the Ti. The rollback of Ti will wound the transaction Tj. The transaction of Tj will be restarted later with a random delay.
• If TS(Ti) > TS (Tj) - Ti will wait until the resources are available.

Younger transactions are put on hold in wait - Die scheme. If older transaction requires a data item present in younger transaction; all transactions are put on hold until the release of the data item.

The transactions which enter the database system after the deadlock will be aborted by the wait -die and wound - wait schemes.

• How to Tune SQL Queries

Deadlock Avoidance

Aborting every transaction to prevent a deadlock is not feasible by practice. So deadlock mechanisms are designed to detect the deadlock situation in advance. The use of deadlock avoidance system is highly preferred in database system which handles bulk data.

Wait - for Graph

Wait - for Graph is the simple and most commonly used method to detect deadlock in a database system. In wait - for graph method, a node will be created for each transaction in a database. This system will maintain a wait - for graph for every transaction waiting for the data items.

• How to Prevent SQL Injection?

Any one of the following two approaches will be used by wait - for graph,

• Any transaction request for a locked data item will not be allowed. The transaction request will have to wait for an indefinite period of time until the lock on the data item is released.
• The younger transaction request will be rolled back while older transaction request is given more preference. This process can be done using a relative algorithm; the transaction which was aborted is called as “victim”. The process of selecting the abort transaction is called as “victim selection”.