A deadlock in mysql happens when two or more transactions are mutually holding and requesting locks on the same resources, creating a cycle of dependencies. Deadlocks occur when transactions try to lock resources in a different order. For example, consider these two transactions running against the orders table:
Transaction #1
START TRANSACTION;
UPDATE orders SET price = 50 WHERE id = 2;
UPDATE orders SET price = 60 WHERE id = 6;
COMMIT;
Transaction #2
START TRANSACTION;
UPDATE orders SET price = 60 WHERE id = 6;
UPDATE orders SET price = 50 WHERE id = 2;
COMMIT;
If you are unlucky, each transaction will execute its first query and update a row of data, locking it in the process. Each transaction will attempt to update its second row, only to find that it is already locked. The two transactions will wait forever for each other to complete, unless something intervenes to break the deadlock.
to solve this problem, database systems implement various forms of deadlock detection and timeouts. the InnoDB storage engine will notice circular dependencies and return an error instantly. This can be a good thing otherwise, deadlocks would manifest themselves as very slow queries. others will give up after the query exceeds a lock wait timeout, which is not always good. The way InnoDB currently handles deadlocks is to rollback the transaction that has the fewest exclusive row locks.
Lock behavior and order are storage engine specific, so some storage engines might deadlock on a certain sequence of statements even though others won’t.
Deadlocks have a dual nature:
- some are unavoidable because of true data conflicts.
- some are caused by how a storage engine works.
Deadlocks cannot be broken without rollingback one of the transactions, either partially or wholly. They are a fact of life in transactional systems, and your applications should be designed to handle them. Many applications can simply retry their transactions from the beginning.
Top comments (0)