Many SQL developers often ask how to prevent deadlocks.
What is a deadlock in a database
A deadlock occurs when two or more transactions wait on each other in a circular way, creating a transaction dependency that none of them can resolve.
Transaction A might hold a lock on some rows in the Accounts table and need to update some rows in the Orders table to finish. At the same time, Transaction B holds locks on those same rows in the Orders table and needs to update the rows in the Accounts table that Transaction A holds. Because of the lock on Orders, Transaction A cannot complete its changes. Due to the lock on Accounts, Transaction B also cannot finish. All activity stops and stays at a standstill until the DBMS detects the deadlock and aborts one of the transactions. The above figure shows this situation.
So, I am giving here the four best methods to avoid it.
- First, do not leave transactions open longer than necessary. Commit the transaction as often as possible. Use COMMIT and ROLLBACK commands appropriately.
- Second, avoid using a higher isolation level than you actually need.
- For longer-running transactions, schedule them separately so they do not conflict with regular workloads.
- When writing SQL queries, design them carefully so they do not create deadlocks.
