TheRiver | blog

You have reached the world's edge, none but devils play past here

0%

mysql 幻读

Posted on Edited on
Symbols count in article: 11k Reading time ≈ 10 mins.

参考

mysql实战45讲

概念

phantom problem是指在同一事务下，连续执行两次的sql语句可能导致不同的结果，第二次的sql语句可能会返回之前不存在的行

一定记住是之前不存在的行，这点很重要。

复现

1
2
3
4
5
6
7
8
9
10
11
MySQL [mysql]> select * from tt;
+----+------+------+-------+------+------+
| id | c    | d    | field | f2   | f3   |
+----+------+------+-------+------+------+
|  1 |    1 |  777 |  NULL | NULL | NULL |
|  2 |    2 |  200 |  NULL | NULL | NULL |
|  3 |    3 |   30 |  NULL | NULL | NULL |
|  9 |    5 |   50 |  NULL | NULL | NULL |
| 20 |    4 |   90 |  NULL | NULL | NULL |
+----+------+------+-------+------+------+
5 rows in set (0.00 sec)

设置场景：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
        sessionA                        sessionB
    begin;

T1  select * from tt where c > 2; 
    //select * from tt where c > 2 for update;  
    update tt set d = 0 where c > 2;   


T2                                       insert tt select null,6,60,null,null,null;


T3  select * from tt where c > 2;
    //select * from tt where c > 2 for update;   

    commit;

预期结果：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
T1 select:
|  3 |    3 |   30 |  NULL | NULL | NULL |
|  9 |    5 |   50 |  NULL | NULL | NULL |
| 20 |    4 |   90 |  NULL | NULL | NULL |

--------------------------------------------------

T1 update:
|  3 |    3 |   0 |  NULL | NULL | NULL |
|  9 |    5 |   0 |  NULL | NULL | NULL |
| 20 |    4 |   0 |  NULL | NULL | NULL |

--------------------------------------------------

T2 insert:
|  3 |    3 |   0 |  NULL | NULL | NULL |
|  9 |    5 |   0 |  NULL | NULL | NULL |
| 20 |    4 |   0 |  NULL | NULL | NULL |
| 21 |    6 |  60 |  NULL | NULL | NULL |

--------------------------------------------------

T3 select:

可能1:
|  3 |    3 |   0 |  NULL | NULL | NULL |
|  9 |    5 |   0 |  NULL | NULL | NULL |
| 20 |    4 |   0 |  NULL | NULL | NULL |
| 21 |    6 |  60 |  NULL | NULL | NULL |

可能2:
|  3 |    3 |   0 |  NULL | NULL | NULL |
|  9 |    5 |   0 |  NULL | NULL | NULL |
| 20 |    4 |   0 |  NULL | NULL | NULL |

读提交场景

sessionB:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
MySQL [mysql]>  set session tx_isolation="read-committed";
Query OK, 0 rows affected, 1 warning (0.00 sec)

MySQL [mysql]> select @@tx_isolation;
+----------------+
| @@tx_isolation |
+----------------+
| READ-COMMITTED |
+----------------+
1 row in set, 1 warning (0.00 sec)

MySQL [mysql]> begin;
Query OK, 0 rows affected (0.00 sec)

MySQL [mysql]> insert tt select null,6,60,null,null,null;
ERROR 1665 (HY000): Cannot execute statement: impossible to write to binary log since BINLOG_FORMAT = STATEMENT and at least one table uses a storage engine limited to row-based logging. InnoDB is limited to row-logging when transaction isolation level is READ COMMITTED or READ UNCOMMITTED.

ERROR 1665 (HY000): Cannot execute statement: impossible to write to binary log since BINLOG_FORMAT = STATEMENT and at least one table uses a storage engine limited to row-based logging. InnoDB is limited to row-logging when transaction isolation level is READ COMMITTED or READ UNCOMMITTED.

这里报错，因为binlog现在是statement,innodb认为在rc/ru下，binlog应该设置为row.

ok,改成row,继续：

sessionA

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
MySQL [mysql]> select @@tx_isolation;
+----------------+
| @@tx_isolation |
+----------------+
| READ-COMMITTED |
+----------------+
1 row in set, 1 warning (0.00 sec)

