九、MySQL锁机制和事务

作者: 码农工号9527 | 来源:发表于2020-12-25 21:29 被阅读0次

InnoDB锁机制

InnoDB存储引擎⽀持⾏级锁，其⼤类可以细分为共享锁和排它锁两类

共享锁(S)：允许拥有共享锁的事务读取该⾏数据。当⼀个事务拥有⼀⾏的共享锁时，另外的事务可以在同⼀⾏数据也获得共享锁，但另外的事务⽆法获得同⼀⾏数据上的排他锁

排它锁(X)：允许拥有排它锁的事务修改或删除该⾏数据。当⼀个事务拥有⼀⾏的排他锁时，另外的事务在此⾏数据上⽆法获得共享锁和排它锁，只能等待第⼀个事务的锁释放

除了共享锁和排他锁之外，InnoDB也⽀持意图锁。该锁类型是属于表级锁，表明事务在后期会对该表的⾏施加共享锁或者排它锁。所以对意图锁也有两种类型：

共享意图锁(IS)：事务将会对表的⾏施加共享锁

排他意图锁(IX)：事务将会对表的⾏施加排它锁

举例来说select … for share mode语句就是施加了共享意图锁，⽽select … for update语句就是施加了排他意图锁
这四种锁之间的相互共存和排斥关系如下：

所以决定⼀个事务请求为数据加锁时能否⽴即施加上锁，取决于该数据上已经存在的锁是否和请求的锁可以共存还是排斥关系，当相互之间是可以共存时则⽴即施加锁，当相互之间是排斥关系时则需要等待已经存在的锁被释放才能施加

InnoDB锁相关系统表

Information_schema.innodb_trx记录了InnoDB中每⼀个正在执⾏的事务，包括该事务获得的锁信息，事务开始时间，事务是否在等待锁等信息

performance_schema.data_locks记录了InnoDB中事务的每个锁信息，以及当前事务的锁正在阻⽌其他事务获得锁

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

InnoDB锁机制

⾏级锁

⾏级锁是施加在索引⾏数据上的锁，⽐如SELECT c1 FROM t WHERE c1 = 10 FOR UPDATE语句是在t.c1=10的索引⾏上增加锁，来阻⽌其他事务对对应索引⾏的insert/update/delete操作。当⼀个InnoDB表没有任何索引时，则⾏级锁会施加在隐含创建的聚簇索引上，所以说当⼀条sql没有⾛任何索引时，那么将会在每⼀条聚集索引后⾯加X锁，这个类似于表锁，但原理上和表锁应该是完全不同的

 mysql> create table temp(id int,name varchar(10));
 Query OK, 0 rows affected (0.01 sec)

 mysql> insert into temp values(1,'a'),(2,'b'),(3,'c');

 mysql> alter table temp add primary key(id); ##增加索引之后
 Query OK, 0 rows affected (0.01 sec)

间隔锁

当我们⽤范围条件⽽不是相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据记录的索引项加锁；对于键值在条件范围内但并不存在的记录，叫做“间隙（GAP)”，InnoDB也会对这个“间隙”加锁
间隔锁是施加在索引记录之间的间隔上的锁，锁定⼀个范围的记录、但不包括记录本身,⽐如SELECT c1 FROM t WHERE c1 BETWEEN 10 and 20 FOR UPDATE语句，尽管有可能对c1字段来说当前表⾥没有=15的值，但还是会阻⽌=15的数据的插⼊操作，是因为间隔锁已经把索引查询范围内的间隔数据也都锁住了
间隔锁的使⽤只在部分事务隔离级别才是⽣效的
间隔锁只会阻⽌其他事务的插⼊操作

gap lock的前置条件：
1 事务隔离级别为REPEATABLE-READ，且sql⾛的索引为⾮唯⼀索引（⽆论是等值检索还是范围检索）
2 事务隔离级别为REPEATABLE-READ，且sql是⼀个范围的当前读操作，这时即使是唯⼀索引也会加gap lock

innodb_locks_unsafe_for_binlog(强制不使⽤间隔锁)参数在8.0中已经取消

 mysql> CREATE TABLE `temp` ( 
  `id` int(11) NOT NULL
  ,`name` varchar(10) DEFAULT NULL
  ,PRIMARY KEY (`id`)
  )ENGINE=InnoDB DEFAULT CHARSET=latin1
 mysql> insert into temp values(1,'a'),(2,'b'),(3,'c');

