Differences

This shows you the differences between two versions of the page.

--- skills:interview [2026/06/15 10:05] – [Transactions] phong2018
+++ skills:interview [2026/06/15 10:07] (current) – [Transactions] phong2018
@@ Line 260: / Line 260: @@
 . What are deadlocks?
+<code>
-Deadlock: A situation where two or more transactions hold locks and wait for each other indefinitely.
+- Deadlock: A situation where two or more transactions hold locks and wait for each other indefinitely.
+- How it occurs: Transactions access the same resources in different orders. Long-running transactions hold locks too long. Missing indexes cause unnecessary locking.
-How it occurs:
+- How to detect it: The database detects deadlocks automatically. MySQL returns: ERROR 1213: Deadlock found when trying to get lock. Investigate with SHOW ENGINE INNODB STATUS.
+- How to prevent it: Keep transactions short. Always access tables/rows in a consistent order. Add proper indexes. Avoid large batch operations in a single transaction.
-Transactions access the same resources in different orders.
+- How to recover: The database rolls back one transaction automatically. Catch the error in the application and retry the transaction with backoff.
-Long-running transactions hold locks too long.
+</code>
-Missing indexes cause unnecessary locking.
-How to detect it:
-The database detects deadlocks automatically.
-MySQL returns: ERROR 1213: Deadlock found when trying to get lock.
-Investigate with SHOW ENGINE INNODB STATUS.
-How to prevent it:
-Keep transactions short.
-Always access tables/rows in a consistent order.
-Add proper indexes.
-Avoid large batch operations in a single transaction.
-How to recover:
-The database rolls back one transaction automatically.
-Catch the error in the application and retry the transaction with backoff.
 ==== Large Scale Databases ====