数据库事务与锁（四）——实战

本文主要围绕主键（同行、不同行）加锁、普通列加锁、索引（普通索引、唯一索引）列加锁几种情况进行展开说明。

新建数据库tbook，id列为主键，name列为唯一索引，price列为普通索引，author列为普通列：

CREATE TABLE `tbook` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(45) DEFAULT NULL,
  `author` varchar(25) DEFAULT '',
  `price` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `index_name` (`name`) USING BTREE,
  KEY `index_price` (`price`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

主键加锁

1) 同行

不同事务 （同行）	S	X
S	允许	不允许
X	不允许	不允许

演示：事务一：

 

事务二：

分析：对比事务一与事务二，发现给同一行主键加X锁，会出错。可以通过查看相关的表INNODB_LOCKS, INNODB_LOCK_WAITS, INNODB_TRX查看事务与锁的具体情况。其他情况（S+S,S+X,X+S）可自行验证。

2) 不同行

不同事务 （不同行）	S	X
S	允许	允许
X	允许	允许

演示：事务一：

 

事务二：

分析：对比事务一与事务二，发现给不同行的逐渐加X锁，不影响。其他情况（S+S,S+X,X+S）可自行验证。

总结：主键加锁不会引起全表加锁，只会针对特定行。

普通列加锁

不同事务 （同行或不同行）	S	X
S	允许	不允许
X	不允许	不允许

演示：事务一：

 

事务二：

分析：事务一对普通列author（此时Id为1）加X锁，事务二对Id=2行加X锁，报错。查看INNODB_TRX表，查看trx_rows_locked字段，可以看出对普通列加锁，会导致给表中的每一行进行加锁。

总结：普通列加锁，会引起全表扫描, 导致全表数据被锁。

索引列加锁

1) 唯一索引

演示：事务一：

 

事务二：

分析：事务一对唯一索引name列加X锁，事务二对其他name列或主键列加X锁，不影响，对同一行的主键加锁时会报错。

总结：唯一索引列加锁，只加锁一条，不会导致全表数据被锁。

2) 普通索引

演示：事务一：

 

事务二：

分析：事务一对普通索引price列加X锁，查看表INNODB_TRX的字段trx_rows_locked=3，事务二再插入数据，发现有些可以插入成功，有些却失败，原因就在于间隙锁和NEXT-KEY锁的存在。由于间隙锁和NEXT-KEY锁的存在，普通索引列会分为几个区间范围，(负无穷，10),(10,20),(20,30),(30,44),(44,90),(90,正无穷)。因为对price=10加锁，则位于10的前后区间，即(负无穷，10),(10,20)均受到影响。

总结：普通索引加锁，由于间隙锁或NEXT-KEY锁的存在，会影响多行数据被锁。