之前的例⼦如果链接1的update语句是update temp set name=‘abc’ where id >4; ⽽链接2的插⼊数据的id=4时也会被阻⽌，是因为记录中的3~4之间也算是间隔
链接1：

 mysql> update temp set name='abc' where id>4;
 Query OK, 0 rows affected (0.00 sec)
 Rows matched: 0 Changed: 0 Warnings: 0

链接2：

 mysql> insert into temp values(4,‘abc’); ##等待

Next-key锁

在默认情况下，mysql的事务隔离级别是可重复读，默认采⽤next-key locks。所谓Next-Key Locks，就是记录锁和间隔锁的结合，即除了锁住记录本身，还要再锁住索引之间的间隙。

插⼊意图锁

插⼊意图锁是在插⼊数据时⾸先获得的⼀种间隔锁，对这种间隔锁，只要不同的事务插⼊的数据位置是不⼀样的，即使都是同⼀个间隔，也不会产⽣互斥关系，⽐如有⼀个索引有4和7两个值，如果两个事务分别插⼊5和6两个值时，虽然两个事务都会在索引4和7之间施加间隔锁，但由于后续插⼊的数值不⼀样，所以两者不会互斥。
⽐如下例中事务A对索引>100的值施加了排他间隔锁，⽽事务B在插⼊数据之前就试图先施加插⼊意图锁⽽必须等待
事务A：

mysql> CREATE TABLE child (id int(11) NOT NULL, PRIMARY KEY(id)) ENGINE=InnoDB;
Query OK, 0 rows affected (0.04 sec)

mysql>  INSERT INTO child (id) values (90),(102);
Query OK, 2 rows affected (0.10 sec)
Records: 2  Duplicates: 0  Warnings: 0

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

mysql> SELECT * FROM child WHERE id > 100 FOR UPDATE;
+-----+
| id  |
+-----+
| 102 |
+-----+
1 row in set (0.00 sec)

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

事务B：

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

mysql> INSERT INTO child (id) VALUES (101);#插入被阻止，直到事务A commit
Query OK, 1 row affected (4.94 sec)

可以通过show engine innodb status命令查看插⼊意向锁被阻⽌

⾃增锁

⾃增锁是针对事务插⼊表中⾃增列时施加的⼀种特殊的表级锁，即当⼀个事务在插⼊⾃增数据时，另⼀个事务必须等待前⼀个事务完成插⼊，以便获得顺序的⾃增值
参数innodb_autoinc_lock_mode可以控制⾃增锁的使⽤⽅法

InnoDB锁相关系统变量

查看当前系统隔离级别

mysql> show variables like 'transaction_isolation';
+-----------------------+-----------------+
| Variable_name         | Value           |
+-----------------------+-----------------+
| transaction_isolation | REPEATABLE-READ |
+-----------------------+-----------------+
1 row in set (0.03 sec)

查看是否开启⾃动提交

mysql> show variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | ON    |
+---------------+-------+
1 row in set (0.00 sec)

mysql>  show variables like 'innodb_table_locks';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| innodb_table_locks | ON    |
+--------------------+-------+
1 row in set (0.00 sec)

查看innodb事务等待事务的超时时间（秒）

mysql> show variables like 'innodb_lock_wait_timeout'; 
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| innodb_lock_wait_timeout | 50    |
+--------------------------+-------+
1 row in set (0.00 sec)

链接1：

 Mysql> set autocommit=0;
 mysql> update temp set name='abc' where id>4;

链接2：

 Mysql> set autocommit=0;
 mysql> insert into temp values(4,'abc');
 …… ## 等待50秒后超时，事务回滚
 ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

InnoDB事务隔离级别

InnoDB存储引擎提供了四种事务隔离级别，分别是：

READ UNCOMMITTED：读取未提交内容
READ COMMITTED：读取提交内容
REPEATABLE READ：可重复读，默认值。
SERIALIZABLE：串⾏化
可以通过 --transaction-isolation 参数设置实例级别的事务隔离级别，也可以通过set [session/global] transaction isolation level语句修改当前数据库链接或者是后续创建的所有数据库链接的事务隔离级别，每个事务隔离级别所对应的锁的使⽤⽅法都有所不同。

SET [GLOBAL | SESSION] TRANSACTION ISOLATION LEVEL
{
READ UNCOMMITTED
| READ COMMITTED
| REPEATABLE READ
| SERIALIZABLE
}

