Databases Reference
In-Depth Information
Table 1-1. ANSI SQL isolation levels
Nonrepeatable reads
possible
Phantom reads
possible
Isolation level
Dirty reads possible
Locking reads
READ UNCOMMITTED
Yes
Yes
Yes
No
READ COMMITTED
No
Yes
Yes
No
REPEATABLE READ
No
No
Yes
No
SERIALIZABLE
No
No
No
Yes
Deadlocks
A
deadlock
is when two or more transactions are mutually holding and requesting locks
on the same resources, creating a cycle of dependencies. Deadlocks occur when trans-
actions try to lock resources in a different order. They can happen whenever multiple
transactions lock the same resources. For example, consider these two transactions
running against the
StockPrice
table:
Transaction #1
START TRANSACTION;
UPDATE StockPrice SET close = 45.50 WHERE stock_id = 4 and date = '2002-05-01';
UPDATE StockPrice SET close = 19.80 WHERE stock_id = 3 and date = '2002-05-02';
COMMIT;
Transaction #2
START TRANSACTION;
UPDATE StockPrice SET high = 20.12 WHERE stock_id = 3 and date = '2002-05-02';
UPDATE StockPrice SET high = 47.20 WHERE stock_id = 4 and date = '2002-05-01';
COMMIT;
If you're unlucky, each transaction will execute its first query and update a row of data,
locking it in the process. Each transaction will then 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 combat this problem, database systems implement various forms of deadlock de-
tection and timeouts. The more sophisticated systems, such as 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 roll back the transaction that
has the fewest exclusive row locks (an approximate metric for which will be the easiest
to roll back).
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, and some are
caused by how a storage engine works.
