深入浅出InnoDB Locking
https://dev.mysql.com/doc/refman/8.0/en/innodb-locking.html
本节讨论的所有锁都是在 InnoDB 引擎下
MySQL 实现行锁定主要使用共享锁和排他锁。也就是常说的读写锁。
-
A shared (S) lock permits the transaction that holds the lock to read a row.
-
An exclusive (X) lock permits the transaction that holds the lock to update or delete a row.
S = shard 读锁，X = exclusive 写锁。
If transaction T1 holds a shared (S) lock on row r, then requests from some distinct transaction T2 for a lock on row r are handled as follows:
如果事务T1在r行持有读锁，则不同事务T2请求r行会被如下处理。
-
A request by T2 for an S lock can be granted immediately. As a result, both T1 and T2 hold an S lock on r.
-
A request by T2 for an X lock cannot be granted immediately.
If a transaction T1 holds an exclusive (X) lock on row r（如果事务T1在r行持有写锁）, a request from some distinct transaction T2 for a lock of either type on r cannot be granted immediately. Instead, transaction T2 has to wait for transaction T1 to release its lock on row r.
总结：在某一行，一个事务持有读锁后另一个事务的读锁可以立即获得而写锁则必须等待。一个事务持有写锁后另一个事务必须等到释放后才能进行读写操作。
Intention Locks 表锁
InnoDB supports multiple granularity locking which permits coexistence of row locks and table locks.
InnoDB 支持多粒度锁定，允许行锁和表锁共存。
For example, a statement such as LOCK TABLES … WRITE takes an exclusive lock (an X lock) on the specified table. To make locking at multiple granularity levels practical, InnoDB uses intention locks. Intention locks are table-level locks that indicate which type of lock (shared or exclusive) a transaction requires later for a row in a table. There are two types of intention locks:
-
An intention shared lock (IS) indicates that a transaction intends to set a shared lock on individual rows in a table.
-
An intention exclusive lock (IX) indicates that a transaction intends to set an exclusive lock on individual rows in a table.
For example, SELECT … FOR SHARE sets an IS lock, and SELECT … FOR UPDATE sets an IX lock.
select * from storage for share
select * from storage for update
The intention locking protocol is as follows:
-
Before a transaction can acquire a shared lock on a row in a table, it must first acquire an IS lock or stronger on the table.
-
Before a transaction can acquire an exclusive lock on a row in a table, it must first acquire an IX lock on the table.
Conflict - 冲突 Compatible - 兼容
A lock is granted to a requesting transaction if it is compatible with（共享） existing locks, but not if it conflicts with（冲突） existing locks. A transaction waits until the conflicting existing lock is released. If a lock request conflicts with an existing lock and cannot be granted because it would cause deadlock, an error occurs.
Intention locks do not block anything except full table requests (for example, LOCK TABLES … WRITE). The main purpose of intention locks is to show that someone is locking a row, or going to lock a row in the table.
Transaction data for an intention lock appears similar to the following in SHOW ENGINE INNODB STATUS and InnoDB monitor output:
SHOW ENGINE INNODB STATUS
TABLE LOCK table `test`.`t` trx id 10080 lock mode IX