Mysql锁相关知识

前言

今天我们来看下Mysql中锁相关的一些内容。

正文

简单对比

Mysql 存储引擎相关对比如下：

Mysql存储引擎	事务	行锁	表锁
MyISAM	不支持	不支持	支持
InnoDB	支持	支持	支持

行锁与表锁的对比如下：

锁	开销	加锁速度	是否可能死锁	粒度范围	冲突概率	并发度
行锁	开销大	加锁慢	可能会出现死锁	粒度小	冲突概率低	并发度高
表锁	开销小	加锁快	不会出现死锁	粒度大	冲突概率高	并发度低

Mysql InnoDB 存储引擎默认使用行锁。

InnoDB锁的争用情况

对于 InnoDB 存储引擎，可以通过检查 InnoDB_row_lock 状态变量来分析Mysql上的行锁的争夺情况：

show status like 'innodb_row_lock%';

如果innodb_row_lock_waits和innodb_row_lock_time_avg的值比较高，说明可能锁的争夺比较严重。

我们可以通过设置 InnoDB Monitors 来进一步观察发生锁冲突的表、数据行等，并分析锁争用的原因。

如下语句：

1. 开启监视器：

   CREATE TABLE innodb_monitor(a INT) ENGINE=INNODB;

   

2. 查询信息：

   show engine innodb status;

   

   Status 字段内容如下：

   =====================================
   2021-07-13 10:32:09 0x7ff568255700 INNODB MONITOR OUTPUT
   =====================================
   Per second averages calculated from the last 52 seconds
   -----------------
   BACKGROUND THREAD
   -----------------
   srv_master_thread loops: 177362 srv_active, 0 srv_shutdown, 932030 srv_idle
   srv_master_thread log flush and writes: 1109392
   ----------
   SEMAPHORES
   ----------
   OS WAIT ARRAY INFO: reservation count 389051
   OS WAIT ARRAY INFO: signal count 362447
   RW-shared spins 0, rounds 543070, OS waits 271365
   RW-excl spins 0, rounds 25094, OS waits 703
   RW-sx spins 278, rounds 7602, OS waits 187
   Spin rounds per wait: 543070.00 RW-shared, 25094.00 RW-excl, 27.35 RW-sx
   ------------
   TRANSACTIONS
   ------------
   Trx id counter 63028783
   
   .................略去部分内容
   
   0 read views open inside InnoDB
   Process ID=17317, Main thread ID=140694730979072, state: sleeping
   Number of rows inserted 227880, updated 171667, deleted 2140, read 9979111369
   0.00 inserts/s, 0.13 updates/s, 0.00 deletes/s, 9813.50 reads/s
   ----------------------------
   END OF INNODB MONITOR OUTPUT
   ============================

3. 移除监视器

   DROP TABLE innodb_monitor;

注意：设置监视器后，在show engine innodb status的显示内容中，会有详细的当前锁等待的信息，包括表名、锁类型、锁定记录的情况等，便于进行进一步的分析和问题的确定。打开监视器以后，默认情况下每15秒会向日志中记录监控的内容，如果长时间打开会导致.err文件变得非常的巨大，所以用户在确认问题原因之后，要记得删除监控表以关闭监视器，或者通过使用--console选项来启动服务器以关闭写日志文件。

PS：MySQL 8.0 版本弃用了innodb_monitor，新增了全新的锁观测方式，在performance_schema下新增了data_locks表和data_lock_waits表。

MySQL 文档

InnoDB 的行锁模式及加锁方法

InnoDB 实现了以下两种类型的行锁（Row Locks）。

• 共享锁（shared lock）：允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁。
• 排他锁（exclusive lock）：允许获得排他锁的事务更新数据，阻止其他事务取得相同数据集的共享读锁和排他写锁。

另外，为了允许行锁和表锁（Table Locks）共存，实现多粒度锁机制，InnoDB还有两种内部使用的意向锁（Intention Locks），这两种意向锁都是表锁。

