InnoDB Data Locking – Part 2.5 “Locks” (Deeper dive)

InnoDB Data Locking – Part 2.5 “Locks” (Deeper dive)

All together now

Let’s now put together all that we’ve learned in InnoDB Data Locking – Part 2 “Locks” about table and record locks to understand following situation:

mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM t FOR SHARE;
+----+
| id |
+----+
|  5 |
| 10 |
| 42 |
+----+
3 rows in set (0.00 sec)

mysql> DELETE FROM t WHERE id=10;
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t VALUES (4);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT INDEX_NAME,LOCK_TYPE,LOCK_DATA,LOCK_MODE
       FROM performance_schema.data_locks

…

Source: InnoDB Data Locking – Part 2.5 “Locks” (Deeper dive)