Session 1:
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from SmsTest where phoneNo between 30 and 40 for update;
+----+---------+-------------+--------+
| sn | phoneNo | channelType | status |
+----+---------+-------------+--------+
| 30 | 30 | 2 | 1 |
| 31 | 31 | 2 | 1 |
| 32 | 32 | 2 | 1 |
| 33 | 33 | 2 | 1 |
| 34 | 34 | 2 | 1 |
| 35 | 35 | 2 | 1 |
| 36 | 36 | 2 | 1 |
| 37 | 37 | 2 | 1 |
| 38 | 38 | 2 | 1 |
| 39 | 39 | 2 | 1 |
| 40 | 40 | 2 | 1 |
+----+---------+-------------+--------+
11 rows in set (0.00 sec)
mysql> explain select * from SmsTest where phoneNo between 30 and 40 for update;
+----+-------------+---------+-------+---------------+--------------+---------+------+------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+-------+---------------+--------------+---------+------+------+-----------------------+
| 1 | SIMPLE | SmsTest | range | SmsTest_idx1 | SmsTest_idx1 | 4 | NULL | 11 | Using index condition |
+----+-------------+---------+-------+---------------+--------------+---------+------+------+-----------------------+
1 row in set (0.00 sec)
Session 2:
mysql> insert into zjzc.SmsTest(PhoneNo,channelType,status) values(28,1,1);
Query OK, 1 row affected (0.00 sec)
mysql> insert into zjzc.SmsTest(PhoneNo,channelType,status) values(29,1,1); --hang
mysql> insert into zjzc.SmsTest(PhoneNo,channelType,status) values(30,1,1); --hang
mysql> insert into zjzc.SmsTest(PhoneNo,channelType,status) values(31,1,1); --hang
mysql> insert into zjzc.SmsTest(PhoneNo,channelType,status) values(32,1,1); --hang
mysql> insert into zjzc.SmsTest(PhoneNo,channelType,status) values(33,1,1); --hang
mysql> insert into zjzc.SmsTest(PhoneNo,channelType,status) values(34,1,1);--hang
mysql> insert into zjzc.SmsTest(PhoneNo,channelType,status) values(40,1,1); --hang
mysql> insert into zjzc.SmsTest(PhoneNo,channelType,status) values(41,1,1);
Query OK, 1 row affected (0.01 sec)
mysql> insert into zjzc.SmsTest(PhoneNo,channelType,status) values(42,1,1);
Query OK, 1 row affected (0.04 sec)
在30-40这个区间无法插入,RR需要区间锁来防止幻读
修改事务隔离为transaction-isolation =READ-COMMITTED
mysql> select @@tx_isolation;
+----------------+
| @@tx_isolation |
+----------------+
| READ-COMMITTED |
+----------------+
1 row in set (0.00 sec)
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from SmsTest where phoneNo between 30 and 40 for update;
+----+---------+-------------+--------+
| sn | phoneNo | channelType | status |
+----+---------+-------------+--------+
| 30 | 30 | 2 | 1 |
| 31 | 31 | 2 | 1 |
| 32 | 32 | 2 | 1 |
| 33 | 33 | 2 | 1 |
| 34 | 34 | 2 | 1 |
| 35 | 35 | 2 | 1 |
| 36 | 36 | 2 | 1 |
| 37 | 37 | 2 | 1 |
| 38 | 38 | 2 | 1 |
| 39 | 39 | 2 | 1 |
| 40 | 40 | 2 | 1 |
+----+---------+-------------+--------+
11 rows in set (0.01 sec)
mysql> explain select * from SmsTest where phoneNo between 30 and 40 for update;
+----+-------------+---------+-------+---------------+--------------+---------+------+------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+-------+---------------+--------------+---------+------+------+-----------------------+
| 1 | SIMPLE | SmsTest | range | SmsTest_idx1 | SmsTest_idx1 | 4 | NULL | 11 | Using index condition |
+----+-------------+---------+-------+---------------+--------------+---------+------+------+-----------------------+
1 row in set (0.01 sec)
Session2:
Database changed
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into zjzc.SmsTest(PhoneNo,channelType,status) values(28,1,1);
Query OK, 1 row affected (0.00 sec)
mysql> insert into zjzc.SmsTest(PhoneNo,channelType,status) values(29,1,1);
Query OK, 1 row affected (0.00 sec)
mysql> insert into zjzc.SmsTest(PhoneNo,channelType,status) values(30,1,1);
Query OK, 1 row affected (0.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into zjzc.SmsTest(PhoneNo,channelType,status) values(31,1,1);
Query OK, 1 row affected (0.00 sec)
Session 1:
mysql> select * from SmsTest where phoneNo between 30 and 40 for update;
+-------+---------+-------------+--------+
| sn | phoneNo | channelType | status |
+-------+---------+-------------+--------+
| 30 | 30 | 2 | 1 |
| 45292 | 30 | 1 | 1 |
| 31 | 31 | 2 | 1 |
| 32 | 32 | 2 | 1 |
| 33 | 33 | 2 | 1 |
| 34 | 34 | 2 | 1 |
| 35 | 35 | 2 | 1 |
| 36 | 36 | 2 | 1 |
| 37 | 37 | 2 | 1 |
| 38 | 38 | 2 | 1 |
| 39 | 39 | 2 | 1 |
| 40 | 40 | 2 | 1 |
+-------+---------+-------------+--------+
12 rows in set (0.00 sec)
mysql> select * from SmsTest where phoneNo between 30 and 40 for update;
+-------+---------+-------------+--------+
| sn | phoneNo | channelType | status |
+-------+---------+-------------+--------+
| 30 | 30 | 2 | 1 |
| 45292 | 30 | 1 | 1 |
| 31 | 31 | 2 | 1 |
| 45293 | 31 | 1 | 1 |
| 32 | 32 | 2 | 1 |
| 33 | 33 | 2 | 1 |
| 34 | 34 | 2 | 1 |
| 35 | 35 | 2 | 1 |
| 36 | 36 | 2 | 1 |
| 37 | 37 | 2 | 1 |
| 38 | 38 | 2 | 1 |
| 39 | 39 | 2 | 1 |
| 40 | 40 | 2 | 1 |
+-------+---------+-------------+--------+
13 rows in set (0.01 sec)
结论 RR模式下 需要区间锁来防止幻读,
RC模式下,没有区间锁,会出现幻读