• 意向共享锁（Intention shared lock）：事务打算给数据行共享锁，事务在给一个数据行加共享锁前必须先取得该表的意向共享锁。
• 意向排他锁（Intention exclusive lock）：事务打算给数据行加排他锁，事务在给一个数据行加排他锁前必须先取得该表的意向排他锁。

上述锁模式的兼容情况具体如下表所示。

请求锁模式 是否兼容 当前锁模式	排他锁	意向排他锁	共享锁	意向共享锁
排他锁	冲突	冲突	冲突	冲突
意向排他锁	冲突	兼容	冲突	兼容
共享锁	冲突	冲突	兼容	兼容
意向共享锁	冲突	兼容	兼容	兼容

如果一个事务请求的锁模式与当前的锁兼容，InnoDB就将请求的锁授予该事务；反之，如果两者不兼容，该事务就要等待锁释放。

意向锁是InnoDB自动加的，不需用户干预。

对于UPDATE、DELETE和INSERT语句，InnoDB会自动给涉及数据集加排他锁；

对于普通SELECT语句，InnoDB不会加任何锁；

事务可以通过以下语句显式给记录集加共享锁或排他锁。

• 共享锁

  SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE

• 排他锁

  SELECT * FROM table_name WHERE ... FOR UPDATE

用SELECT ... IN SHARE MODE获得共享锁，主要用在需要数据依存关系时来确认某行记录是否存在，并确保没有人对这个记录进行UPDATE或者DELETE操作。但是如果当前事务也需要对该记录进行更新操作，则很有可能造成死锁，对于锁定行记录后需要进行更新操作的应用，应该使用SELECT... FOR UPDATE方式获得排他锁。

假设有一张如下表：