MySQL [mysql]> select * from tt where c > 2; 
+----+------+------+-------+------+------+
| id | c    | d    | field | f2   | f3   |
+----+------+------+-------+------+------+
|  3 |    3 |   30 |  NULL | NULL | NULL |
|  9 |    5 |   50 |  NULL | NULL | NULL |
| 20 |    4 |   90 |  NULL | NULL | NULL |
+----+------+------+-------+------+------+
3 rows in set (0.00 sec)

MySQL [mysql]> update tt set d = 0 where c > 2; 
Query OK, 3 rows affected (0.01 sec)
Rows matched: 3  Changed: 3  Warnings: 0

MySQL [mysql]> select * from tt where c > 2;
+----+------+------+-------+------+------+
| id | c    | d    | field | f2   | f3   |
+----+------+------+-------+------+------+
|  3 |    3 |    0 |  NULL | NULL | NULL |
|  9 |    5 |    0 |  NULL | NULL | NULL |
| 12 |    6 |   60 |  NULL | NULL | NULL |
| 20 |    4 |    0 |  NULL | NULL | NULL |
+----+------+------+-------+------+------+
4 rows in set (0.01 sec)

sessionB

1
2
3
4
5
6
7
8
9
10
11
MySQL [mysql]> select @@tx_isolation;
+----------------+
| @@tx_isolation |
+----------------+
| READ-COMMITTED |
+----------------+
1 row in set, 1 warning (0.00 sec)

MySQL [mysql]> insert tt select null,6,60,null,null,null;
Query OK, 1 row affected (0.03 sec)
Records: 1  Duplicates: 0  Warnings: 0

按照幻读的定义，rc级别下，是会产生幻读的，同理，ru也是一样的。隔离级别：ru < rc < rr < se.序列化读，每次都会加锁，不会产生幻读，是最高的隔离级别。读提交，从定义来看，就是要能读到其他事务提交的结果，本身隔离性就比较低，幻读也比较能理解。主要读提交下，innodb要求binlog是row格式，所以即使幻读，binlog日志恢复的数据起码是一致的。注意，rc只会持有行锁。

可重复读作为默认的隔离级别，再来看看这种情况。

可重复读场景

sessionA

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
MySQL [mysql]> select @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set, 1 warning (0.00 sec)

MySQL [mysql]> begin;
Query OK, 0 rows affected (0.00 sec)

MySQL [mysql]> select * from tt where c > 2 for update;  
+----+------+------+-------+------+------+
| id | c    | d    | field | f2   | f3   |
+----+------+------+-------+------+------+
|  3 |    3 |   30 |  NULL | NULL | NULL |
|  9 |    5 |   50 |  NULL | NULL | NULL |
| 20 |    4 |   90 |  NULL | NULL | NULL |
+----+------+------+-------+------+------+
3 rows in set (0.00 sec)

MySQL [mysql]> update tt set d = 0 where c > 2;  
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3  Changed: 3  Warnings: 0

sessionB

1
2
3
4
5
6
7
8
9
10
MySQL [mysql]> select @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set, 1 warning (0.00 sec)

MySQL [mysql]> insert tt select null,6,60,null,null,null;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

可重复读，由于快照的原因，普通的读是不会有幻读的。所以这里用锁定一致性视图读，即加锁的当前读。

但是这里sessionB被阻塞了，因为锁的原因：

1
2
3
4
5
6
7
8
MySQL [mysql]> select * from information_schema.innodb_locks;
+--------------+-------------+-----------+-----------+--------------+------------+------------+-----------+----------+-----------+
| lock_id      | lock_trx_id | lock_mode | lock_type | lock_table   | lock_index | lock_space | lock_page | lock_rec | lock_data |
+--------------+-------------+-----------+-----------+--------------+------------+------------+-----------+----------+-----------+
| 18687:76:3:5 | 18687       | X,GAP     | RECORD    | `mysql`.`tt` | PRIMARY    |         76 |         3 |        5 | 20        |
| 18685:76:3:5 | 18685       | X         | RECORD    | `mysql`.`tt` | PRIMARY    |         76 |         3 |        5 | 20        |
+--------------+-------------+-----------+-----------+--------------+------------+------------+-----------+----------+-----------+
2 rows in set, 1 warning (0.00 sec)

