InnoDB共享锁和排它锁（行锁类型）

前言：

    最怕面试官问到一大堆的锁概念了，表锁、页锁、行锁，排它锁、共享锁...

    有关于锁的概念实在太多了，而我们在实际使用中使用到的又太少。很少有专门去写特定类型的锁实现，一般都是数据库默认帮我们做了相应的锁动作。

    本文就着示例把InnoDB中的行锁的两种标准实现：共享锁和排它锁分析一下

 

准备：

    1）笔者新建了张表（实际是copy的其他表），city_copy表，主键为ID，自增长模式，默认从1开始。表创建SQL如下：

CREATE TABLE `city_copy`  (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `Name` char(35) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL DEFAULT '',
  `CountryCode` char(3) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL DEFAULT '',
  `District` char(20) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL DEFAULT '',
  `Population` int(11) NOT NULL DEFAULT 0,
  PRIMARY KEY (`ID`) USING BTREE,
  INDEX `CountryCode`(`CountryCode`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 4080 CHARACTER SET = latin1 COLLATE = latin1_swedish_ci ROW_FORMAT = Dynamic;

    2）有关于InnoDB事务和锁记录的几张表

        information_schema.INNODB_TRX：事务信息表

        information_schema.INNODB_LOCKS：锁信息表

        information_schema.INNODB_LOCK_WAITS：锁等待信息表

 

1.共享锁事务之间的读取     1）session1（共享锁）

set autocommit=0; ##首先就是关闭自动提交
select * from city where id < 4 lock in share mode; ##以共享模式读取数据

select * from information_schema.INNODB_TRX; ##查询事务信息

    查询事务结果值：

    可以看到，当前SQL执行完成之后存在已经RUNNING(运行中)的事务，锁定记录数为4行

 

    2）session2（共享锁）

    与session1执行相同的动作

set autocommit=0; ##首先就是关闭自动提交
select * from city where id < 4 lock in share mode; ##以共享模式读取数据

select * from information_schema.INNODB_TRX; ##查询事务信息

    结果就是：出现两条RUNNING事务记录，两个select语句分别都执行成功，获取到结果值。

    两个session分别执行commit之后，运行中的事务信息消息。说明事务结束。

 

    总结：共享锁与共享锁之间是相互兼容的。

 

2.共享锁与排它锁的事务示例     1）session1（共享锁）

set autocommit=0; ##首先就是关闭自动提交
select * from city where id < 4 lock in share mode; ##以共享模式读取数据

select * from information_schema.INNODB_TRX; ##查询事务信息

    session1执行共享锁读取方式，结果与1相同，不再展示

 

    2）session2(排它锁)

set autocommit=0; ##首先就是关闭自动提交
select * from city where id < 4 for update; ##以排它锁的方式读取
select * from information_schema.INNODB_TRX; ##查询事务信息

// res 事务结果值如下
trx_id	trx_state	trx_started	trx_requested_lock_id	trx_wait_started	trx_weight	trx_mysql_thread_id	trx_query	trx_operation_state	trx_tables_in_use	trx_tables_locked	trx_lock_structs	trx_lock_memory_bytes	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	trx_autocommit_non_locking
462611	LOCK WAIT	2019-12-28 18:01:58	462611:50:5:2	2019-12-28 18:01:58	2	9	select * from city where id < 4 for update	starting index read	1	1	2	1136	1	0	0	REPEATABLE READ	1	1		0	0	0	0
284711444560536	RUNNING	2019-12-28 18:01:38			2	10	select * from information_schema.INNODB_TRX		0	1	2	1136	4	0	0	REPEATABLE READ	1	1		0	0	0	0

    根据事务结果值可以看到，与之前的状态有所不同

    id=284711444560536的事务是session1的共享锁事务；

    id=462611的事务是session中的排它锁事务，目前是锁等待状态(表现形式就是目前获取不到select结果值，一直在转圈执行)，这个会一直等待session1锁释放或者session2锁超时时候回滚。

 

    总结：共享锁与排他锁是互不兼容的。

    读者也可以试试，先执行session2再执行session1，结果值也是互不兼容的。

 

总结：

    1.我们首先需要记住的是：共享锁和排它锁都是行锁类型，所以上述的例子都是锁定id