Phantom reading and How to prevent phantom reading

RR Isolation Demo: mysql> show create TABLE rr_100\g;



CREATE TABLE ' rr_100 ' (' id ' bigint ' NOT null default ' 0 ', ' value ' varchar () 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 () not NULL DEFA ULT ' 0 ', ' value ' varchar (+) default NULL, PRIMARY KEY (' id ') engine=innodb default Charset=utf8 1 row in Set (0.00 SEC) Error:no query specified must open a transaction to enable duplicate reads if transaction is not enabled: Session a:session B:MYSQ
L> 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) No transactions are turned on at this time, no duplicate reads occur, after the transaction is opened: Session A:ses
Sion 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 ' so there is a phantom read, the table has no data, in fact, the data already exist, silly submission, only to find data conflicts.
Mysql> select * from rr_100;
Empty Set (0.00 sec) mysql> Delete from rr_100; QuerY OK, 1 Row Affected (0.00 sec) View no records, delete does have data so, InnoDB points out that can avoid the illusion of what is going on. By default, InnoDB operates into repeatable READ transaction isolation level and with the Innodb_locks_unsafe_for_binlog sy 
Stem variable disabled.  In the, InnoDB uses next-key locks for searches and index scans, which prevents, phantom rows (the


"Avoiding the Phantom Problem Using next-key locking"). By default, the InnoDB operation at repeatable READ transaction isolation level, Innodb_locks_unsafe_for_binlog system variable is disabled in this case, InnoDB uses next-key locks for

Search and index scans provide a protection against phantom rows.


To prevent Phantom Reading, InnoDB uses an algorithm called Next-key locking combination Index-row locking and gap locking.


To prevent Phantom Reading, InnoDB uses an algorithm called Next-key locking combining Index-row locking and interval locks you can use Next-key locking to implement a single check in your application. You can use Next-key locking to implement a unique check in your application: If you read your data in share mode and don't like to see duplicates for a record you want to insert, then you can safely insert your records, Zhi to Next-key lock settings Succeeded in reading any previous one because, Next-key locking lets you "lock" the nonexistent record in your table for locking reads (SELECT and for UPDATE or lock in SHARE MOD E), UPDATE, and DELETE STATements, locking depends on whether the statement uses a unique index with a unique search condition, or a Range-typ E 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 blocks insertions by the sessions into the gaps covered by the range.

For lock reads (SELECT with for UPDATE or lock in SHARE MODE), UPDATE, and DELETE statement locks depend on whether the statement uses a unique index unique search condition, or a search condition of a range type.


For a unique index with a unique search condition, InnoDB only locks the index record, not its previous interval. 
For other search conditions, InnoDB locks The index rang scanned, uses gap locks or next-key (gap plus Index-record) locks to plug other sessions into the coverage interval consistency read and submit read, first look at the experiment, experiment Four:
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 rowIn Set (0.00 sec) mysql> start transaction; Query OK, 0 rows Affected (0.00 sec) Mysql&gt ;
                                                  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 * 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 secIf you use normal reading, you will get consistent results, and if you use lock read, you will read the results of the latest submit read. itself, repeatable reading and submission reading are contradictory.

In the same transaction, if repeatable reading is ensured, the submission of other transactions is not seen and the submission is violated, and if the submission is guaranteed, the results are inconsistent with the two readings, which violates the repeatable reading. If you want to the ' freshest ' state of the ' database, you should use either ' READ committed isolation level or a loc


King Read:select * from T_bitfly LOCK in SHARE MODE;
If you need to see the latest fresh status of the data, you can use the Read Committed isolation level or a lock read: mysql> select * from rr_100 lock in SHARE MODE; +----+-------+
| ID |
Value |  +----+-------+
| 1 |  A | | 2 |
B | +----+-------+ conclusion: The repeatable reading of MySQL InnoDB does not guarantee the avoidance of phantom reading, which requires the use of lock reading to ensure.





