共计 16379 个字符，预计需要花费 41 分钟才能阅读完成。
InnoDB 锁机制
InnoDB 存储引擎支持 行级锁
其大类可以细分为 共享锁和排它锁 两类
共享锁 (S)：允许拥有共享锁的事务读取该行数据。 当一个事务拥有一行的共享锁时，另外的事务可以在同一行数据也获得共享锁，但另外的事务无法获得同一行数据上的排他锁
排它锁(X)：允许拥有排它锁的事务修改或删除该行数据。当一个事务拥有一行的排他锁时，另外的事务在此行数据上无法获得共享锁和排它锁，只能等待第一个事务的锁释放
除了共享锁和排他锁之外， InnoDB 也支持意图锁。该锁类型是属于表级锁 ，表明事务在后期会对 该表的行施加共享锁或者排它锁。所以对意图锁也有两种类型：
共享意图锁 (IS)：事务将会对表的行施加共享锁
排他意图锁(IX)：事务将会对表的行施加排它锁
举例来说 select … for share mode 语句就是施加了共享意图锁，而 select … for update 语句就是施加了排他意图锁
InnoDB 锁相关系统表
Information_schema.innodb_trx 记录了 InnoDB 中每一个正在执行的事务，包括该事务获得的锁信息，事务开始时间，事务是否在等待锁等信息
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 | TRX_ID InnODB 内部标示每个事务的 IDTRX_WEIGHT 表示该事务的权重，近似等于事务锁的行记录数。当发生死锁时，InnoDB 会根据此值选择最小的值作为牺牲品.TRX_STATE 事务当前状态，包括 RUNNING, LOCK WAIT, ROLLING BACK, and COMMITTING.TRX_STARTED 事务开始时间TRX_REQUESTED_LOCK_ID 当事务状态为 lock_wait 时，表示需要等待的事务的锁 ID，对应 innodb_locks 表里的 lock_id；如果是其他值则为 NULLTRX_WAIT_STARTED 当事务状态为 LOCK WAIT 则代表事务等待锁的开始时间；如果是其他值则为 NULL.TRX_MySQL_THREAD_ID MySQL 线程 ID，对应 show processlist 里的值TRX_QUERY 事务当前执行的语句TRX_OPERATION_STATE 事务当前执行的语句类型，不执行则为 NULLTRX_TABLES_IN_USE 执行当前语句需要涉及到几个 InnoDB 表TRX_TABLES_LOCKED 当前语句执行施加的行锁对应了几个表TRX_LOCK_STRUCTS 当前事务保留的锁个数TRX_LOCK_MEMORY_BYTES 当前事务的锁信息所占用的内存 byte 数TRX_ROWS_LOCKED 近似等于当前事务施加的行锁数量，也会包含删除语句所涉及的行锁TRX_ROWS_MODIFIED 当前事务插入或者修改的行数TRX_CONCURRENCY_TICKETSTRX_ISOLATION_LEVEL 当前事务的隔离级别TRX_UNIQUE_CHECKS 唯一键约束检查是开启状态还是关闭状态，常用于批量导入数据时关闭检查TRX_FOREIGN_KEY_CHECKS 外键约束检查是开启还是关闭状态，常用于批量导入数据时关闭检查TRX_LAST_FOREIGN_KEY_ERROR 上一次外键约束错误的详细信息TRX_ADAPTIVE_HASH_LATCHEDTRX_ADAPTIVE_HASH_TIMEOUTTRX_IS_READ_ONLY 当值为 1 时表明事务为只读事务TRX_AUTOCOMMIT_NON_LOCKING 当值为 1 代表事务中只有一个普通 select 语句，不会施加任何锁，且由于 autocommit 是开启的，所以事务只会包含该 select 语句 |
Information_schema.innodb_locks 记录了 InnoDB 中事务在申请但目前还没有获取到的每个锁信息，以及当前事务的锁正在阻止其他事务获得锁
1 2 3 4 5 6 7 8 9 10 | LOCK_ID InnoDB 内部标示每个锁的 IDLOCK_TRX_ID 表示持有该锁的事务 ID，对应 innodb_trx 表中的事务 IDLOCK_MODE 表示该锁的模式。可以是 S[,GAP], X[,GAP], IS[,GAP], IX[,GAP], AUTO_INC, UNKNOWN. 除了 AUTO_INC and UNKNOWN 的锁模式，其他的锁模式都暗含是 GAP 间隔锁LOCK_TYPE 表示锁类型，可以是 RECORD 表示行锁, TABLE 表示表锁LOCK_TABLE 该锁涉及的表名LOCK_INDEX 当为行锁时，则代表该锁涉及的索引名，否则为 NULL.LOCK_SPACE 当为行锁时，则表示被锁记录所在的表空间 ID；否则为 NULLLOCK_PAGE 当为行锁时，则表示被锁记录所在的数据页数量；否则为 NULLLOCK_REC 事务锁定行的数量，若是表锁则该值为 NULLLOCK_DATA 事务锁定记录主键值，若是表锁则该值为 NULL |
Information_schema.innodb_lock_waits 记录了 InnoDB 中事务之间相互等待锁的信息
1 2 3 4 | REQUESTING_TRX_ID 请求锁被阻止的事务 IDREQUESTED_LOCK_ID 请求锁被阻止的锁 IDBLOCKING_TRX_ID 阻止上述事务获得锁的事务 IDBLOCKING_LOCK_ID 阻止事务对应的锁 ID |
InnoDB 锁机制
行级锁
行级锁是施加在索引行数据上的锁，比如 SELECT c1 FROM t WHERE c1 = 10 FOR UPDATE 语句是在 t.c1=10 的索引行上增加锁，来阻止其他事务对对应索引行的 insert/update/delete 操作。
当一个 InnoDB 表没有任何索引时，则行级锁会施加在隐含创建的聚簇索引上，所以说当一条 sql 没有走任何索引时，那么将会在每一条聚集索引后面加 X(排它)锁，这个类似于表锁，但原理上和表锁应该是完全不同的
1 2 | mysql> create table temp(id int,name varchar(10));mysql> insert into temp values(1,'a'),(2,'b'),(3,'c'); |
开启两个会话链接，链接同一个数据库，模拟排他锁
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 | session1：##update 加排他锁mysql> set autocommit=0; #模拟开启一个事务会话Query OK, 0 rows affected (0.00 sec)mysql> update temp set name='aa' where >Query OK, 1 row affected (0.00 sec)Rows matched: 1 Changed: 1 Warnings: 0session2:##update 等待第一个锁释放 mysql> update temp set name='bb'where >session1##commit 释放锁mysql> commit;Query OK, 0 rows affected (0.00 sec)session2等待结束释放锁update 操作才会成功Query OK, 1 row affected (5.81 sec)Rows matched: 1 Changed: 1 Warnings: 0 |
看看事务的详细信息：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 | session1:set autocommit=0;update temp set name='a' where >session2:select trx_id,trx_state,trx_started,trx_tables_locked,trx_rows_locked frominformation_schema.innodb_trx\G;*************************** 1. row *************************** trx_id: 35455759 #事务 id trx_state: RUNNING #事务状态 trx_started: 2018-10-08 18:15:26 #事务开启时间trx_tables_locked: 1 trx_rows_locked: 41 row in set (0.00 sec)update temp set name='b' where >session3:# 查看锁等待select * from information_schema.innodb_lock_waits;# 查看锁状态select trx_id,trx_state,trx_started,trx_tables_locked,trx_rows_locked from information_schema.innodb_trx;# 查看锁的信息select lock_id,lock_trx_id,lock_mode,lock_type,lock_index,lock_rec,lock_data from information_schema.innodb_locks; |
看看该事务的锁信息
lock_type : RECORD 行级锁
lock_index:GEN_CLUSE_INDEX 聚集索引
给 temp 表加一个主键索引在看看
alter table temp add primary key(id); ## 增加索引之后, 记得把前面事务提交
1 2 3 4 5 6 7 8 9 10 11 12 | session1：update 加排它锁set autocommit=0;update temp set name='a' where >session2:update temp set name='b' where >update 不需要等待第一个锁释放，直接执行成功session1: 进行提交事务commit； |
间隔锁
当 我们用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB 会给符合条件 的已有数据记录的索引项加锁；对于键值在条件范围内但并不存在的记录，叫做“间隙（GAP)”，InnoDB 也会对这个“间隙”加锁
间隔锁是施加在索引记录之间的间隔上的锁，锁定一个范围的记录、但不包括记录本身, 比如 SELECT c1 FROM t WHERE c1 BETWEEN 10 and 20 FOR UPDATE 语句，尽管有可能对 c1 字段来说当前表里没有 =15 的值，但还是会阻止 =15 的数据的插入操作，是因为间隔锁已经把索引查询范围内的间隔数据也都锁住了
间隔锁的使用只在部分事务隔离级别才是生效的
间隔锁只会阻止其他事务的插入操作
gap lock 的前置条件：
1 事务隔离级别为 REPEATABLE-READ，innodb_locks_unsafe_for_binlog 参数为 0，且 sql 走的索引为非唯一索引（无论是等值检索还是范围检索）
2 事务隔离级别为 REPEATABLE-READ，innodb_locks_unsafe_for_binlog 参数为 0，且 sql 是一个范围的当前读操作，这时即使不是非唯一索引也会加 gap lock
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | update 加排他锁和间隔锁set autocommit=0;mysql> update temp2 set name='abc' where id between 4 and 6;Query OK, 0 rows affected (0.01 sec)Rows matched: 0 Changed: 0 Warnings: 0session2:insert into temp2 values(4,'d');会等待第一个锁释放session1：释放锁commit;session2:Query OK, 1 row affected (7.65 sec)等待结束释放锁 插入数据成功 |
例子如果链接 1 的 update 语句是 update temp set name=‘abc’where id >4; 而链接 2 的
插入数据的 >
Next-key 锁
在默认情况下，mysql 的事务隔离级别是可重复读，并且 innodb_locks_unsafe_for_binlog
参数为 0，这时默认采用 next-key locks。所谓 Next-Key Locks，就是 记录锁和间隔锁的结合，即除了锁住记录本身，还要再锁住索引之间的间隙。
插入意图锁
插入意图锁是在插入数据时首先获得的一种间隔锁，对这种间隔锁只要不同的事务插 入的数据位置是不一样的，虽然都是同一个间隔，也不会产生互斥关系
比如有一个索引有 4 和 7 两个值，如果两个事务分别插入 5 和 6 两个值时，虽然两个事务
都会在索引 4 和 7 之间施加间隔锁，但由于后续插入的数值不一样，所以两者不会互斥
比如下例中事务 A 对索引 >100 的值施加了排他间隔锁，而事务 B 在插入数据之前就试
图先施加插入意图锁而必须等待
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | CREATE TABLE child (id int(11) NOT NULL, PRIMARY KEY(id)) ENGINE=InnoDB;INSERT INTO child (id) values (90),(102);# 开启事务 A START TRANSACTION;# 增加排它间隔锁SELECT * FROM child WHERE id > 100 FOR UPDATE;+-----+| id |+-----+| 102 |+-----+1 row in set (0.00 sec)另一个 session 窗口，开启事务 B：START TRANSACTION;INSERT INTO child (id) VALUES (101);这时候会有锁等待 |
可以通过 show engine innodb status 命令查看插入意向锁被阻止
自增锁
自增锁是针对事务插入表中自增列时施加的一种特殊的表级锁，即
当一个事务在插入自增数据时，另一个事务必须等待前一个事务完
成插入，以便获得顺序的自增值
参数 innodb_autoinc_lock_mode 可以控制自增锁的使用方法
InnoDB 锁相关系统变量
查看当前系统隔离级别
1 2 3 4 5 6 | show variables like 'tx_isolation'; +---------------+-----------------+| Variable_name | Value |+---------------+-----------------+| tx_isolation | REPEATABLE-READ |+---------------+-----------------+ |
查看是否开启自动提交
1 2 | show variables like 'autocommit';show variables like 'innodb_table_locks'; |
查看 innodb 事务等待事务的超时时间（秒）
1 2 3 4 5 6 | mysql> show variables like 'innodb_lock_wait_timeout';+--------------------------+-------+| Variable_name | Value |+--------------------------+-------+| innodb_lock_wait_timeout | 50 |+--------------------------+-------+ |
innodb_locks_unsafe_for_binlog 参数用来控制 innodb 中是否允许间隔锁，默认是 OFF 代表允许间隔锁，设置成 ON 则代表不使用间隔锁，只是使用行级锁
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | • mysql> show variables like 'innodb_locks_unsafe_for_binlog';• +--------------------------------+-------+• | Variable_name | Value |• +--------------------------------+-------+• | innodb_locks_unsafe_for_binlog | OFF |• +--------------------------------+-------+• 在 my.cnf 中配置 innodb_locks_unsafe_for_binlog=on选项• 链接 1：• mysql> set autocommit=0;• mysql> update temp set name='abc' where id>=4;• 链接 2：• mysql> set autocommit=0;• Query OK, 0 rows affected (0.00 sec)• mysql> insert into temp values(4,‘abc’); ## 未出现等待的情况• Query OK, 1 row affected (0.00 sec) |
InnoDB 事务隔离级别
InnoDB 存储引擎提供了四种事务隔离级别，分别是：
- READ UNCOMMITTED：读取未提交内容
- READ COMMITTED：读取提交内容
- REPEATABLE READ：可重复读，默认值。
- SERIALIZABLE：串行化
可以通过–transaction-isolation 参数设置实例级别的事务隔离级别，也可以通过 set [session/global] transaction isolation
level 语句修改当前数据库链接或者是后续创建的所有数据库链接的事务隔离级别
1 2 3 4 5 6 7 | SET [GLOBAL | SESSION] TRANSACTION ISOLATION LEVEL{READ UNCOMMITTED| READ COMMITTED| REPEATABLE READ| SERIALIZABLE} |
REPEATABLE READ：可重复读，默认值。 表明对同一个事务来说第一次读数据时会创建快照，在事务结束前的其他读操作 (不加锁) 会获得和第一次读相同的结果。当读操作是加锁的读语句(select … for update 或者 lock in share mode), 或者 update 和 delete 语句时，加锁的方式依赖于语句是否使用唯一索引访问唯一值或者范围值
当访问的是唯一索引的唯一值时，则 InnoDB 会在索引行施加行锁 当访问唯一索引的范围值时，则会在扫描的索引行上增加间隔锁或者 next-key 锁以防止其他链接对此范围的插入
READ COMMITTED：读取提交内容。意味着每次读都会有自己最新的快照。对于加锁读语句 (select … for update 和 lock in share mode)，或者 update，delete 语句会在对应的行索引上增加锁，但 不像可重复读一样会增加间隔锁，因此其他的事务执行插入操作时如果是插入非索引行上的数值，则不影响插入
由于该隔离级别是禁用间隔锁的，所以会导致幻读的情况
如果是使用此隔离级别，就必须使用行级别的二进制日志
此隔离级别还有另外的特点：
对于 update 和 delete 语句只会在约束条件对应的行上增加锁
对 update 语句来说，如果对应的行上已经有锁，则 InnoDB 会执行半一致读的操作，来确定 update 语句对应的行在上次 commit 之后的数据是否在锁的范围，如果不是，则不影响 update 操作，如果是，则需要等待对应的锁解开
例：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 | session1：# 设置为读提交mysql> set session transaction isolation level read committed;# 关闭 mysql 默认的自动提交mysql> set autocommit=0;mysql> update temp set name='aaa' where >mysql> select * from temp;+----+------+| id | name |+----+------+| 1 | aaa || 2 | b || 3 | c || 4 | d |+----+------+session2:mysql> set session transaction isolation level read committed;mysql> set autocommit=0;mysql> select * from temp;+----+------+| id | name |+----+------+| 1 | a || 2 | b || 3 | c || 4 | d |session1:# 提交 session1 的所有事务commit;session2：mysql> select * from temp;+----+------+| id | name |+----+------+| 1 | aaa || 2 | b || 3 | c || 4 | d |+----+------+# 同一个事务中能看到另一个事务已经提交的数据mysql> commit; |
比如如下情况
1 2 3 | CREATE TABLE t (a INT NOT NULL, b INT) ENGINE = InnoDB;INSERT INTO t VALUES (1,2),(2,3),(3,2),(4,3),(5,2);COMMIT; |
表中并没有任何索引，所以会使用隐藏创建的聚簇索引来施加行级锁
当第一个链接执行修改:
1 2 3 4 5 6 | 当第一个 SESSION 链接执行修改:SET autocommit = 0;UPDATE t SET b = 5 WHERE b = 3;之后第二个 SESSION 链接执行修改：SET autocommit = 0;UPDATE t SET b = 4 WHERE b = 2; |
对可重复读隔离级别来说，第一个事务的修改会在每行记录上都增加排他锁，并且直到事务结束后锁才会释放
而第二个事务会一直等待前面事务的锁被释放后才能执行
READ UNCOMMITTED：读取未提交内容，所读到的数据可能是脏数据
SERIALIZABLE：串行化，此隔离级别更接近于可重复读这个级别，只是当 autocommit 功能被禁用后，InnoDB 引擎会将每个 select 语句隐含的转化为 select … lock in share mode
总结：
在数据库操作中，为了有效保证并发读取数据的正确性，提出的事务隔离级别。我们的数据库锁，也是为了构建这些隔离级别存在的。
| 隔离级别 | 脏读 | 不可重复读 | 幻读 |
| 可重复读 | 不可能 | 不可能 | 可能 |
| 提交读 | 不可能 | 可能 | 可能 |
| 未提交读 | 可能 | 可能 | 可能 |
| 串行化 | 不可能 | 不可能 | 不可能 |
未提交读(Read Uncommitted)：允许脏读，也就是可能读取到其他会话中未提交事务修改的数据
提交读(Read Committed)：只能读取到已经提交的数据。Oracle 等多数数据库默认都是该级别
可重复读(Repeated Read)：可重复读。在同一个事务内的查询都是事务开始时刻一致的，InnoDB 默认级别。在 SQL 标准中，该隔离级别消除了不可重复读，但是还存在幻象读
串行读(Serializable)：完全串行化的读，每次读都需要获得表级共享锁，读写相互都会阻塞
Autocommit/commit/rollback
当设置 autocommit 属性开启时，每个 SQL 语句都会隐含成为独立的事务。 默认情况下 autocommit 属性是开启的，也就意味着当每个 SQL 语句最后执行结果不返回错误时都会执行 commit 语句，当返回失败时会执行 rollback 语句
而当 autocommit 属性开启时，可以通过执行 start transaction 或者 begin 语句来显示的开启一个事务，而事务里可以包含多个 SQL 语句，最终事务的结束是由 commit 或者 rollback 来终结
而当在数据库链接里执行 set autocommit= 0 代表当前数据库链接禁止自动提交，事务的终结由 commit 或者 rollback 决定，同时也意味着下一个事务的开始
如果一个事务在 autocommit= 0 的情况下数据库链接退出而没有执行 commit 语句，则这个事务会回滚
一些特定的语句会隐含的终结事务，就好比是执行了 commit 语句
commit 语句代表将此事务的数据修改永久化，并对其他事务可见，而 rollback 则代表将此事务的数据修改回滚
commit 和 rollback 都会把当前事务执行所施加的锁释放
当使用多语句事务时，如果全局的 autocommit 属性是开启的，则开始此事务的方式可以使 set autocommit= 0 将当前链接的属性关闭，最后执行 commit 和 rollback；或者是显示的使用 start transaction 语句开启事务
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 | • mysql> -- Do a transaction with autocommit turned on.• mysql> create table customer (id int,name varchar(48));• mysql> START TRANSACTION;• mysql> INSERT INTO customer VALUES (10, 'Heikki');• Query OK, 1 row affected (0.00 sec)• mysql> COMMIT;• Query OK, 0 rows affected (0.00 sec)• mysql> -- Do another transaction with autocommit turned off.• mysql> SET autocommit=0;• mysql> INSERT INTO customer VALUES (15, 'John');• Query OK, 1 row affected (0.00 sec)• mysql> INSERT INTO customer VALUES (20, 'Paul');• Query OK, 1 row affected (0.00 sec)• mysql> DELETE FROM customer WHERE b = 'Heikki';• Query OK, 1 row affected (0.00 sec)• mysql> -- Now we undo those last 2 inserts and the delete.• mysql> ROLLBACK;• Query OK, 0 rows affected (0.00 sec)• mysql> SELECT * FROM customer;• +------+--------+• | a | b |• +------+--------+• | 10 | Heikki | |
一致读
在默认的隔离级别下 一致读是指 InnoDB 在多版本控制中在事务的首次读时产生一个镜像，在首次读时间点之前其他事务提交的修改可以读取到，而首次读时间点之后其他事务提交的修改或者是未提交的修改都读取不到
唯一例外的情况是在首次读时间点之前的本事务未提交的修改数据可以读取到
在读取提交数据隔离级别下，一致读的每个读取操作都会有自己的镜像
一致读操作不会施加任何的锁，所以就不会阻止其他事务的修改动作
在下面的例子中，链接 A 对链接 B 所做的修改，只有在它的事务和链接 B 的事务都提交的情况下才能看到
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | Session A Session BSET autocommit=0; SET autocommit=0;SELECT * FROM t;empty setINSERT INTO t VALUES (1, 2); SELECT * FROM t; empty set COMMIT;SELECT * FROM t;empty setCOMMIT;SELECT * FROM t;---------------------| 1 | 2 |--------------------- |
首次读时间点之前的本事务未提交的修改数据可以读取到在读取提交数据隔离级别下，一致读的每个读取操作都会有自己的镜像
例 一直读每个读取操作前自己的镜像
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 | session1：开启一个事务，进行修改数据操作mysql> set autocommit=0;Query OK, 0 rows affected (0.00 sec)mysql> update temp set name='aaa' ;Query OK, 4 rows affected (0.00 sec)Rows matched: 4 Changed: 4 Warnings: 0session2: 开启事务commitmysql> start transaction;Query OK, 0 rows affected (0.00 sec)session1: 进行提交修改数据为 aa 的事务commit;session2: 还是本事务没有提交的镜像数据mysql> select * from temp;+----+---------+| id | name |+----+---------+| 1 | 1111111 || 2 | 1111111 || 3 | 1111111 || 4 | 1111111 |+----+---------+session2: 在进行数据修改mysql> update temp set name='bbb';Query OK, 4 rows affected (0.00 sec)Rows matched: 4 Changed: 4 Warnings: 0mysql> commit;Query OK, 0 rows affected (0.01 sec)session1：再进行查看，mysql> select * from temp;+----+---------+| id | name |+----+---------+| 1 | 1111111 || 2 | 1111111 || 3 | 1111111 || 4 | 1111111 |+----+---------+然后提交 session2 的事务：mysql> select * from temp;+----+------+| id | name |+----+------+| 1 | bbb || 2 | bbb || 3 | bbb || 4 | bbb |+----+------+ |
一致读在某些 DDL 语句下不生效：
碰到 drop table 语句时，由于 InnoDB 不能使用被 drop 的表，所以无法实现一致读
碰到 alter table 语句时，也无法实现一致读
当碰到 insert into… select, update … select 和 create table … select 语句时，在默认的事务隔离级别下，语句的执行更类似于在读取提交数据的隔离级别下
加锁读操作
当在一个事务中在读操作结束后会执行 insert 和 update 操作时，普通的读操作无法阻止其他事务对相同数据执行修改操作，
所以 InnoDB 提供了两种在读操作时就增加锁的方式
select … lock in share mode：在读取的行数据上施加共享锁，其他的事务可以读相同的数据但无法修改；如果在执行此语句时有其他事务对相同的数据已经施加了锁，则需要等待事务完结释放锁
select … for update：和 update 操作一样，在涉及的行上施加排他锁，并阻止任何其他事务对涉及行上的修改操作、以及加锁读操作，但不会阻止对涉及行上的一般读（不加锁）操作
同样，锁的释放也是在事务提交或者回滚之后
比如在子表中插入一行数据，要确保对应的列在父表中有值，通过一般的读操作先查父表有值然后再插入的方法是不保险的，因为在读操作和插入操作之间就有可能其他事务会将父表的数据修改掉。那保险的做法是在查询父表是用加锁读的方式，比如：
SELECT * FROM parent WHERE NAME =‘Jones’LOCK IN SHARE MODE;
再比如当表中有一个行数计数字段时，使用一致读和 lock in share mode 都有可能导致重复错误数据出现，因为有可能两个事务会读到相同的值，在这种情况下就要使用 select … for update 语句保证一个事务在读时，另一个事务必须等待
SQL 语句对应的锁
加锁读，修改和删除 SQL 语句都会在索引扫描过的每一行增加锁，也就是说不光是在 where 条件限制的索引行上增加锁，也会对扫描到的间隔增加间隔锁
如果 SQL 语句是使用二级索引查找数据而且施加的是排他锁，则 InnoDB 也会在对应的聚簇索引行上施加锁
如果 SQL 语句没有任何索引可以使用，则 MySQL 需要扫描全表数据，而每
行数据都会被施加锁，所以一个良好的习惯是为 InnoDB 添加合适的索引
针对不同的语句，InnoDB 会施加不同的锁：
Select…from 语句属于一致性读，在默认情况下不施加任何的锁，除非在可串行化隔离级别下，会施加共享 next-key 锁在扫描的索引行上，当碰到使用唯一索引查找唯一值时只在唯一值上施加锁
Select…lock in share mode 语句会在索引扫描行上施加共享 next-key 锁，除非是当碰到使用唯一索引查找唯一值时只在唯一值上施加锁
Select…for update 语句会对扫描索引的行上施加排他 next-key 锁，除非是当碰到使用唯一索引查找唯一值时只在唯一值上施加锁
Update 语句会对扫描索引的行上施加排他 next-key 锁，除非是当碰到使用唯一索引查找唯一值时只在唯一值上施加锁。
Delete 语句会对扫描索引的行上施加排他 next-key 锁，除非是当碰到使用唯一索引查找唯一值时只在唯一值上施加锁
Insert 语句会对索引扫描的行上施加锁，但不是 next-key 锁，所以不会阻止其他事务对该行值前的间隔上插入数据
Insert into T select…from S 语句会对插入到 T 表的行施加排他锁（非间隔锁），而在默认隔离级别下会对访问的 S 表上的行施加共享 next-key 锁
当表上有外键约束时，对任何的 insert,update 和 delete 操作都会在需要检查外键约束的行上施加共享行锁
Lock table 语句是施加表级锁
幻读
幻读问题发生在同一个事务中当相同的读操作在前后两次读数据时返回不同的结果集。
比如在表的 ID 字段上有一个索引，当希望对 ID>100 的数据进行后续修改时，我们会使用如下的语句：SELECT * FROM child WHERE id > 100 FOR UPDATE，而如果表里目前只有 90 和 102 两个值时，如果没有间隔锁锁住 90 到 102 之间的间隔，则其他的事务会插入比如 101 这个值，这样的话在第二次读数据时就会返回三行记录而导致幻读
为了阻止幻读情况的发生，InnoDB 使用了一种方法 next-key 锁将索引行锁和间隔锁合并在一起。InnoDb 会在索引扫描的行上施加行级共享锁或者排他锁，而 next-key 锁也会在每个索引行之前的间隔上施加锁，会导致其他的 session 不能在每个索引之前的间隔内插入新的索引值
间隔锁会施加在索引读碰到的行数据上，所以对上例来说为了阻止插入任何 >100 的值，也会将最后扫描的索引值 102 之前的间隔锁住
InnoDB 锁性能监控
1 2 3 4 5 6 7 8 9 10 11 | mysql> show status like '%innodb_row_lock%';+-------------------------------+-------+| Variable_name | Value |+-------------------------------+-------+| Innodb_row_lock_current_waits | 0 || Innodb_row_lock_time | 0 || Innodb_row_lock_time_avg | 0 || Innodb_row_lock_time_max | 0 || Innodb_row_lock_waits | 0 |+-------------------------------+-------+5 rows in set (0.00 sec) |
Innodb_row_lock_current_waits：当前等待锁的数量
Innodb_row_lock_time：系统启动到现在、锁定的总时间长度
Innodb_row_lock_time_avg：每次平均锁定的时间
Innodb_row_lock_time_max：最长一次锁定时间
Innodb_row_lock_waits：系统启动到现在、总共锁定次数
InnoDB 死锁
死锁的情况发生在 不同的的事务相互之间拥有对方需要的锁，而导致相互一直无限等待
死锁可能发生在不同的事务都会对多个相同的表和相同的行上施加锁，但事务对表的操作顺序不相同
为了减少死锁的发生，要避免使用 lock table 语句，要尽量让修改数据的范围尽可能的小和快速；当不同的事务要修改多个表或者大量数据时，尽可能的保证修改的顺序在事务之间要一致
默认情况下InnoDB 下的死锁自动侦测功能是开启的，当 InnoDB 发现死锁时，会将其中的一个事务作为牺牲品回滚。
show variables like‘innodb_deadlock_detect’;
通过 innodb_lock_wait_timeout 参数配置自动侦测功能是否开启，如果关闭的话，InnoDB 就会使用 innodb_lock_wait_timeout 参数来自动回滚等待足够时间的事
mysql> show variables like ‘%innodb_lock_wait_timeout%’;
可以通过 show engine innodb status 语句查看最后一次发生死锁的情况
比如以下例子产生的死锁：
1 2 3 4 5 | 事务 1:update temp set name=‘aa’where >事务 2:update temp set name=‘bb’where >事务 1:update temp set name=‘aaa’where >事务 2: update temp set name=‘bbb’where >ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction |
InnoDB 死锁检测和回滚
默认情况下死锁检测功能是开启的，当死锁发生时 InnoDB 会自动检测
到并牺牲 (回滚) 其中的一个或者几个事务，以便让其他的事务继续执行
下去。
InnoDB 选择牺牲的事务往往是代价比较小的事务，其代价计算是根据
事务 insert,update, delete 的数据行规模决定
如果事务中的某个语句因为错误而回滚，则这个语句上的锁可能还会
保留，是因为 InnoDB 仅会存储行锁信息，而不会存储行锁是由事务中
的哪个语句产生的
如果在一个事务中，select 语句调用了函数，而函数中的某个语句执行
失败，则那个语句会回滚，如果在整个事务结束时执行 rollback，则整
个事务回滚
可以通过 innodb_deadlock_detect 参数关闭死锁检测功能，而仅仅用
innodb_lock_wait_timeout 的功能来释放锁等待
减少死锁发生的方法
在事务性数据库中，死锁是个经典的问题，但只要发生的频率不高则死锁问题不需要太过担心
查看死锁的方法有两种：
通过 show engine innodb status 命令可以查看最后一个死锁的情况
通过 innodb_print_all_deadlocks 参数配置可以将所有死锁的信息都打印到 MySQL 的错误日志中
减少死锁发生的方法：
- 尽可能的保持事务小型化，减少事务执行的时间可以减少发生影响的概率
- 及时执行 commit 或者 rollback，来尽快的释放锁
- 当要访问多个表数据或者要访问相同表的不同行集合时，尽可能的保证每次访问的顺序是相同的。比如可以将多个语句封装在存储过程中，通过调用同一个存储过程的方法可以减少死锁的发生
- 增加合适的索引以便语句执行所扫描的数据范围足够小
- 尽可能的少使用锁，比如如果可以承担幻读的情况，则直接使用 select 语句，而不要使用 select…for update 语句
- 如果没有其他更好的选择，则可以通过施加表级锁将事务执行串行化，最大限度的限制死锁发生
：
