项目中遇到的MySql行锁与并发性关系(2)

继续之前的话题:

在连接1中执行COMMIT操作:

查询当前表数据:

SELECT * FROM TEST_ALR;
ID	name
1	张三
2	李四
3	王五
4	赵六
5	孙七

在连接1开启事务,然后对ID=1的行加行锁:

SET autocommit=0;
SELECT * FROM TEST_ALR WHERE ID=1 FOR UPDATE;

此时在连接2对ID=1的行进行UPDATE操作:

UPDATE TEST_ALR SET NAME='TEST' WHERE ID=1;

在等待了一段时间之后报等待超时错误:

Error Code : 1205

Lock wait timeout exceeded; try restarting transaction

然后对ID=2的行进行UPDATE操作

UPDATE TEST_ALR SET NAME='TEST' WHERE ID=2;

1 row(s) affected

发现成功执行,此时的TEST_ALR进行的是行级锁而不是表级锁

因为行锁是需要根据索引列来做判断的,所以如果用子查询读取出来的数据当做索引列锁定的行数的时候会发现锁的是表而不是行,除非子查询中的表和外表之前有主外键关系,建立TEST_ALR的外键表:

DROP TABLE IF EXISTS TEST_RLA;
CREATE TABLE TEST_RLA
(
   ID                   INT NOT NULL AUTO_INCREMENT COMMENT 'ID',
   PRIMARY KEY (ID),
  CONSTRAINT TEST_LINE_LOCK FOREIGN KEY (ID) REFERENCES TEST_ALR (ID)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 ;

往表中插入测试数据:

INSERT INTO TEST_RLA VALUES (1),(2),(3),(4),(5);

然后用子查询对表TEST_ALR加行锁:

SELECT * FROM TEST_ALR WHERE ID IN (SELECT ID FROM TEST_RLA) FOR UPDATE;

然后在连接2中对ID=2,3的行进行UPDATE操作

UPDATE TEST_ALR SET NAME='TEST' WHERE ID=2;

Error Code : 1205

Lock wait timeout exceeded; try restarting transaction

UPDATE TEST_ALR SET NAME='TEST' WHERE ID=3;

Error Code : 1205

Lock wait timeout exceeded; try restarting transaction

实际测试时1-5都出现锁表情况了,当UPDATE ID=7的时候:

UPDATE TEST_ALR SET NAME='TEST' WHERE ID=7;
1 row(s) affected

证明UPDATE成功了,所以证明有主外键关系的子查询是可以加行锁的,猜测原理大概是因为MySql对表加行锁的时候,实际上是在索引列上的每一行都打了个标记,而其他连接判断该表的某行是否发生行锁的话直接读取索引的信息就能够解决问题了,而主外键关系的子查询关联条件其实是走的索引,而不是通过扫描子查询中的实际数据,所以可以加行锁,所以如果没有主外键关系的子查询,因为Mysql并不知道子查询表中的数据到底是什么样的,所以不能对其加行锁,而是对外表加的表级锁