sql deadlock

Dead lock could happen when two sql transactions are waiting for a lock the other transaction hold in order to finish. Since neither transaction can finish, no lock is released. 

For example, transaction 1 need to lock table user, then table department

BEGIN TRANSACTION

update user set salary = 2000 where id = 10;

update department set user_id = 10 where dep_id = 555;

COMMIT TRANSACTION

at the same time transaction 2 need to lock table department, then user

BEGIN TRANSACTION

update department set user_id = 10 where dep_id = 555;

update user set salary = 2000 where id = 10;

COMMIT TRANSACTION

Imagine transaction 1 executed the first update, locked user table, transaction 2 executed the first update, locked department table. 

now when transaction 1 execute the second update, it need to lock table department, which transaction 2 currently holding. Transaction 2 release the lock on department table until it finish. But Transaction 2 can not make progress, because it need to hold lock of user table, which transaction 1 is holding.

This example is written in SQL Server syntax, but similar condition happen in other database as well. 

InnoDB uses automatic row-level locking. You can get deadlocks even in the case of transactions that just insert or delete a single row. That is because these operations are not really “atomic”; they automatically set locks on the (possibly several) index records of the row inserted or deleted. Table-level locks prevent concurrent updates to the table, avoiding deadlocks at the expense of less responsiveness for a busy system.

No database can possibly work around deadlock errors in general. Databases need to detect such kind of deadlock then terminate one of the transaction in order to break the deadlock. 

Mysql for example, can be configured to detect deadlock every 5 seconds.

Normally, you must write your applications so that they are always prepared to re-issue a transaction if it gets rolled back because of a deadlock. Keep transactions small and short in duration to make them less prone to collision. Commit transactions immediately after making a set of related changes to make them less prone to collision.