REPEATABLE READ：
可重复读，默认值。表明对同⼀个事务来说第⼀次读数据时会创建快照，在事务结束前的其他读操作(不加锁)会获得和第⼀次读相同的结果。当读操作是加锁的读语句(select … forupdate或者lock in share mode),或者update和delete语句时，加锁的⽅式依赖于语句是否使⽤唯⼀索引访问唯⼀值或者范围值
当访问的是唯⼀索引的唯⼀值时，则InnoDB会在索引⾏施加⾏锁
当访问唯⼀索引的范围值时，则会在扫描的索引⾏上增加间隔锁或者next-key锁以防⽌其他链接对此范围的插⼊
READ COMMITTED：
读取提交内容。意味着每次读都会有⾃⼰最新的快照。对于加锁读语句(select … for update和lock in share mode)，或者update，delete语句会在对应的⾏索引上增加锁，但不像可重复读⼀样会增加间隔锁，因此其他的事务执⾏插⼊操作时如果是插⼊⾮索引⾏上的数值，则不影响插⼊。
由于该隔离级别是禁⽤间隔锁的，所以会导致幻读的情况

幻读:事务A 按照一定条件进行数据读取，期间事务B 插入了相同搜索条件的新数据，事务A再次按照原先条件进行读取时，发现了事务B 新插入的数据称为幻读
不可重复读:如果事务A 按一定条件搜索，期间事务B 删除了符合条件的某一条数据，导致事务A 再次读取时数据少了一条。这种情况归为不可重复读

如果是使⽤此隔离级别，就必须使⽤⾏级别的⼆进制⽇志
此隔离级别还有另外的特点：
对于update和delete语句只会在约束条件对应的⾏上增加锁
对update语句来说，如果对应的⾏上已经有锁，则InnoDB会执⾏半⼀致读的操作，来确定update语句对应的⾏在上次commit之后的数据是否在锁的范围，如果不是，则不影响update操作，如果是，则需要等待对应的锁解开

⽐如如下情况：

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;

表中并没有任何索引，所以会使⽤隐藏创建的聚簇索引来施加⾏级锁,当第⼀个链接执⾏修改:

SET autocommit = 0;
UPDATE t SET b = 5 WHERE b = 3;

之后第⼆个链接执⾏修改：

SET autocommit = 0;
UPDATE t SET b = 4 WHERE b = 2;

对可重复读隔离级别来说，第⼀个事务的修改会在每⾏记录上都增加排他锁，并且直到事务结束后锁才会释放

x-lock(1,2); retain x-lock
x-lock(2,3); update(2,3) to (2,5); retain x-lock
x-lock(3,2); retain x-lock
x-lock(4,3); update(4,3) to (4,5); retain x-lock
x-lock(5,2); retain x-lock

⽽第⼆个事务会⼀直等待前⾯事务的锁被释放后才能执⾏

x-lock(1,2); block and wait for first UPDATE to commit or roll back

对读取提交内容事务隔离级别来说，第⼀个修改操作会在所有⾏上都加排他锁，即首先第一条语句同样会获取所有行的x-lock，然后它会去检查where条件，如果不匹配会立即释放掉这条记录的x-lock

x-lock(1,2); unlock(1,2) releases those for rows that it does not modify
x-lock(2,3); update(2,3) to (2,5); retain x-lock
x-lock(3,2); unlock(3,2)
x-lock(4,3); update(4,3) to (4,5); retain x-lock
x-lock(5,2); unlock(5,2)

⽽第⼆个事务通过半⼀致读的⽅式判断每⾏的最后commit的数据是否在修改的范围⾥，会在未加锁的⾏上加上排他锁(即第二条查询语句会先读取每条记录的一个最新的快照，然后去检查where条件是否匹配。)

READ UNCOMMITTED：
读取未提交内容，所读到的数据可能是脏数据
SERIALIZABLE：
串⾏化，此隔离级别更接近于可重复读这个级别，只是当autocommit功能被禁⽤后，InnoDB引擎会将每个select语句隐含的转化为select … lock in share mode

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

 mysql> -- Do a transaction with autocommit turned on.
 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的事务都提交的情况下才能看到

⼀致读在某些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 sharemode都有可能导致重复错误数据出现，因为有可能两个事务会读到相同的值，在这种情况下就要使⽤select … for update语句保证⼀个事务在读时，另⼀个事务必须等待

SELECT counter_field FROM child_codes FOR UPDATE;
UPDATE child_codes SET counter_field = counter_field + 1;