可以看到sessionA持有了X锁和gap锁。X锁属于行锁，而行锁加上间隙锁合称为next-key lock.实际应该没有实现的next-key lock,其只是对于实现的行锁+间隙锁的统称。

由于间隙锁的存在，rr级别下，可以解决幻读的问题。select不加锁的话，根据快照不会产生幻读。select加锁读，通过间隙锁解决幻读问题。

幻读对binlog的影响

如果rr下，没有gap lock机制的话，幻读对binlog也会有影响，如果binlog=statement,回想前面，rc,ru下强制要求binlog=row是有原因的。现在分析下，rr下，binlog=statement的情况：

关闭间隙锁的方式：

  • 隔离级别设置为rc
  • innodb_locks_unsafe_for_binlog=1

sessionA:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
MySQL [mysql]> show variables like 'innodb_locks_unsafe_for_binlog';
+--------------------------------+-------+
| Variable_name                  | Value |
+--------------------------------+-------+
| innodb_locks_unsafe_for_binlog | ON    |
+--------------------------------+-------+
1 row in set (0.00 sec)

MySQL [mysql]> begin;
Query OK, 0 rows affected (0.00 sec)

MySQL [mysql]> select * from tt where c > 2 for update;  
+----+------+------+-------+------+------+
| id | c    | d    | field | f2   | f3   |
+----+------+------+-------+------+------+
|  3 |    3 |   30 |  NULL | NULL | NULL |
|  9 |    5 |   50 |  NULL | NULL | NULL |
| 20 |    4 |   90 |  NULL | NULL | NULL |
+----+------+------+-------+------+------+
3 rows in set (0.00 sec)

MySQL [mysql]> update tt set d = 0 where c > 2;   
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3  Changed: 3  Warnings: 0

MySQL [mysql]> select * from tt where c > 2;
+----+------+------+-------+------+------+
| id | c    | d    | field | f2   | f3   |
+----+------+------+-------+------+------+
|  3 |    3 |    0 |  NULL | NULL | NULL |
|  9 |    5 |    0 |  NULL | NULL | NULL |
| 20 |    4 |    0 |  NULL | NULL | NULL |
| 25 |    6 |   60 |  NULL | NULL | NULL |
+----+------+------+-------+------+------+
4 rows in set (0.00 sec)

MySQL [mysql]> commit;

sessionB:

1
2
3
4
5
6
7
8
MySQL [(none)]> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MySQL [mysql]> insert tt select null,6,60,null,null,null;
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0

binlog:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
SET TIMESTAMP=1598347030/*!*/;
BEGIN
/*!*/;
# at 1708
# at 1740
#200825 17:17:10 server id 1  end_log_pos 1740 CRC32 0xe0159005         Intvar
SET INSERT_ID=25/*!*/;
#200825 17:17:10 server id 1  end_log_pos 1857 CRC32 0xc851009c         Query   thread_id=4     exec_time=0     error_code=0
SET TIMESTAMP=1598347030/*!*/;
insert tt select null,6,60,null,null,null
/*!*/;
# at 1857
#200825 17:17:10 server id 1  end_log_pos 1888 CRC32 0x9f72707d         Xid = 142
COMMIT/*!*/;
# at 1888
#200825 17:17:54 server id 1  end_log_pos 1953 CRC32 0x1b7f81f5         Anonymous_GTID  last_committed=5        sequence_number=7       rbr_only=no
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 1953
#200825 17:16:39 server id 1  end_log_pos 2034 CRC32 0xc51fd871         Query   thread_id=2     exec_time=0     error_code=0
SET TIMESTAMP=1598346999/*!*/;
BEGIN
/*!*/;
# at 2034
#200825 17:16:39 server id 1  end_log_pos 2141 CRC32 0xa64310b0         Query   thread_id=2     exec_time=0     error_code=0
SET TIMESTAMP=1598346999/*!*/;
update tt set d = 0 where c > 2

sessionA后提交的，如果根据binlog恢复数据或者从库更新：

1 insert tt select null,6,60,null,null,null

2 update tt set d = 0 where c > 2

就得不到主库的结果了：

