Last week on duty, encountering such a customer problem, the table structure is simplified as follows: CREATE table ' AA ' (
' C1 ' int (ten) unsigned not NULL auto_increment,
' C2 ' int (one) DEFAULT NULL,
' C3 ' Int (one) DEFAULT ' 0 ',
' C4 ' int (one) DEFAULT NULL,
PRIMARY KEY (' C1 ')
Engine=innodb DEFAULT Charset=utf8 When using the following syntax, how can I insert two records of C2 = 1007 and C3 = 1? Insert into a AA (C2,C4) Select 1007, 8 from dual where NOT EXISTS (SELECT * from AA where C2 = 1007 and C3 = 1);
There is no doubt that the user's binlog is first detected, and it is true that there are two insert statements after the previous one.
Will that be the MySQL bug? Even a bug has to be reproduced first.
How to reproduce it?
Session 1:
Begin work;
Insert into a AA (C2,C4) Select 1007, 8 from dual where NOT EXISTS (SELECT * from AA where C2 = 1007 and C3 = 1);
This is not a commit.
Session 2:
Begin work;
Insert into a AA (C2,C4) Select 1002, 5 from dual where NOT EXISTS (SELECT * from AA where C2 = 1002 and C3 = 0);
Commit
Session1:
Commit
OK, a little bit of a prospect. In this case, it can be reproduced stably.
But the customer to the present, but still cannot reproduce. The thought might be that the isolation level is different.
Sure enough, I was in Read Committed's scene, the client was reproduced in repeatable read scene.
So why do the two isolation levels have different effects?
The root cause is that the RC isolation level guarantees a locking (record lock) of the records read to;
The RR isolation level guarantees a locking (record lock) on the read record, while guaranteeing a lock on the read range, and a new record that satisfies the query condition cannot be inserted (GAP Lock), and there is no phantom read phenomenon.
The purpose of the gap lock is to prevent two times of the same transaction from being read at present, and the illusion of reading occurs.
Some basic points of knowledge:
Two-stage lock
The database follows the two-phase lock protocol, dividing the transaction into two stages, locking the stage and unlocking the stage (so called two-segment lock)
- Lock phase: The lock operation can be performed at this stage. Before reading any data, request and obtain S lock (shared lock, other transactions can continue to share the lock, but not the lock), before the write operation to request and obtain an X lock (exclusive lock, other transactions can no longer acquire any locks). The lock does not succeed, the transaction goes into a wait state until locking succeeds to continue execution.
- Unlocking phase: When a transaction releases a block, the transaction enters the unlock phase, and the lock operation is no longer allowed at this stage.
| Transactions |
locking/unlock handling |
| Begin |
|
| INSERT into Test ..... |
Add insert corresponding to the lock |
| Update Test Set ... |
Add update corresponding to the lock |
| Delete from Test .... |
Add delete corresponding to the lock |
| Commit |
When a transaction commits, the lock corresponding to the INSERT, update, and delete are released simultaneously |
While this approach does not prevent deadlocks, the two-phase lock protocol ensures that concurrent scheduling of transactions is serializable (serialization is important, especially in data recovery and backup).
Isolation level of a transaction
| Isolation level |
dirty Read (Dirty Read) |
non-repeatable read (nonrepeatable read) |
Phantom Read (Phanto M read) |
| Uncommitted read (READ UNCOMMITTED) |
possible |
possible |
may /td> |
| committed read (Read committed) |
not possible |
possible |
possible |
| available Repeat read (repeatable read) |
not possible |
not possible |
possible |
| serializable (Serializable) |
not possible |
not possible |
not possible |
- Uncommitted read (READ UNCOMMITTED): Allows dirty reads, which may read data that has not been committed in other sessions for transaction modification
- Read Committed: Read only the data that has been committed. For the current read, theRC isolation level guarantees a read-to-record locking (record Lock), which has a phantom-read phenomenon.
- Repeatable read (repeated read): Repeatable read. Queries within the same transaction are all consistent at the beginning of the transaction, InnoDB the default level. For the current read, theRR isolation level guarantees that the read locking (record Lock), while ensuring that the read range is locked, the new record satisfies the query condition can not be inserted (GAP Lock), there is no phantom read phenomenon.
- Serial Read (Serializable): Fully serialized read, each read need to obtain a table-level shared lock, read and write each other will block. From MVCC concurrency control is degraded to lock-based concurrency control. Do not distinguish between the snapshot read and the current read, all the read operation is the current read, read the read-write lock (S lock), write lock (x lock). Serializable isolation level, read and write conflicts, so the degree of concurrency drops sharply, not recommended under MYSQL/INNODB.
Read uncommitted This level, the database is generally not used, and any operation is not locked, this is not discussed here.
Snapshot read vs current Read
In MVCC concurrency control, read operations can be divided into two categories: snapshot read (snapshot read) and current read. Snapshot reads, read the visible version of the record (possibly a historical version), without locking. The current read, read is the latest version of the record, and the current read returned records, will be added to the lock, to ensure that other transactions will no longer concurrently modify this record.
In a system that supports MVCC concurrency control, which read operations are snapshot reads? Which operations are currently read? Take MySQL InnoDB as an example:
- Snapshot read: a simple select operation, which belongs to the snapshot read, without locking.
- SELECT * from table where?;
- Current read: Special read operation, insert/update/delete operation, belongs to the current read, need to lock.
- SELECT * FROM table where? lock in Share mode;
- SELECT * FROM table where? for update;
- Insert into table values (...);
- Update table set? Where?;
- Delete from table where?;
All of the above statements belong to the current read, reading the latest version of the record. Also, after reading, it is necessary to ensure that other concurrent transactions cannot modify the current record and lock the read record. In addition to the first statement, the read record plus S lock (shared lock), the other operation, plus X lock (exclusive lock).
What are the different isolation levels, and how are they locked for different indexing situations?
Delete from t1 where id = 10;
- combination one:ID column is primary key, RC Isolation level: Only the record with ID = 10 on the primary key is added to the X lock.
- combination two:ID column is a level two unique index, RC isolation level: Need to add two x lock, one corresponding to the ID unique index on the id = 10 of the record, the other lock corresponds to the cluster index on the [name= ' d ', id=10] records.
- combination three:ID column is a level two non-unique index, RC isolation level: All records corresponding to the SQL query criteria will be locked. At the same time, the records on the primary key index are also locked.
- combination four:there is no index on the ID column, RC isolation level:SQL will be filtered by a full scan of the clustered index, as filtering is done at the MySQL server level. Therefore, each record, whether or not it satisfies the condition, is added with an X lock. However, for the sake of efficiency, MySQL is optimized, for the record that does not satisfy the condition, will put the lock after judgment, finally hold the lock on the record that satisfies the condition, but the locking/release lock action on the record that does not satisfy the condition will not be omitted.
- All records on the clustered index are added with an X lock. The x lock is all added regardless of whether the record satisfies the condition. What is the difference between the effect of this lock and the table lock? RC isolation level, there are differences, records can still be inserted. RR, there is no difference in functionality. However, InnoDB does not actively upgrade table locks.
- Why not just lock on the record that satisfies the condition? This is due to the implementation of MySQL. If a condition cannot be quickly filtered through the index, then the storage engine plane (INNODB) will return all records after locking and then be filtered by the MySQL server layer. So they locked up all the records.
- The index Condition Pushdown is supported after 5.6 and can be filtered at the InnoDB layer.
- Combination five:ID column is primary key, RR isolation level: locking is consistent with combining one [ID primary key, Read Committed].
- Combination SIX:the ID column is a two-level unique index, RR isolation level: identical to the combination of two [ID unique index, Read Committed].
- combination Seven:the ID column is a level two non-unique index, RR isolation level: First, through the ID index to the first to satisfy the query criteria record, add the record x lock, add gap on the gap lock, and then add the primary key clustered index record x lock, then return; then read the next one, repeat. Until the first record that does not meet the criteria [11,f] is reached, the X lock is not required, but the gap lock is still required, and the end is returned.
- Combination Eight:There is no index on the ID column, RR isolation level: at REPEATABLE read isolation level, if the current read of a full table scan, all records in the table will be locked and all gaps within the clustered index will be locked, eliminating all concurrent update/delete/insert operations. Of course, it is also possible to mitigate the locking overhead and concurrency effects by triggering semi-consistent read, but Semi-consistent read itself also poses other problems that are not recommended.
- Combination nine: Serializable isolation level: in Mysql/innodb, the so-called read does not lock, and does not apply to all cases, but to the isolation level related. Serializable isolation level, read without lock is no longer set up, all read operations, are currently read.
Summary on duty problem
