Row and Gap Locks

Row and Gap Locks

Row Locks, Gap Locks, and Locking Reads

InnoDB locks index entries, so the index a statement scans decides what it locks. A record lock covers one entry, a gap lock the space between two entries, and a next-key lock (the REPEATABLE READ default) an entry plus the gap before it. An insert first requests an insert intention lock on its gap, which waits for other sessions' gap locks. Table-level IX and IS locks merely announce row locks. Plain SELECTs lock nothing, but SELECT ... FOR SHARE takes shared locks and SELECT ... FOR UPDATE exclusive ones. NOWAIT fails at once on a locked row, and SKIP LOCKED leaves locked rows out, which lets several workers share one job queue. This view lists the current database's locks:

A compact view of performance_schema.data_locksSQL

CREATE VIEW locks AS
SELECT engine_transaction_id AS trx, object_name AS tbl, index_name AS idx,
       lock_mode AS mode, lock_status AS status, lock_data AS data
FROM performance_schema.data_locks
WHERE object_schema = DATABASE();

Session A updates both Databases titles through the category_id index. Session B, with a 3-second lock wait timeout, skips A's rows, is refused by NOWAIT, and tries to add a Databases title while Session C looks at the locks:

Next-key locks, SKIP LOCKED, NOWAIT, and a lock wait timeoutSQL

Session A> START TRANSACTION;
Session A> UPDATE products SET stock = stock + 1 WHERE category_id = 3;
Session A: Query OK, 2 rows affected
Session B> SET SESSION innodb_lock_wait_timeout = 3;
Session B> SELECT id FROM products WHERE stock > 0 ORDER BY id LIMIT 2 FOR UPDATE SKIP LOCKED;
Session B: +----+
Session B: | id |
Session B: +----+
Session B: |  1 |
Session B: |  4 |
Session B: +----+
Session B> SELECT id FROM products WHERE id = 2 FOR UPDATE NOWAIT;
Session B: ERROR 3572 (HY000) at line 3: Statement aborted because lock(s) could not be acquired immediately and NOWAIT is set.
Session B> INSERT INTO products VALUES (9, 'BK-SQL-03', 'Query Tuning', 3, 38.00, 5, NULL);
Session B: (waiting)
Session C> SELECT * FROM locks ORDER BY trx, tbl, idx, data;
Session C: +-------+------------+-------------+------------------------+---------+------+
Session C: | trx   | tbl        | idx         | mode                   | status  | data |
Session C: +-------+------------+-------------+------------------------+---------+------+
Session C: | 92399 | products   | NULL        | IX                     | GRANTED | NULL |
Session C: | 92399 | products   | category_id | X                      | GRANTED | 3, 2 |
Session C: | 92399 | products   | category_id | X                      | GRANTED | 3, 3 |
Session C: | 92399 | products   | category_id | X,GAP                  | GRANTED | 4, 5 |
Session C: | 92399 | products   | PRIMARY     | X,REC_NOT_GAP          | GRANTED | 2    |
Session C: | 92399 | products   | PRIMARY     | X,REC_NOT_GAP          | GRANTED | 3    |
Session C: | 92402 | categories | NULL        | IS                     | GRANTED | NULL |
Session C: | 92402 | categories | PRIMARY     | S,REC_NOT_GAP          | GRANTED | 3    |
Session C: | 92402 | products   | NULL        | IX                     | GRANTED | NULL |
Session C: | 92402 | products   | category_id | X,GAP,INSERT_INTENTION | WAITING | 4, 5 |
Session C: +-------+------------+-------------+------------------------+---------+------+
Session B: ERROR 1205 (HY000) at line 4: Lock wait timeout exceeded; try restarting transaction
Session A> ROLLBACK;

A holds next-key locks (X) on entries (3, 2) and (3, 3), record locks on primary keys 2 and 3, and a gap lock before (4, 5), so no new category-3 row can appear: this is how locking statements prevent phantoms. B's entry (3, 9) falls in that gap and waited until error 1205; its shared lock on categories is the foreign-key check (Foreign Keys). Gap locks overreach: in another run a category-2 insert, entry (2, 9), timed out too. The timeout (innodb_lock_wait_timeout, 50 seconds by default) rolls back only the waiting statement. Without a usable index, a locking statement locks every row it scans (Indexes).

MENU