And this lock degree is used to the mechanism is Next-key locks.
  Next-key locks (Gap plus index-record) test: CREATE TABLE ' sms_rr ' (' sn ' int (one) not NULL auto_increment ' self-numbering ', ' Phoneno ' int ' not NULL, ' varchar ' is not NULL, ' Channeltype ' int (one) DEFAULT NULL COMMENT ' channel recognition ', ' Status ' tinyint (4) not NULL, PRIMARY KEY (' sn ') Engine=innodb auto_increment=1 1 row in Set (0.00 sec) mysql> S How CREATE TABLE Sms_rr\G 1. Row *************************** table:sms_rr Create table:create Table ' sms_rr ' (' sn ' int (one) not NULL auto_in Crement COMMENT ' self numbering ', ' Phoneno ' int () not NULL, ' message ' varchar (?) not NULL, ' Channeltype ' int (one) DEFAULT N  ull COMMENT ' channel recognition ', ' 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 (one) not NULL auto_in Crement COMMENT ' self numbering ', ' Phoneno ' int () not NULL, ' message ' varchar (?) not NULL, ' Channeltype ' int (one) DEFAULT N ull COMMENT ' channel recognition ', ' 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 | +----+---------+---------+-------------+--------+ 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> inserts into SMS_RR (
Phoneno,message,channeltype,status) VALUES (' cc ', 1, 1); ERROR 1205 (HY000): Lock wait timeout exceeded;
Try restarting transaction mysql> INSERT INTO SMS_RR (phoneno,message,channeltype,status) VALUES (, ' cc ', 1, 1); 
Query OK, 1 row Affected (0.00 sec) mysql> INSERT into SMS_RR (phoneno,message,channeltype,status) VALUES (, ' cc ', 1, 1); ERROR 1205 (HY000): Lock wait timeout exceeded;
Try restarting transaction mysql> INSERT INTO SMS_RR (phoneno,message,channeltype,status) VALUES (' cc ', 1, 1); ERROR 1205 (HY000): Lock wait timeout exceeded;
Try restarting transaction mysql> INSERT INTO SMS_RR (phoneno,message,channeltype,status) VALUES (n, ' cc ', 1, 1); ERROR 1205 (HY000): Lock wait timeout exceeded;
Try restarting transaction mysql> INSERT INTO SMS_RR (phoneno,message,channeltype,status) VALUES ("CC", 1, 1); ERROR 1205 (HY000): LockWait timeout exceeded;
Try restarting transaction mysql> INSERT INTO SMS_RR (phoneno,message,channeltype,status) VALUES (, ' cc ', 1, 1); ERROR 1205 (HY000): Lock wait timeout exceeded;
Try restarting transaction mysql> INSERT INTO SMS_RR (phoneno,message,channeltype,status) VALUES (num, ' cc ', 1, 1); ERROR 1205 (HY000): Lock wait timeout exceeded;
Try restarting transaction mysql> INSERT INTO SMS_RR (phoneno,message,channeltype,status) VALUES (, ' cc ', 1, 1); ERROR 1205 (HY000): Lock wait timeout exceeded;
Try restarting transaction mysql> INSERT INTO SMS_RR (phoneno,message,channeltype,status) VALUES ("CC", 1, 1); ERROR 1205 (HY000): Lock wait timeout exceeded;
Try restarting transaction mysql> INSERT INTO SMS_RR (phoneno,message,channeltype,status) VALUES ("CC", 1, 1); ERROR 1205 (HY000): Lock wait timeout exceeded;
Try restarting transaction mysql> INSERT INTO SMS_RR (phoneno,message,channeltype,status) VALUES (' cc ', 1, 1); ERROR 1205 (HY000): Lock wait timeout exceeded; Try Restarting transaction mysql> INSERT into SMS_RR (phoneno,message,channeltype,status) VALUES (, ' cc ', 1, 1); ERROR 1205 (HY000): Lock wait timeout exceeded;
Try restarting transaction mysql> INSERT INTO SMS_RR (Phoneno,message,channeltype,status) VALUES (+, ' cc ', 1, 1); Query OK, 1 row Affected (0.00 sec) locked range for "20,31" Next-key locks (Gap plus index-record) test 2:session 1:database C
Hanged 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 =% for update; +----+---------+---------+-------------+--------+
| sn | Phoneno | message | Channeltype |
Status | +----+---------+---------+-------------+--------+
|      23 | 23 |           TTT |      2 |
3 | +----+---------+---------+-------------+--------+ 1 row in Set (0.00 sec) Sesssion 2:mysql> inserts into SMS_RR(phoneno,message,channeltype,status) VALUES ("CC", 1, 1); 
Query OK, 1 row affected (0.01 sec) mysql> insert into SMS_RR (phoneno,message,channeltype,status) VALUES (, ' cc ', 1, 1); ERROR 1205 (HY000): Lock wait timeout exceeded;
Try restarting transaction mysql> INSERT INTO SMS_RR (phoneno,message,channeltype,status) VALUES (, ' cc ', 1, 1); ERROR 1205 (HY000): Lock wait timeout exceeded;
Try restarting transaction mysql> INSERT INTO SMS_RR (phoneno,message,channeltype,status) VALUES (' cc ', 1, 1); ERROR 1205 (HY000): Lock wait timeout exceeded;
Try restarting transaction mysql> INSERT INTO SMS_RR (phoneno,message,channeltype,status) VALUES (n, ' cc ', 1, 1); ERROR 1205 (HY000): Lock wait timeout exceeded;
Try restarting transaction mysql> INSERT INTO SMS_RR (phoneno,message,channeltype,status) VALUES ("CC", 1, 1); ERROR 1205 (HY000): Lock wait timeout exceeded;
Try restarting transaction mysql> INSERT INTO SMS_RR (phoneno,message,channeltype,status) VALUES (, ' cc ', 1, 1); ERROR 1205 (HY: Lock wait timeout exceeded;
Try restarting transaction mysql> INSERT INTO SMS_RR (phoneno,message,channeltype,status) VALUES (num, ' cc ', 1, 1); ERROR 1205 (HY000): Lock wait timeout exceeded;
Try restarting transaction mysql> INSERT INTO SMS_RR (phoneno,message,channeltype,status) VALUES (, ' cc ', 1, 1); ERROR 1205 (HY000): Lock wait timeout exceeded;
Try restarting transaction mysql> INSERT INTO SMS_RR (phoneno,message,channeltype,status) VALUES ("CC", 1, 1); ERROR 1205 (HY000): Lock wait timeout exceeded;
Try restarting transaction mysql> INSERT INTO SMS_RR (phoneno,message,channeltype,status) VALUES ("CC", 1, 1); ERROR 1205 (HY000): Lock wait timeout exceeded;
Try restarting transaction mysql> INSERT INTO SMS_RR (phoneno,message,channeltype,status) VALUES (' cc ', 1, 1); ERROR 1205 (HY000): Lock wait timeout exceeded;
Try restarting transaction mysql> INSERT INTO SMS_RR (phoneno,message,channeltype,status) VALUES (, ' cc ', 1, 1); ERROR 1205 (HY000): Lock wait timeout exceeded;Try restarting transaction mysql> INSERT INTO SMS_RR (Phoneno,message,channeltype,status) VALUES (+, ' cc ', 1, 1);




 Query OK, 1 row affected (0.00 sec) Locks the "20 31" interval