Transaction ACID properties, where I stands for Isolation.
What is transaction isolation?
Isolation means that when the concurrent transactions of multiple users access the same database, the transactions of one user should not be disturbed by the transactions of other users, and multiple concurrent transactions should be isolated from each other.
How can one transaction interfere with another?
Let’s take an example. Suppose we have an InnoDB table:
t(id PK, name);
There are three records in the table:
1, shenjian
2, zhangsan
3, lisi
Case 1: Read dirty
Transaction A, executed first, in uncommitted state:
insert into t values(4, wangwu);
Transaction B, later executed, also not committed:
select * from t;
If transaction B can read the record (4, wangwu), transaction A has an effect on transaction B. This effect is called “read dirty”, reading the records of uncommitted transactions.
Case 2: Cannot be read repeatedly
Transaction A, first executed:
select * from t where id=1;
The result set is:
1, shenjian
Transaction B, then executes, and commits:
update t set name=xxoo where id=1;
commit;
Transaction A, execute the same query again:
select * from t where id=1;
The result set is:
1, xxoo
This time it is the effect of committed transaction B on transaction A. This effect is called “unrepeatable reads”, where the same query within A transaction yields different results.
Case 3: Unreal reading
Transaction A, first executed:
select * from t where id>3;
The result set is:
NULL
Transaction B, then executes, and commits:
insert into t values(4, wangwu);
commit;
Select * from (select * from (select * from (select * from (select * from (select * from (select * from (select * from (select * from (select * from (select * from (select * from (select * from)))));
insert into t values(4, xxoo);
The result set is:
Error : duplicate key!
Mysql > insert id=4; mysql > insert id=4; mysql > insert id=4; mysql > insert ID =4;
This time it is the effect of committed transaction B on transaction A. This effect is called A “phantom read”.
As you can see, concurrent transactions can lead to other transactions:
(1) Read dirty;
(2) not repeatable to read;
(3) magic reading;
What kind of transaction isolation levels does InnoDB implement?
According to the SQL92 standard, InnoDB implements four different transaction isolation levels:
(1) Read Uncommitted;
(2) Read Committed, RC;
(3) Repeated Read (RR);
(4) Serializable;
Different transaction isolation levels are a tradeoff between consistency and concurrency.
How are InnoDB’s four transaction isolation levels implemented?
InnoDB uses different Locking strategies to achieve different isolation levels.
I. Read unsubmitted ( Read Uncommitted )
At this transaction isolation level, select statements are not locked.
Voice-over: The official line is
SELECT statements are performed in a nonlocking fashion.
In this case, inconsistent data may be read, that is, read dirty. This is the isolation level with the highest concurrency and lowest consistency.
Ii. Serialization ( Serializable )
At this transaction isolation level, all SELECT statements are implicitly converted to SELECT… in share mode.
This can result in any SELECT reading rows being blocked if uncommitted transactions are modifying them.
Voice-over: The official line is
To force a plain SELECT to block if other transactions have modified the selected rows.
This is the isolation level with the best consistency but the worst concurrency.
In the scenario of large data volume and high concurrency on the Internet, the above two isolation levels will hardly be used.
Three, repeatable ( Repeated Read, RR )
This is InnoDB’s default isolation level, under RR:
(1) Ordinary select uses Snapshot read, which is a Consistent Nonlocking read, and MVCC is used to implement it at the bottom level. The specific principle is described in InnoDB concurrency is so high, why is that? Are described in detail in;
Select * from (select… in share mode / select … For update, update, delete, etc., their locks depend on whether they use unique search conditions on unique indexes. Or range-type search condition:
– Use a unique query condition on a unique index, a record lock will be used, and the interval between records will not be locked, i.e. a gap lock and a next-key lock will not be used.
– Range query condition, will use gap lock and adjacent key lock, lock index records between the range, to avoid the insertion of records between the range, to avoid phantom row records, try to avoid non-repeatable read \
Voice-over: This passage is a bit convoluted. Read it several times.
For more information on record locks, gap locks, and keylocks, see InnoDB’s seven Types of Locks.
4. Read the submission ( Read Committed, RC )
This is the most common isolation level on the Internet, under RC:
(1) Common read is snapshot read.
(2) Select, UPDATE, delete, and other locked statements, except for foreign-key constraint checking and duplicate-key checking. All other times only log locks are used;
At this point, the insertion of other transactions can still be performed, which may result in the phantom record being read.
conclusion
(1) Interference between concurrent transactions may lead to problems such as dirty reads, non-repeatability and phantom reads;
(2) InnoDB implements four isolation levels in SQL92 standard;
– Read uncommitted: select is not locked, and the read may be dirty.
– Read commit (RC) : Common SELECT snapshot read. The lock select /update /delete uses a record lock and may cause unrepeatable reads.
– Repeatable read (RR) : common SELECT snapshot read, lock SELECT/UPDATE /delete Record lock, gap lock, or temporary key lock is selected based on query conditions to prevent phantom records from being read.
– serialization: implicitly convert select to select… In share mode, update and delete are mutually exclusive.
InnoDB’s default isolation level is RR, and the most commonly used isolation level is RC.
Some of you might ask, why didn’t you mention insert? It uses insert intent locks, as detailed in InnoDB’s seven Locks.
The Architect’s Path – Share practical architecture articles
Related articles:
Why InnoDB concurrency is so high?
InnoDB’s Seven Locks
InnoDB index, Finally understood
Voiceover: This article is based on MySQL5.6.
The code word is not easy, thank you.