mysql半一致性读案例分析

一.基本信息

版本：mysql 5.6.29

事务隔离级别(TR_ISOLATION): READ COMMITED

建表：

CREATE TABLE `test_locks` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(20) DEFAULT NULL,
  `age` INT(11)
) ENGINE=InnoDB

插入数据：
INSERT INTO test_locks(id,name,age) VALUES(1,'a',10);
INSERT INTO test_locks(id,name,age) VALUES(10,'b',50);
INSERT INTO test_locks(id,name,age) VALUES(16,'c',500);

 

二.案例测试

测试1（有阻塞）
S1：begin; UPDATE test_locks SET NAME=NAME+'UPDATE' WHERE ID=16; 

S2：begin; SELECT *  FROM  test_locks WHERE id = 1 lock in share mode; 

测试2（无阻塞）
S1：begin; SELECT *  FROM  test_locks WHERE id = 1 lock in share mode;
S2：begin; UPDATE test_locks SET NAME=NAME+'UPDATE' WHERE ID=16;

测试3（无阻塞）
S1：begin; UPDATE test_locks SET NAME=NAME+'UPDATE' WHERE ID=50;

S2：begin; SELECT *  FROM  test_locks WHERE id = 1 lock in share mode;  

测试4（无阻塞）
S1：begin; SELECT *  FROM  test_locks WHERE id = 1 lock in share mode;
S2：begin; UPDATE test_locks SET NAME=NAME+'UPDATE' WHERE ID=50;

测试5：（有阻塞）
S1：begin; delete FROM  test_locks WHERE id = 1;
S2：begin; delete FROM  test_locks WHERE id = 50; 

测试6：（有阻塞）

S1：begin; SELECT * FROM  test_locks WHERE id = 1 FOR UPDATE;

S2：begin; SELECT * FROM  test_locks WHERE id = 50 FOR UPDATE; 

测试7：（有阻塞）

S1：begin; SELECT *  FROM  test_locks WHERE id = 1 lock in share mode;

S2：begin; SELECT *  FROM  test_locks WHERE id = 50 FOR UPDATE; 

测试8：（有阻塞）

S1：begin; UPDATE test_locks SET NAME=NAME+'UPDATE' WHERE ID=10;

S2：begin; UPDATE test_locks SET NAME=NAME+'UPDATE' WHERE ID=10;

因为表没有主键或索引，上面除测试2、测试3和测试4之外，都会锁全表，所以会话2会阻塞。 

我们先看测试3:

测试3（无阻塞）
S1：begin; UPDATE test_locks SET NAME=NAME+'UPDATE' WHERE ID=50;

S2：begin; SELECT *  FROM  test_locks WHERE id = 1 lock in share mode;

由于ID=50的记录不存在，会话二有一个行锁（实际就是锁全表），会话一没有记录锁，所以没有阻塞。

执行计划如下：

(product)root@localhost [demo]> explain UPDATE test_locks SET NAME=NAME+'UPDATE' WHERE ID=50;

+----+-------------+------------+------+---------------+------+---------+------+------+-------------+

| id | select_type | table      | type | possible_keys | key  | key_len | ref  | rows | Extra       |

+----+-------------+------------+------+---------------+------+---------+------+------+-------------+

|  1 | SIMPLE      | test_locks | ALL  | NULL          | NULL | NULL    | NULL |    3 | Using where |

+----+-------------+------------+------+---------------+------+---------+------+------+-------------+

1 row in set (0.00 sec)1 row in set (0.00 sec)

 

(product)root@localhost [demo]> explain SELECT *  FROM  test_locks WHERE id = 1 lock in share mode;

+----+-------------+------------+------+---------------+------+---------+------+------+-------------+

| id | select_type | table      | type | possible_keys | key  | key_len | ref  | rows | Extra       |

+----+-------------+------------+------+---------------+------+---------+------+------+-------------+

|  1 | SIMPLE      | test_locks | ALL  | NULL          | NULL | NULL    | NULL |    3 | Using where |

+----+-------------+------------+------+---------------+------+---------+------+------+-------------+

 

Show engine innodb status\G如下：

---TRANSACTION 36625, ACTIVE 97 sec
2 lock struct(s), heap size 360, 1 row lock(s)
MySQL thread id 4, OS thread handle 0x7f20ec040700, query id 20 localhost root cleaning up
TABLE LOCK table `demo`.`test_locks` trx id 36625 lock mode IS
RECORD LOCKS space id 12 page no 3 n bits 72 index `GEN_CLUST_INDEX` of table `demo`.`test_locks` trx id 36625 lock mode S locks rec but not gap
---TRANSACTION 36624, ACTIVE 113 sec
2 lock struct(s), heap size 360, 0 row lock(s)
MySQL thread id 3, OS thread handle 0x7f20ec071700, query id 22 localhost root init
show engine innodb status
TABLE LOCK table `demo`.`test_locks` trx id 36624 lock mode IX
RECORD LOCKS space id 12 page no 3 n bits 72 index `GEN_CLUST_INDEX` of table `demo`.`test_locks` trx id 36624 lock_mode X locks rec but not gap

 若把测试3改成如下：

S1：begin; UPDATE test_locks SET NAME=NAME+'UPDATE' WHERE ID=10;

S2：begin; SELECT *  FROM  test_locks WHERE id = 1 lock in share mode;

 会话一会排它锁全表，会话二再请求共享锁是就会阻塞。

执行计划如下：

(product)root@localhost [demo]> explain UPDATE test_locks SET NAME=NAME+'UPDATE' WHERE ID=10;

+----+-------------+------------+------+---------------+------+---------+------+------+-------------+