CREATE TABLE `course` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `cou_no` varchar(10) DEFAULT NULL,
  `cou_name` varchar(255) DEFAULT NULL,
  `teach_no` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_teach_no` (`teach_no`) USING BTREE,
  KEY `idx_cou_no` (`cou_no`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

表数据如下：

我们以此表来看下InnoDB存储引擎的共享锁例子。

session_1	session_2
set autocommit = 0; select id,cou_no,cou_name,teach_no from course where id= 1; 结果如下：	set autocommit = 0; select id,cou_no,cou_name,teach_no from course where id= 1; 结果如下：
当前session 对id=1的记录加share mode 的共享锁。 select id,cou_no,cou_name,teach_no from course where id= 1 LOCK in SHARE MODE; 结果如下：
	其他session仍然可以查询记录，并也可以对该记录加share mode的共享锁。 select id,cou_no,cou_name,teach_no from course where id= 1 LOCK in SHARE MODE; 结果如下：
当前session对锁定的记录进行更新操作，会等待锁。 update course set cou_name = '语文1' where id = 1;
	其他session也对该记录进行更新操作，则会导致死锁退出。 update course set cou_name = '语文2' where id = 1;
获得锁后，可以成功更新。

我们再来看下InnoDB存储引擎的排他锁例子。

session_1	session_2
set autocommit = 0; select id,cou_no,cou_name,teach_no from course where id= 1; 结果如下：	set autocommit = 0; select id,cou_no,cou_name,teach_no from course where id= 1; 结果如下：
当前session 对id=1的记录加for update 的排他锁。 select id,cou_no,cou_name,teach_no from course where id= 1 FOR UPDATE; 结果如下：
	其他session可以查询该记录，但是不能对该记录加排他锁，会等待获得锁。
当前session可以对锁定的记录进行更新操作，更新后释放锁。
	其他session获得锁，得到其他session提交的记录。

InnoDB 行锁实现方式

行锁及表锁

InnoDB行锁是通过给索引上的索引项加锁来实现的，这一点MySQL与Oracle不同，后者是通过在数据块中对相应数据行加锁来实现的。InnoDB这种行锁实现特点意味着：只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁！

在实际应用中，要特别注意InnoDB行锁的这一特性，不然的话，可能导致大量的锁冲突，从而影响并发性能。

我们通过例子来看一下这一特性。

1. 在不通过索引条件查询的时候，InnoDB使用的是表锁，而不是行锁。

   新建表tab_no_index，没有索引。

   create table tab_no_index(id int,name varchar(10)) engine=innodb; 
   insert into tab_no_index values(1,'1'),(2,'2'),(3,'3'),(4,'4');  

   InnoDB存储引擎的表在不使用索引时会使用表锁。

   session_1	session_2
   set autocommit=0; select * from tab_no_index where id = 1 ;	set autocommit=0; select * from tab_no_index where id = 2 ;
   select * from tab_no_index where id = 1 for update;
   	select * from tab_no_index where id = 2 for update; 会一直等待锁释放

   在如上表所示的例子中，看起来session_1只给id=1这一行加了排他锁，但session_2在请求其他行的排他锁时，却出现了锁等待！

   原因就是在没有索引的情况下，InnoDB只能使用表锁。

   当我们给其增加一个索引后，InnoDB就只锁定了符合条件的行，如下。

   我们使用有索引的一张表tab_with_index 来看下。

   create table tab_with_index(id int,name varchar(10)) engine=innodb;
   alter table tab_with_index add index id(id); 
   insert into tab_with_index values(1,'1'),(2,'2'),(3,'3'),(4,'4'); 

   InnoDB存储引擎的表在使用索引时使用行锁。

   session_1	session_2
   set autocommit=0; select * from tab_with_index where id = 1 ;	set autocommit=0; select * from tab_with_index where id = 2 ;
   select * from tab_with_index where id = 1 for update;
   	select * from tab_with_index where id = 2 for update;

   可以看到 session_2不会出现锁等待的问题。

2. 由于MySQL的行锁是针对索引加的锁，不是针对记录加的锁，所以虽然是访问不同行的记录，但是如果是使用相同的索引键，是会出现锁冲突的。应用设计的时候要注意这一点。

   上述tab_with_index表中，id字段有索引，name字段没有索引。

   我们再添加一条记录，如下：

   insert into tab_with_index  values(1,'4');
   select * from tab_with_index where id = 1; 

   结果如下：

   

   InnoDB存储引擎使用相同索引键获取锁导致阻塞。

   session_1	session_2
   set autocommit=0;	set autocommit=0;
   select * from tab_with_index where id = 1 and name = '1' for update;
   	虽然session_2访问的是和session_1不同的记录，但是因为使用了相同的索引，所以需要等待锁。 select * from tab_with_index where id = 1 and name = '4' for update;

3. 当表有多个索引的时候，不同的事务可以使用不同的索引锁定不同的行，另外，不论是使用主键索引、唯一索引或普通索引，InnoDB都会使用行锁来对数据加锁。

   我们对表 tab_with_index 的 name字段添加普通索引。

   alter table tab_with_index add index name(name); 

   InnoDB存储引擎的表使用不同索引锁定不同的行。

   session_1	session_2
   set autocommit=0;	set autocommit=0;
   select * from tab_with_index where id = 1 for update;
   	session_2使用name的索引访问记录，因为记录没有被索引，所以可以获得锁。 select * from tab_with_index where name = '2' for update;
   	select * from tab_with_index where name = '4' for update; 由于访问的记录已经被session_1锁定，所以等待获得锁。

4. 即便在条件中使用了索引字段，但是否使用索引来检索数据是由MySQL通过判断不同执行计划的代价来决定的，如果MySQL认为全表扫描效率更高，比如对一些很小的表，它就不会使用索引，这种情况下InnoDB将使用表锁，而不是行锁。因此，在分析锁冲突时，别忘了检查SQL的执行计划，以确认是否真正使用了索引。

   在下面的例子中，检索值的数据类型与索引字段不同，虽然MySQL能够进行数据类型转换，但却不会使用索引，从而导致InnoDB使用表锁。通过用explain检查两条SQL的执行计划，我们可以清楚地看到了这一点。

   EXPLAIN select * from tab_with_index where name = '1';

   

   EXPLAIN select * from tab_with_index where name = 1;

   

   可以看到第二条语句没有使用到索引，即会触发MySQL的行锁。

   session_1	session_2
   set autocommit=0;	set autocommit=0;
   select * from tab_with_index where name = 1 for update; 该语句不走name索引，InnoDB会使用表锁
   	select * from tab_with_index where name = '2' for update; 我们此时session_2访问其他记录，也会进入锁等待。

间隙锁（Next-Key锁）

当我们用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据记录的索引项加锁；对于键值在条件范围内但并不存在的记录，叫做“间隙（GAP)”，InnoDB也会对这个“间隙”加锁，这种锁机制就是所谓的间隙锁（Next-Key锁）。

比如对于上述tab_with_index例子中，我们执行如下SQL。

select * from tab_with_index where id >=4 for update;

可以看到只能查到一条id=4的记录，但是事务情况下，我们向表中插入id=5的数据时，就会出现锁等待。

InnoDB存储引擎的间隙锁情况。

session_1	session_2
set autocommit = 0;	set autocommit = 0;
select * from tab_with_index where id >=4 for update;
	insert into tab_with_index values(1,'100'); 我们向表中插入id<4的数据，可以成功处理，可以说明不是表锁。
	insert into tab_with_index values(5,'5'); 我们向表中插入id>=4的数据，比如id=5，这个id不存在，但是也会等待session_1释放锁，出现锁等待。

可以看到，上述SQL是一个范围查找，不仅会对符合条件的记录加锁（id=4），也会对符合条件但不存在的记录加锁（id >=5）。

InnoDB使用间隙锁的目的，一方面是为了防止幻读，以满足相关隔离级别的要求，对于上面的例子，要是不使用间隙锁，如果其它事务插入了id大于4的任何记录，那么本事务如果再次执行上述语句，就会发生幻读；另外一方面，是为了满足其恢复和复制的需要。

很显然，在使用范围条件检索并锁定记录时，InnoDB这种加锁机制会阻塞符合条件范围内键值的并发插入，这往往会造成严重的锁等待。因此，在实际应用开发中，尤其是并发插入比较多的应用，我们要尽量优化业务逻辑，尽量使用相等条件来访问更新数据，避免使用范围条件。

还要特别说明的是，InnoDB除了通过范围条件加锁时使用间隙锁外，如果使用相等条件请求给一个不存在的记录加锁，InnoDB也会使用间隙锁！

恢复和复制对InnoDB锁机制的影响

MySQL通过BINLOG记录执行成功的INSERT、UPDATE、DELETE等更新数据的SQL语句，并由此实现MySQL数据库的恢复和主从复制。MySQL的恢复机制（复制其实就是在Slave Mysql不断做基于BINLOG的恢复）有以下特点。

• 一是MySQL的恢复是SQL语句级的，也就是重新执行BINLOG中的SQL语句。这与Oracle数据库不同，Oracle是基于数据库文件块的。

• 二是MySQL的BINLOG是按照事务提交的先后顺序记录的，恢复也是按这个顺序进行的。这点也与Oralce不同，Oracle是按照系统更新号（System Change Number，SCN）来恢复数据的，每个事务开始时，Oracle都会分配一个全局唯一的SCN，SCN的顺序与事务开始的时间顺序是一致的。

从上面两点可知，MySQL的恢复机制要求：在一个事务未提交前，其他并发事务不能插入满足其锁定条件的任何记录，也就是不允许出现幻读，这已经超过了ISO/ANSI SQL92 “可重复读”隔离级别的要求，实际上是要求事务要串行化。这也是许多情况下，InnoDB要用到间隙锁的原因，比如在用范围条件更新记录时，无论在Read Commited或是Repeatable Read隔离级别下，InnoDB都要使用间隙锁，但这并不是隔离级别要求的。

另外，对于以下语句：

insert into target_tab select * from source_tab where ...

和

create table new_tab ...select ... from source_tab where ...

这种SQL语句，用户并没有对source_tab做任何更新操作，但MySQL对这种SQL语句做了特别处理。

我们来看下下面这个例子。

我们创建两张一样的表source_tab和 target_tab ，并向source_tab里添加一些数据。

CREATE TABLE `source_tab` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) DEFAULT NULL,
  `sex` char(1) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
CREATE TABLE `target_tab` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) DEFAULT NULL,
  `sex` char(1) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
INSERT INTO `source_tab` (`id`, `name`, `sex`) VALUES ('1', '1', '1');
INSERT INTO `source_tab` (`id`, `name`, `sex`) VALUES ('2', '2', '0');
INSERT INTO `source_tab` (`id`, `name`, `sex`) VALUES ('3', '3', '1');
INSERT INTO `source_tab` (`id`, `name`, `sex`) VALUES ('4', '4', '0');

session_1	session_2
set autocommit = 0; select * from target_tab; select * from source_tab where sex = '0';	set autocommit = 0; select * from target_tab; select * from source_tab where sex = '0';
insert into target_tab select id,name,sex from source_tab where sex = '0';
	update source_tab set name = '1' where sex = '1'; 会一直等待。
commit;
	session_1事务提交后session_2事务执行成功。
	commit;

在上面的例子中，只是简单地读 source_tab表的数据，相当于执行一个普通的SELECT语句，用一致性读就可以了。ORACLE正是这么做的，它通过MVCC技术实现的多版本数据来实现一致性读，不需要给source_tab加任何锁。我们知道InnoDB也实现了多版本数据，对普通的SELECT一致性读，也不需要加任何锁；但这里InnoDB却给source_tab加了共享锁，并没有使用多版本数据一致性读技术！

MySQL为什么要这么做呢？其原因还是为了保证恢复和复制的正确性。因为不加锁的话，如果在上述语句执行过程中，其他事务对source_tab做了更新操作，就可能导致数据恢复的结果错误。

INSERT...SELECT...和 CREATE TABLE...SELECT...语句，可能会阻止对源表的并发更新，造成对源表锁的等待。如果查询比较复杂的话，会造成严重的性能问题，我们在应用中应尽量避免使用。实际上，MySQL将这种SQL叫作不确定（non-deterministic）的SQL，不推荐使用。

如果应用中一定要用这种SQL来实现业务逻辑，又不希望对源表的并发更新产生影响，可以采取以下两种措施：

• 一是将innodb_locks_unsafe_for_binlog的值设置为on，强制MySQL使用多版本数据一致性读。但付出的代价是可能无法用binlog正确地恢复或复制数据，因此，不推荐使用这种方式。
• 二是通过使用select * from source_tab ... Into outfile和load data infile ...语句组合来间接实现，采用这种方式MySQL不会给source_tab加锁。

InnoDB在不同隔离级别下的一致性读及锁的差异

上面讲过，锁和多版本数据是InnoDB实现一致性读和ISO/ANSI SQL92隔离级别的手段，因此，在不同的隔离级别下，InnoDB处理SQL时采用的一致性读策略和需要的锁是不同的。同时，数据恢复和复制机制的特点，也对一些SQL的一致性读策略和锁策略有很大影响。

它们如下表所示：

                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                

隔离级别   一致性读和锁   SQL		Read Uncommited	Read Commited	Repeatable Read	Serializable
SQL	条件
select	相等	None locks	Consisten read/None lock	Consisten read/None lock	Share locks
	范围	None locks	Consisten read/None lock	Consisten read/None lock	Share Next-Key
update	相等	exclusive locks	exclusive locks	exclusive locks	exclusive locks
	范围	exclusive next-key	exclusive next-key	exclusive next-key	exclusive next-key
insert	N/A	exclusive locks	exclusive locks	exclusive locks	exclusive locks
replace	无键冲突	exclusive locks	exclusive locks	exclusive locks	exclusive locks
	键冲突	exclusive next-key	exclusive next-key	exclusive next-key	exclusive next-key
delete	相等	exclusive locks	exclusive locks	exclusive locks	exclusive locks
	范围	exclusive next-key	exclusive next-key	exclusive next-key	exclusive next-key
select ... from ... Lock in share mode	相等	Share locks	Share locks	Share locks	Share locks
	范围	Share locks	Share locks	Share Next-Key	Share Next-Key
select * from ... for update	相等	exclusive locks	exclusive locks	exclusive locks	exclusive locks
	范围	exclusive locks	Share locks	exclusive next-key	exclusive next-key
insert into ... select ...（指源表锁）	innodb_locks_unsafe_for_binlog=off	Share Next-Key	Share Next-Key	Share Next-Key	Share Next-Key
	innodb_locks_unsafe_for_binlog=on	None locks	Consisten read/None lock	Consisten read/None lock	Share Next-Key
create table ... select ...（指源表锁）	innodb_locks_unsafe_for_binlog=off	Share Next-Key	Share Next-Key	Share Next-Key	Share Next-Key
	innodb_locks_unsafe_for_binlog=on	None locks	Consisten read/None lock	Consisten read/None lock	Share Next-Key

从上表可以看出：对于许多SQL，隔离级别越高，InnoDB给记录集加的锁就越严格（尤其是使用范围条件的时候），产生锁冲突的可能性也就越高，从而对并发性事务处理性能的影响也就越大。因此，我们在应用中，应该尽量使用较低的隔离级别，以减少锁争用的机率。实际上，通过优化事务逻辑，大部分应用使用Read Commited隔离级别就足够了。对于一些确实需要更高隔离级别的事务，可以通过在程序中执行SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ或SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE动态改变隔离级别的方式满足需求。

什么时候使用表锁

对于InnoDB表，在绝大部分情况下都应该使用行级锁，因为事务和行锁往往是我们之所以选择InnoDB表的理由。但在个别特殊事务中，也可以考虑使用表级锁。

• 第一种情况是：事务需要更新大部分或全部数据，表又比较大，如果使用默认的行锁，不仅这个事务执行效率低，而且可能造成其他事务长时间锁等待和锁冲突，这种情况下可以考虑使用表锁来提高该事务的执行速度。
• 第二种情况是：事务涉及多个表，比较复杂，很可能引起死锁，造成大量事务回滚。这种情况也可以考虑一次性锁定事务涉及的表，从而避免死锁、减少数据库因事务回滚带来的开销。

当然，应用中这两种事务不能太多，否则，就应该考虑使用MyISAM表了。

在InnoDB下，使用表锁要注意以下两点。

• 使用LOCK TABLES虽然可以给InnoDB加表级锁，但必须说明的是，表锁不是由InnoDB存储引擎层管理的，而是由其上一层 ── MySQL Server负责的，仅当autocommit=0、innodb_table_locks=1（默认设置）时，InnoDB层才能知道MySQL加的表锁，MySQL Server也才能感知InnoDB加的行锁，这种情况下，InnoDB才能自动识别涉及表级锁的死锁；否则，InnoDB将无法自动检测并处理这种死锁。

• 在用 LOCK TABLES对InnoDB表加锁时要注意，要将AUTOCOMMIT设为0，否则MySQL不会给表加锁；事务结束前，不要用UNLOCK TABLES释放表锁，因为UNLOCK TABLES会隐含地提交事务；COMMIT或ROLLBACK并不能释放用LOCK TABLES加的表级锁，必须用UNLOCK TABLES释放表锁。正确的方式见如下语句：

例如，如果需要写表t1并从表t读，可以按如下做：

1. SET AUTOCOMMIT=0; 
2. LOCK TABLES t1 WRITE, t2 READ, ...; 
3. [do something with tables t1 and t2 here]; 
4. COMMIT; 
5. UNLOCK TABLES; 

关于死锁

MyISAM表锁是deadlock free的，这是因为MyISAM总是一次获得所需的全部锁，要么全部满足，要么等待，因此不会出现死锁。但在InnoDB中，除单个SQL组成的事务外，锁是逐步获得的，这就决定了在InnoDB中发生死锁是可能的。如下所示的就是一个发生死锁的例子。

session_1	session_2
set autocommit = 0; select * from table_1 where where id=1 for update; – 做一些其他处理…	set autocommit = 0; select * from table_2 where where id=1 for update;
select * from table_2 where id =1 for update; 因session_2已取得table_2的排他锁，等待	做一些其他处理…
	select * from table_1 where where id=1 for update; session_2想取得table_1的排他锁，但因session_1已取得table_1的排他锁，导致互相等待，造成死锁

在上面的例子中，两个事务都需要获得对方持有的排他锁才能继续完成事务，这种循环锁等待就是典型的死锁。

发生死锁后，InnoDB一般都能自动检测到，并使一个事务释放锁并回退，另一个事务获得锁，继续完成事务。但在涉及外部锁，或涉及表锁的情况下，InnoDB并不能完全自动检测到死锁，这需要通过设置锁等待超时参数 innodb_lock_wait_timeout来解决。需要说明的是，这个参数并不是只用来解决死锁问题，在并发访问比较高的情况下，如果大量事务因无法立即获得所需的锁而挂起，会占用大量计算机资源，造成严重性能问题，甚至拖跨数据库。我们通过设置合适的锁等待超时阈值，可以避免这种情况发生。

通常来说，死锁都是应用设计的问题，通过调整业务流程、数据库对象设计、事务大小，以及访问数据库的SQL语句，绝大部分死锁都可以避免。下面就通过实例来介绍几种避免死锁的常用方法。

1. 在应用中，如果不同的程序会并发存取多个表，应尽量约定以相同的顺序来访问表，这样可以大大降低产生死锁的机会。
2. 在程序以批量方式处理数据的时候，如果事先对数据排序，保证每个线程按固定的顺序来处理记录，也可以大大降低出现死锁的可能。
3. 在事务中，如果要更新记录，应该直接申请足够级别的锁，即排他锁，而不应先申请共享锁，更新时再申请排他锁，因为当用户申请排他锁时，其他事务可能又已经获得了相同记录的共享锁，从而造成锁冲突，甚至死锁。
4. 在REPEATABLE-READ隔离级别下，如果两个线程同时对相同条件记录用SELECT…FOR UPDATE加排他锁，在没有符合该条件记录情况下，两个线程都会加锁成功。程序发现记录尚不存在，就试图插入一条新记录，如果两个线程都这么做，就会出现死锁。这种情况下，将隔离级别改成READ COMMITTED，就可避免问题。
5. 当隔离级别为READ COMMITTED时，如果两个线程都先执行SELECT...FOR UPDATE，判断是否存在符合条件的记录，如果没有，就插入记录。此时，只有一个线程能插入成功，另一个线程会出现锁等待，当第1个线程提交后，第2个线程会因主键重出错，但虽然这个线程出错了，却会获得一个排他锁！这时如果有第3个线程又来申请排他锁，也会出现死锁。对于这种情况，可以直接做插入操作，然后再捕获主键重复异常，或者在遇到主键重复错误时，总是执行ROLLBACK释放获得的排他锁。

尽管通过上面介绍的设计和SQL优化等措施，可以大大减少死锁，但死锁很难完全避免。因此，在程序设计中总是捕获并处理死锁异常是一个很好的编程习惯。

如果出现死锁，可以用show engine innodb status命令来确定最后一个死锁产生的原因。返回结果中包括死锁相关事务的详细信息，如引发死锁的SQL语句，事务已经获得的锁，正在等待什么锁，以及被回滚的事务等。据此可以分析死锁产生的原因和改进措施。

总结

以上就是关于MySQL InnoDB 存储引擎及锁的一些内容。

参考资料

MySQL学习之——锁(行锁、表锁、页锁、乐观锁、悲观锁等)