1
2
3
4
5
6
7
8
9
MySQL [mysql]> select * from tt where c > 2;
+----+------+------+-------+------+------+
| id | c    | d    | field | f2   | f3   |
+----+------+------+-------+------+------+
|  3 |    3 |    0 |  NULL | NULL | NULL |
|  9 |    5 |    0 |  NULL | NULL | NULL |
| 20 |    4 |    0 |  NULL | NULL | NULL |
| 25 |    6 |   60 |  NULL | NULL | NULL |
+----+------+------+-------+------+------+

这是幻读在rr模式下，binlog=statement可能造成的问题，但是gap lock没让这种情况发生。

gap lock

前面碰到了间隙锁，这里再深化一下：

sessionA:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
MySQL [mysql]> select * from tt;
+----+------+------+-------+------+------+
| id | c    | d    | field | f2   | f3   |
+----+------+------+-------+------+------+
|  1 |    1 |  777 |  NULL | NULL | NULL |
|  2 |    2 |  200 |  NULL | NULL | NULL |
|  3 |    3 |   30 |  NULL | NULL | NULL |
|  9 |    5 |   50 |  NULL | NULL | NULL |
| 20 |    4 |   90 |  NULL | NULL | NULL |
+----+------+------+-------+------+------+
5 rows in set (0.00 sec)

MySQL [mysql]> begin;
Query OK, 0 rows affected (0.00 sec)

MySQL [mysql]> select * from tt where d = 90 for update;
+----+------+------+-------+------+------+
| id | c    | d    | field | f2   | f3   |
+----+------+------+-------+------+------+
| 20 |    4 |   90 |  NULL | NULL | NULL |
+----+------+------+-------+------+------+
1 row in set (0.00 sec)

select * from tt where d = 90 for update;这条语句加了2个锁，一个x锁，一个gap锁。

  • 间隙锁锁住了d=90的间隙,(50-90),(90-200).
  • x锁锁住了d=90
  • next-key lock锁的是(50-200)

所以，添加数据：

sessionB:

1
2
3
4
5
6
7
8
9
MySQL [mysql]>  insert tt select null,6,60,null,null,null;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

MySQL [mysql]>  insert tt select null,6,100,null,null,null;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

MySQL [mysql]> insert tt select null,6,45,null,null,null;
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0

间隙锁的降级

当条件语句是唯一索引，则gap锁降级为行锁，只锁定索引记录。

sessionA:

1
2
3
4
5
6
7
8
9
10
MySQL [mysql]> begin;
Query OK, 0 rows affected (0.00 sec)

MySQL [mysql]> select * from tt where id = 20 for update;
+----+------+------+-------+------+------+
| id | c    | d    | field | f2   | f3   |
+----+------+------+-------+------+------+
| 20 |    4 |   90 |  NULL | NULL | NULL |
+----+------+------+-------+------+------+
1 row in set (0.01 sec)

sessionB:

1
2
3
4
5
6
7
MySQL [mysql]>  insert tt select null,7,110,null,null,null;
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0

MySQL [mysql]>  insert tt select 21,8,92,null,null,null;
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0

gap lock导致的死锁

间隙锁和间隙锁之前是兼容的，间隙锁和insert插入间隙的操作是不兼容的，所以：

1
2
3
4
5
6
7
8
9
10
11
12
MySQL [mysql]> select * from tt;
+----+------+------+-------+------+------+
| id | c    | d    | field | f2   | f3   |
+----+------+------+-------+------+------+
|  1 |    1 |  777 |  NULL | NULL | NULL |
|  2 |    2 |  200 |  NULL | NULL | NULL |
|  3 |    3 |    0 |  NULL | NULL | NULL |
|  9 |    5 |    0 |  NULL | NULL | NULL |
| 20 |    4 |    0 |  NULL | NULL | NULL |
| 25 |    6 |   60 |  NULL | NULL | NULL |
+----+------+------+-------+------+------+
6 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
            sessionA                    sessinB

T1 begin;
select * from tt where d = 300 for update;

T2                                      begin;
                                        select * from tt where d = 300 for update;

T3 insert tt select null,7,400,null,null,null;

T4                                      insert tt select null,8,500,null,null,null;

结果：

T3 成功插入；

T4死锁；

又一个事务可以成功提交，其他死锁的报错：

ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

也还好吧。

ending

----------- ending -----------