具体例子：
商品goods表中有一个字段status，status为1代表商品未被下单，status为2代表商品已经被下单，那么我们对某个商品下单时必须确保该商品status为1。假设商品的id为1。如果不采用锁，那么操作方法如下：

//1.查询出商品信息
select status from t_goods where id=1;
//2.根据商品信息生成订单
insert into t_orders (id,goods_id) values (null,1);
//3.修改商品status为2
update t_goods set status=2;

在上面的场景中，商品信息从查询出来到修改，中间有一个处理订单的过程，使用select … for update的原理就是，当我们在查询出goods信息后就把当前的数据锁定，直到我们修改完毕后再解锁。那么在这个过程中，因为goods被锁定了，就不会出现有第三者来对其进行修改了。要使用悲观锁，我们必须关闭mysql数据库的自动提交属性。

set autocommit=0;　　
//设置完autocommit后，我们就可以执行我们的正常业务了。具体如下：
//0.开始事务
begin;/begin work;/start transaction; (三者选一就可以)
//1.查询出商品信息
select status from t_goods where id=1 for update;
//2.根据商品信息生成订单
insert into t_orders (id,goods_id) values (null,1);
//3.修改商品status为2
update t_goods set status=2;
//4.提交事务
commit;/commit work;

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之前的间隔锁住

 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     |
 +-------------------------------+-------+

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发现死锁时，会将其中的⼀个事务作为牺牲品回滚。

可以通过设置innodb_deadlock_detect参数可以打开或关闭死锁检测:
innodb_deadlock_detect = on 打开死锁检测，数据库发生死锁时自动回滚（默认选项）
innodb_deadlock_detect = off 关闭死锁检测，发生死锁的时候，用锁超时来处理，通过设置锁超时参innodb_lock_wait_timeout可以在超时发生时回滚被阻塞的事务

通过innodb_deadlock_detect参数配置⾃动侦测功能是否开启，如果关闭的话，InnoDB就使⽤innodb_lock_wait_timeout参数来⾃动回滚等待⾜够时间的事务
可以通过show engine innodb status语句查看最后⼀次发⽣死锁的情况

⽐如以下例⼦产⽣的死锁：
链接1：

 mysql> CREATE TABLE t (i INT) ENGINE = InnoDB;
 Query OK, 0 rows affected (1.07 sec)

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

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

 mysql> SELECT * FROM t WHERE i = 1 LOCK IN SHARE MODE; ##在 i=1记录上加共享锁
 +------+
 | i    |
 +------+
 | 1    | 
 +------+

链接2：

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

mysql> DELETE FROM t WHERE i = 1; ##请求在i=1的记录上增加排他锁，但被链接1的事务阻⽌

链接1：

mysql> DELETE FROM t WHERE i = 1;
 ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

这个死锁发⽣是因为链接1试图施加排他锁，但因为链接2上的事务已经在请求排他锁，⽽这个锁的释放必须要等待链接1上的事务释放共享锁，⽽链接1上原本的共享锁由于顺序的原因也⽆法升级为排它锁，所以就导致了死锁的发⽣。

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... for update和select...lock in share mode语句时可以使⽤读取提交数据隔离级别
当要访问多个表数据或者要访问相同表的不同⾏集合时，尽可能的保证每次访问的顺序是相同的。⽐如可以将多个语句封装在存储过程中，通过调⽤同⼀个存储过程的⽅法可以减少死锁的发⽣
增加合适的索引以便语句执⾏所扫描的数据范围⾜够⼩
尽可能的少使⽤锁，⽐如如果可以承担幻读的情况，则直接使⽤select语句，⽽不要使⽤select...for update语句
如果没有其他更好的选择，则可以通过施加表级锁将事务执⾏串⾏化，最⼤限度的限制死锁发⽣

 SET autocommit=0;
 LOCK TABLES t1 WRITE, t2 READ, ...;
 ... do something with tables t1 and t2 here ...
 COMMIT;
 UNLOCK TABLES;

九、MySQL锁机制和事务

InnoDB锁机制

InnoDB锁相关系统表

InnoDB锁机制

⾏级锁

间隔锁

Next-key锁

插⼊意图锁

⾃增锁

InnoDB锁相关系统变量

InnoDB事务隔离级别

Autocommit/commit/rollback

⼀致读

加锁读操作

SQL语句对应的锁

幻读

InnoDB死锁

InnoDB死锁检测和回滚

减少死锁发⽣的⽅法

相关文章

网友评论

延伸阅读

深度阅读

栏目导航

热点阅读