MySQL 锁机制和事务

文章由LinuxBoy分享于2019-05-31 05:05:51热评（287）

MySQL 锁机制和事务

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 内部标示每个事务的ID TRX_WEIGHT 表示该事务的权重，近似等于事务锁的行记录数。当发生死锁时， InnoDB会根据此值选择最小的值作为牺牲品. TRX_STATE 事务当前状态，包括RUNNING, LOCK WAIT, ROLLING BACK, and COMMITTING. TRX_STARTED 事务开始时间 TRX_REQUESTED_LOCK_ID 当事务状态为lock_wait时，表示需要等待的事务的锁ID，对应innodb_locks 表里的lock_id；如果是其他值则为NULL TRX_WAIT_STARTED 当事务状态为LOCK WAIT则代表事务等待锁的开始时间；如果是其他值则为NULL. TRX_MYSQL_THREAD_ID MySQL线程ID，对应show processlist里的值 TRX_QUERY 事务当前执行的语句 TRX_OPERATION_STATE 事务当前执行的语句类型，不执行则为NULL TRX_TABLES_IN_USE 执行当前语句需要涉及到几个InnoDB表 TRX_TABLES_LOCKED 当前语句执行施加的行锁对应了几个表 TRX_LOCK_STRUCTS 当前事务保留的锁个数 TRX_LOCK_MEMORY_BYTES 当前事务的锁信息所占用的内存byte数 TRX_ROWS_LOCKED 近似等于当前事务施加的行锁数量，也会包含删除语句所涉及的行锁 TRX_ROWS_MODIFIED 当前事务插入或者修改的行数 TRX_CONCURRENCY_TICKETS TRX_ISOLATION_LEVEL 当前事务的隔离级别 TRX_UNIQUE_CHECKS 唯一键约束检查是开启状态还是关闭状态，常用于批量导入数据时关闭检查 TRX_FOREIGN_KEY_CHECKS 外键约束检查是开启还是关闭状态，常用于批量导入数据时关闭检查 TRX_LAST_FOREIGN_KEY_ERROR 上一次外键约束错误的详细信息 TRX_ADAPTIVE_HASH_LATCHED TRX_ADAPTIVE_HASH_TIMEOUT TRX_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内部标示每个锁的ID LOCK_TRX_ID 表示持有该锁的事务ID，对应innodb_trx表中的事务ID LOCK_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；否则为NULL LOCK_PAGE 当为行锁时，则表示被锁记录所在的数据页数量；否则为NULL LOCK_REC 事务锁定行的数量，若是表锁则该值为NULL LOCK_DATA 事务锁定记录主键值，若是表锁则该值为NULL

Information_schema.innodb_lock_waits记录了InnoDB中事务之间相互等待锁的信息

1 2 3 4 REQUESTING_TRX_ID 请求锁被阻止的事务ID REQUESTED_LOCK_ID 请求锁被阻止的锁ID BLOCKING_TRX_ID 阻止上述事务获得锁的事务ID BLOCKING_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');`

开启两个会话链接，链接同一个数据库，模拟排他锁

+ View Code?

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: 0 session2: ##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

看看事务的详细信息：

+ View Code?

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 from information_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: 4 1 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: 0 session2: 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	`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则代表不使用间隔锁，只是使用行级锁

+ View Code?

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操作，如果是，则需要等待对应的锁解开

例：

+ View Code?

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语句开启事务

+ View Code?

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 B SET autocommit=0; SET autocommit=0; SELECT * FROM t; empty set INSERT INTO t VALUES (1, 2); SELECT * FROM t; empty set COMMIT; SELECT * FROM t; empty set COMMIT; SELECT * FROM t; --------------------- | 1 | 2 | ---------------------

首次读时间点之前的本事务未提交的修改数据可以读取到在读取提交数据隔离级别下，一致读的每个读取操作都会有自己的镜像
例一直读每个读取操作前自己的镜像

+ View Code?

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: 0 session2:开启事务 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: 0 mysql> 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语句
如果没有其他更好的选择，则可以通过施加表级锁将事务执行串行化，最大限度的限制死锁发生

linuxboy的RSS地址：https://www.linuxboy.net/rssFeed.aspx

本文永久更新链接地址：https://www.linuxboy.net/Linux/2019-01/156362.htm

推荐文章：

MySQL 锁机制和事务