| id | select_type | table      | type | possible_keys | key  | key_len | ref  | rows | Extra       |

+----+-------------+------------+------+---------------+------+---------+------+------+-------------+

|  1 | SIMPLE      | test_locks | ALL  | NULL          | NULL | NULL    | NULL |    3 | Using where |

+----+-------------+------------+------+---------------+------+---------+------+------+-------------+

1 row in set (0.00 sec)1 row in set (0.00 sec)

 

(product)root@localhost [demo]> explain SELECT *  FROM  test_locks WHERE id = 1 lock in share mode;

+----+-------------+------------+------+---------------+------+---------+------+------+-------------+

| id | select_type | table      | type | possible_keys | key  | key_len | ref  | rows | Extra       |

+----+-------------+------------+------+---------------+------+---------+------+------+-------------+

|  1 | SIMPLE      | test_locks | ALL  | NULL          | NULL | NULL    | NULL |    3 | Using where |

+----+-------------+------------+------+---------------+------+---------+------+------+-------------+

1 row in set (0.00 sec)

Show engine innodb status\G如下：

---TRANSACTION 36627, ACTIVE 17 sec

2 lock struct(s), heap size 360, 1 row lock(s)

MySQL thread id 9, OS thread handle 0x7f20ec040700, query id 37 localhost root cleaning up

TABLE LOCK table `demo`.`test_locks` trx id 36627 lock mode IS

RECORD LOCKS space id 12 page no 3 n bits 72 index `GEN_CLUST_INDEX` of table `demo`.`test_locks` trx id 36627 lock mode S locks rec but not gap

---TRANSACTION 36626, ACTIVE 42 sec

2 lock struct(s), heap size 360, 1 row lock(s), undo log entries 1

MySQL thread id 8, OS thread handle 0x7f20ec071700, query id 38 localhost root init

show engine innodb status

TABLE LOCK table `demo`.`test_locks` trx id 36626 lock mode IX

RECORD LOCKS space id 12 page no 3 n bits 72 index `GEN_CLUST_INDEX` of table `demo`.`test_locks` trx id 36626 lock_mode X locks rec but not gap

 再看下测试2：

测试2（无阻塞）
S1：begin; SELECT *  FROM  test_locks WHERE id = 1 lock in share mode;
S2：begin; UPDATE test_locks SET NAME=NAME+'UPDATE' WHERE ID=16;

由于没有主键或索引，会话一会对全表产生共享锁，会话二执行update时，需要获取排它锁，但因半一致性读特性，update会读取符合该条件的最近一次提交的记录并锁定，并不需要等待会话一释放锁。

执行计划同前面一样。

Show engine innodb status\G如下：

---TRANSACTION 36630, ACTIVE 20 sec

2 lock struct(s), heap size 360, 1 row lock(s), undo log entries 1

MySQL thread id 11, OS thread handle 0x7f20ec071700, query id 59 localhost root init

show engine innodb status

TABLE LOCK table `demo`.`test_locks` trx id 36630 lock mode IX

RECORD LOCKS space id 12 page no 3 n bits 72 index `GEN_CLUST_INDEX` of table `demo`.`test_locks` trx id 36630 lock_mode X locks rec but not gap

---TRANSACTION 36629, ACTIVE 35 sec

2 lock struct(s), heap size 360, 1 row lock(s)

MySQL thread id 12, OS thread handle 0x7f20ec040700, query id 56 localhost root cleaning up

TABLE LOCK table `demo`.`test_locks` trx id 36629 lock mode IS

RECORD LOCKS space id 12 page no 3 n bits 72 index `GEN_CLUST_INDEX` of table `demo`.`test_locks` trx id 36629 lock mode S locks rec but not gap

 

最后测试4无阻塞是因为ID=50的记录不存在，与测试3情况一样。

测试4（无阻塞）
S1：begin; SELECT *  FROM  test_locks WHERE id = 1 lock in share mode;
S2：begin; UPDATE test_locks SET NAME=NAME+'UPDATE' WHERE ID=50;

三.半一致性读

什么是半一致性读（semi-consistent read）？

简单来说，semi-consistent read是read committed与consistent read两者的结合。一个update语句，如果读到一行已经加锁的记录，此时InnoDB返回记录最近提交的版本，由MySQL上层判断此版本是否满足 update的where条件。若满足(需要更新)，则MySQL会重新发起一次读操作，此时会读取行的最新版本(并加锁)。semi-consistent read只会发生在read committed隔离级别下，或者是参数innodb_locks_unsafe_for_binlog被设置为true(该参数即将被废弃)。

发生semi consistent read(半一致性读)情形：
1、RC、RU模式下，或者 innodb_locks_unsafe_for_binlog = 1。
2、先执行非UPDATE SQL，后执行UPDATE。

3、只影响有实际存在的行。 

semi-consistent优缺点分析:

优点:

减少了更新同一行记录时的冲突，减少锁等待。

无并发冲突，读记录最新版本并加锁；有并发冲突，读事务最新的commit版本，不加锁，无需锁等待。

可以提前放锁，进一步减少并发冲突概率。

对于不满足update更新条件的记录，可以提前放锁，减少并发冲突的概率。

在理解了semi-consistent read原理及实现方案的基础上，可以酌情考虑使用semi-consistent read，提高系统的并发性能。

缺点:

非冲突串行化策略，因此对于binlog来说，是不安全的

两条语句，根据执行顺序与提交顺序的不同，通过binlog复制到备库后的结果也会不同。不是完全的冲突串行化结果。

因此只能在事务的隔离级别为read committed(或以下)，或者设置了innodb_locks_unsafe_for_binlog参数的情况下才能够使用。