最近对insert的锁操作比较费解,所以自己动手,一看究竟。主要是通过一下三个sql来看一下执行中的sql的到底使用了什么锁。

select * from information_schema.INNODB_TRX\G; //记录当前正在执行的事务,以及事务的一些状态

select * from information_schema.INNODB_LOCKS\G; //包含了InnoDB事务锁的具体情况,包括事务正在申请加的锁和事务加上的锁。

select * from information_schema.INNODB_LOCK_WAITS; //包含了blocked的事务的锁等待的状态

mysql的版本:5.6.25-73.1-log  使用了innodb,隔离级别是 REPEATABLE-READ

首先看下表结构: 表中有唯一键

分别在俩个事务中执行同样的insert操作:

insert into room_poi_cut_result (poi_id,poi_orgunit_id,old_bd,new_bd,cut_reason,is_execute) values(12345,22,12,12,0,0);

首先看下 INNODB_TRX 表的数据:可以看到 事务10722584462处于执行状态,事务10722584486处于阻塞状态

然后再看下锁的状态:可以看到,事务10722584462和事务10722584486都是在获取行锁,一个是S锁,一个是X锁

最后我们看下 INNODB_LOCK_WAITS 这个表中的数据:可以看到事务10722584486正在被阻塞在10722584486:29178:10:638 这个锁上,而这个锁的持有者是事务10722584462

所以,这里可以得到结论,首先会在唯一键加上行锁,其次insert语句会有一个锁升级的过程,从S锁升级到X锁。

 

接下来做第二个实验,在inser中加入 on update语句。

分别在俩个事务中执行同样的insert操作:

insert into room_poi_cut_result (poi_id,poi_orgunit_id,old_bd,new_bd,cut_reason,is_execute) values(12345,22,12,12,0,0) on duplicate key update poi_id=12345;

然后再看下INNODB_TRX,INNODB_LOCKS,INNODB_LOCK_WAITS 这三个表的数据:

结论,首先会在唯一键加上行锁,其次和第一次实验不同的是,直接是在唯一键上加X锁。

 

接下来做第三个实验,把表的唯一键去掉,然后执行insert操作:

表结构:

然后在俩个事务中执行以下insert语句:

insert into room_poi_cut_result2 (poi_id,poi_orgunit_id,old_bd,new_bd,cut_reason,is_execute) values(12345,22,12,12,0,0);

结果没有发生阻塞,看下INNODB_TRX 这个表中的数据:可以看到俩个事务都是在running状态,而且trx_rows_locked 都是0