RR 隔离演示:
mysql> show create table rr_100\G;
CREATE TABLE `rr_100` (
`id` bigint(20) NOT NULL default '0',
`value` varchar(32) default NULL,
PRIMARY KEY (`id`));
mysql> select @@global.tx_isolation, @@tx_isolation;
+-----------------------+-----------------+
| @@global.tx_isolation | @@tx_isolation |
+-----------------------+-----------------+
| REPEATABLE-READ | REPEATABLE-READ |
+-----------------------+-----------------+
1 row in set (0.00 sec)
mysql> show create table rr_100\G;
*************************** 1. row ***************************
Table: rr_100
Create Table: CREATE TABLE `rr_100` (
`id` bigint(20) NOT NULL DEFAULT '0',
`value` varchar(32) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
ERROR:
No query specified
必须开启事务,才能实现重复读,如果不开启事务:
Session A: Session B:
mysql> select * from rr_100;
Empty set (0.00 sec)
mysql> insert into rr_100 values(1,'a');
Query OK, 1 row affected (0.01 sec)
mysql> select * from rr_100;
+----+-------+
| id | value |
+----+-------+
| 1 | a |
+----+-------+
1 row in set (0.00 sec)
此时 没有开启事务,不会出现重复读,开启事务后:
Session A: Session B:
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from rr_100;
Empty set (0.00 sec)
mysql> insert into rr_100 values(1,'a');
Query OK, 1 row affected (0.00 sec)
Session A:
mysql> select * from rr_100;
Empty set (0.00 sec)
mysql> insert into rr_100 values(1,'a');
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
如此就出现了幻读，以为表里没有数据，其实数据已经存在了，傻乎乎的提交后，才发现数据冲突了。
mysql> select * from rr_100;
Empty set (0.00 sec)
mysql> delete from rr_100 ;
Query OK, 1 row affected (0.00 sec)
查看没有记录,删除确有数据
那么，InnoDB指出的可以避免幻读是怎么回事呢？
By default, InnoDB operates in REPEATABLE READ transaction isolation level
and with the innodb_locks_unsafe_for_binlog system variable disabled.
In this case, InnoDB uses next-key locks for searches and index scans, which prevents phantom rows
(see Section 13.6.8.5, “Avoiding the Phantom Problem Using Next-Key Locking”).
默认,InnoDB 操作在 REPEATABLE READ 事务隔离级别,
innodb_locks_unsafe_for_binlog 系统变量是disabled的
在这种情况下,InnoDB 使用 next-key locks对于搜索和索引扫描,提供了防止幻影行。
为了防止幻读,InnoDB 使用一个算法称为 next-key locking 组合 index-row locking 和gap locking.
为了防止幻读,InnoDB 使用一算法称为next-key locking 结合 index-row locking 和区间锁
你可以使用 next-key locking来实现 一个唯一检查在你的应用里。
你可以使用 next-key locking 来实现一个唯一的检查在你的应用里:
如果你读取你的数据在share mode和不像看到重复的对于一个记录你想要插入,
然后你可以安全的插入你的记录,ZHi到 next-key lock 设置成功在你读取先前的任何一个
因为,next-key locking 让你"lock" 不存在的记录在你的表里
For locking reads (SELECT with FOR UPDATE or LOCK IN SHARE MODE),UPDATE, and DELETE statements,
locking depends on whether the statement uses a unique index with a unique search condition,
or a range-type search condition. For a unique index with a unique search condition, InnoDB locks only the index record found,
not the gap before it. For other search conditions, InnoDB locks the index range scanned,
using gap locks or next-key (gap plus index-record) locks to block insertions by other sessions into the gaps covered by the range.
对于锁定读((SELECT with FOR UPDATE or LOCK IN SHARE MODE),UPDATE,和DELETE 语句
锁依赖于是否语句使用一个唯一索引 唯一搜索条件,
或者一个范围类型的搜索条件。
对于一个唯一的索引进行唯一搜索条件,InnoDB 只锁定index record记录，不是它之前的区间。
对于其他的搜索条件,InnoDB 锁定index rang scanned,使用gap locks 或者next-key(gap 加上index-record)
锁来堵塞其他会话插入到范围覆盖的区间
一致性读和提交读，先看实验，实验四：
Sesssion 1: Session 2:
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from rr_100;
+----+-------+
| id | value |
+----+-------+
| 1 | a |
+----+-------+
1 row in set (0.00 sec)
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from rr_100;
+----+-------+
| id | value |
+----+-------+
| 1 | a |
+----+-------+
1 row in set (0.00 sec)
mysql> insert into rr_100 values(2,'b');
Query OK, 1 row affected (0.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.01 sec)
mysql> select * from rr_100;
+----+-------+
| id | value |
+----+-------+
| 1 | a |
| 2 | b |
+----+-------+
2 rows in set (0.00 sec)
mysql> select * from rr_100 ;
+----+-------+
| id | value |
+----+-------+
| 1 | a |
+----+-------+
1 row in set (0.00 sec)
mysql> select * from rr_100 for update;
+----+-------+
| id | value |
+----+-------+
| 1 | a |
| 2 | b |
+----+-------+
mysql> select * from rr_100 LOCK IN SHARE MODE;
+----+-------+
| id | value |
+----+-------+
| 1 | a |
| 2 | b |
+----+-------+
2 rows in set (0.00 sec)
2 rows in set (0.00 sec)
如果使用普通的读，会得到一致性的结果，如果使用了加锁的读，就会读到“最新的”“提交”读的结果。
本身，可重复读和提交读是矛盾的。在同一个事务里，如果保证了可重复读，就会看不到其他事务的提交，违背了提交读；如果保证了提交读，就会导致前后两次读到的结果不一致，违背了可重复读。
If you want to see the “freshest” state of the database, you should use either the READ COMMITTED isolation level or a locking read:
SELECT * FROM t_bitfly LOCK IN SHARE MODE;
如果你需要看到最新鲜状态的数据,你可以使用 READ COMMITTED isolation level
或者一个锁定读:
mysql> select * from rr_100 LOCK IN SHARE MODE;
+----+-------+
| id | value |
+----+-------+
| 1 | a |
| 2 | b |
+----+-------+
结论：MySQL InnoDB的可重复读并不保证避免幻读，需要应用使用加锁读来保证。而这个加锁度使用到的机制就是next-key locks。
next-key locks(gap 加上index-record)测试:
CREATE TABLE `Sms_rr` (
`sn` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增编号',
`phoneNo` int(16) NOT NULL ,
`message` varchar(250) NOT NULL ,
`channelType` int(11) DEFAULT NULL COMMENT '通道识别',
`status` tinyint(4) NOT NULL,
PRIMARY KEY (`sn`)
) ENGINE=InnoDB AUTO_INCREMENT=1
1 row in set (0.00 sec)
mysql> show create table Sms_rr\G;
*************************** 1. row ***************************
Table: Sms_rr
Create Table: CREATE TABLE `Sms_rr` (
`sn` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增编号',
`phoneNo` int(16) NOT NULL,
`message` varchar(250) NOT NULL,
`channelType` int(11) DEFAULT NULL COMMENT '通道识别',
`status` tinyint(4) NOT NULL,
PRIMARY KEY (`sn`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
mysql> create index Sms_rr_idx1 on Sms_rr(phoneNo);
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show create table Sms_rr\G;
*************************** 1. row ***************************
Table: Sms_rr
Create Table: CREATE TABLE `Sms_rr` (
`sn` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增编号',
`phoneNo` int(16) NOT NULL,
`message` varchar(250) NOT NULL,
`channelType` int(11) DEFAULT NULL COMMENT '通道识别',
`status` tinyint(4) NOT NULL,
PRIMARY KEY (`sn`),
KEY `Sms_rr_idx1` (`phoneNo`)
) ENGINE=InnoDB AUTO_INCREMENT=21 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
mysql> select * from Sms_rr;
+----+---------+---------+-------------+--------+
| sn | phoneNo | message | channelType | status |
+----+---------+---------+-------------+--------+
| 1 | 1 | xxxxxxx | 2 | 1 |
| 2 | 2 | xxxxxxx | 2 | 1 |
| 3 | 3 | xxxxxxx | 2 | 1 |
| 4 | 4 | xxxxxxx | 2 | 1 |
| 5 | 5 | xxxxxxx | 2 | 1 |
| 6 | 6 | xxxxxxx | 2 | 1 |
| 7 | 7 | xxxxxxx | 2 | 1 |
| 8 | 8 | xxxxxxx | 2 | 1 |
| 9 | 9 | xxxxxxx | 2 | 1 |
| 10 | 10 | xxxxxxx | 1 | 1 |
| 11 | 11 | xxxxxxx | 2 | 1 |
| 12 | 12 | xxxxxxx | 2 | 1 |
| 13 | 13 | xxxxxxx | 2 | 1 |
| 14 | 14 | xxxxxxx | 2 | 1 |
| 15 | 15 | xxxxxxx | 2 | 1 |
| 16 | 16 | xxxxxxx | 2 | 1 |
| 17 | 17 | xxxxxxx | 2 | 1 |
| 18 | 18 | xxxxxxx | 1 | 1 |
| 19 | 19 | xxxxxxx | 1 | 1 |
| 20 | 20 | xxxxxxx | 2 | 1 |
| 23 | 23 | ttt | 2 | 3 |
| 32 | 32 | xxxxxxx | 2 | 1 |
| 33 | 33 | xxxxxxx | 2 | 1 |
| 34 | 34 | xxxxxxx | 2 | 1 |
| 35 | 35 | xxxxxxx | 2 | 1 |
| 36 | 36 | xxxxxxx | 2 | 1 |
| 37 | 37 | xxxxxxx | 2 | 1 |
| 38 | 38 | xxxxxxx | 2 | 1 |
| 39 | 39 | xxxxxxx | 2 | 1 |
| 40 | 40 | xxxxxxx | 2 | 1 |
+----+---------+---------+-------------+--------+
30 rows in set (0.00 sec)
Database changed
mysql> explain select * from Sms_rr where phoneNo >20 and phoneNo<30;
+----+-------------+--------+-------+---------------+-------------+---------+------+------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+-------+---------------+-------------+---------+------+------+-----------------------+
| 1 | SIMPLE | Sms_rr | range | Sms_rr_idx1 | Sms_rr_idx1 | 4 | NULL | 1 | Using index condition |
+----+-------------+--------+-------+---------------+-------------+---------+------+------+-----------------------+
1 row in set (0.00 sec)
Session 1:
mysql> select connection_id();
+-----------------+
| connection_id() |
+-----------------+
| 1 |
+-----------------+
1 row in set (0.00 sec)
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from Sms_rr where phoneNo >20 and phoneNo<30 for update;
+----+---------+---------+-------------+--------+
| sn | phoneNo | message | channelType | status |
+----+---------+---------+-------------+--------+
| 23 | 23 | ttt | 2 | 3 |
+----+---------+---------+-------------+--------+
1 row in set (0.00 sec)
Session 2:
mysql> insert into Sms_rr(phoneNo,message,channelType,status) values(20,'cc',1,1);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> insert into Sms_rr(phoneNo,message,channelType,status) values(19,'cc',1,1);
Query OK, 1 row affected (0.00 sec)
mysql> insert into Sms_rr(phoneNo,message,channelType,status) values(21,'cc',1,1);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> insert into Sms_rr(phoneNo,message,channelType,status) values(22,'cc',1,1);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> insert into Sms_rr(phoneNo,message,channelType,status) values(23,'cc',1,1);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> insert into Sms_rr(phoneNo,message,channelType,status) values(24,'cc',1,1);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> insert into Sms_rr(phoneNo,message,channelType,status) values(25,'cc',1,1);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> insert into Sms_rr(phoneNo,message,channelType,status) values(26,'cc',1,1);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> insert into Sms_rr(phoneNo,message,channelType,status) values(27,'cc',1,1);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> insert into Sms_rr(phoneNo,message,channelType,status) values(28,'cc',1,1);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> insert into Sms_rr(phoneNo,message,channelType,status) values(29,'cc',1,1);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> insert into Sms_rr(phoneNo,message,channelType,status) values(30,'cc',1,1);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> insert into Sms_rr(phoneNo,message,channelType,status) values(31,'cc',1,1);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> insert into Sms_rr(phoneNo,message,channelType,status) values(32,'cc',1,1);
Query OK, 1 row affected (0.00 sec)
锁住的区间为【20,31】
next-key locks(gap 加上index-record)测试2:
Session 1:
Database changed
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> select connection_id();
+-----------------+
| connection_id() |
+-----------------+
| 2 |
+-----------------+
1 row in set (0.00 sec)
mysql> select * from Sms_rr where phoneNo = 23 for update;
+----+---------+---------+-------------+--------+
| sn | phoneNo | message | channelType | status |
+----+---------+---------+-------------+--------+
| 23 | 23 | ttt | 2 | 3 |
+----+---------+---------+-------------+--------+
1 row in set (0.00 sec)
Sesssion 2:
mysql> insert into Sms_rr(phoneNo,message,channelType,status) values(19,'cc',1,1);
Query OK, 1 row affected (0.01 sec)
mysql> insert into Sms_rr(phoneNo,message,channelType,status) values(20,'cc',1,1);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> insert into Sms_rr(phoneNo,message,channelType,status) values(21,'cc',1,1);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> insert into Sms_rr(phoneNo,message,channelType,status) values(22,'cc',1,1);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> insert into Sms_rr(phoneNo,message,channelType,status) values(23,'cc',1,1);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> insert into Sms_rr(phoneNo,message,channelType,status) values(24,'cc',1,1);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> insert into Sms_rr(phoneNo,message,channelType,status) values(25,'cc',1,1);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> insert into Sms_rr(phoneNo,message,channelType,status) values(26,'cc',1,1);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> insert into Sms_rr(phoneNo,message,channelType,status) values(27,'cc',1,1);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> insert into Sms_rr(phoneNo,message,channelType,status) values(28,'cc',1,1);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> insert into Sms_rr(phoneNo,message,channelType,status) values(29,'cc',1,1);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> insert into Sms_rr(phoneNo,message,channelType,status) values(30,'cc',1,1);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> insert into Sms_rr(phoneNo,message,channelType,status) values(31,'cc',1,1);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> insert into Sms_rr(phoneNo,message,channelType,status) values(32,'cc',1,1);
Query OK, 1 row affected (0.00 sec)
锁了【20 31】这个区间
幻读和如何防止幻读
程序员文章站
2022-06-14 16:40:17
...
上一篇： Mysql(Innodb)如何避免幻读
下一篇： 批量修改文件名