Shulou(Shulou.com)05/31 Report--

Many novices are not very clear about the isolation levels of transactions in MySQL. In order to help you solve this problem, the following editor will explain it in detail. People with this need can come and learn. I hope you can gain something.

What is a transaction?

Transactions, consisting of a limited sequence of database operations, which are either performed or not performed, are an indivisible unit of work.

If A transfers 100 yuan to B, deduct 100 yuan from A's account first, and then add 100 yuan to B's account. If after deducting 100 yuan from A, there is no time to add to B, the banking system is abnormal, resulting in a decrease in the balance of A, but no increase in the balance of B. So you need a transaction to roll back A's money. It's as simple as that.

Four characteristics of transaction

Atomicity: transactions are executed as a whole, and either all or none of the operations on the database are performed.

Consistency: means that the data will not be destroyed before and after the transaction ends. If An account transfers 10 yuan to B account, regardless of success or not, the total amount of An and B will remain the same.

Isolation: when multiple transactions are accessed concurrently, transactions are isolated from each other, one transaction should not be disturbed by other transactions, and multiple concurrent transactions should be isolated from each other.

Persistence: indicates that the operational changes made by the transaction to the database after the transaction has been committed will be persisted in the database.

Problems with transaction concurrency

What's wrong with the concurrent execution of transactions, in other words, how does one transaction interfere with other transactions? Let's look at the example.

Suppose you now have a table:

CREATE TABLE `roomt` (`id` int (11) NOT NULL, `name` varchar (255) DEFAULT NULL, `balance` int (11) DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `un_name_ idx` (`name`) USING BTREE) ENGINE=InnoDB DEFAULT CHARSET=utf8

There is data in the table:

Dirty reading (dirty read)

Suppose there are now two transactions An and B:

Suppose that the balance of An is 100, and transaction An is preparing to query the balance of Jay.

At this time, transaction B deducts the balance of Jay first, deducting 10%

The last thing A reads is the balance after deduction.

From the above figure, we can find that transaction An and B execute alternately, and transaction An is disturbed by transaction B, because transaction A reads the uncommitted data of transaction B. this is dirty reading.

Non-repeatable (unrepeatable read)

Suppose there are now two transactions An and B:

Transaction A first queried the balance of Jay and found that the result was 100.

At this time, transaction B deducts the account balance of Jay and commits the transaction after deducting 10%.

Transaction A then checked the account balance of Jay and found that it became 90%.

Transaction An is disturbed by transaction B again! Within the scope of transaction A, two identical queries read the same record but return different data, which is non-repeatable.

Illusory reading

Suppose there are now two transactions An and B:

Transaction A first inquires the account records whose id is greater than 2, and gets two records of id=2 and id=3.

At this point, transaction B opens, inserts an id=4 record, and commits the

Transaction An executes the same query again, but gets three records of id=2,3,4.

Transaction A queries the result set of a range, another concurrent transaction B inserts / deletes data into this range and quietly commits, and then transaction A queries the same scope again, and the result set obtained by the two reads is different. This is phantom reading.

Four isolation level practices of transactions

Since there are some problems in concurrent transactions, such as dirty reading, non-repeatability, phantom reading, and so on, which isolation level does InnoDB achieve?

Read unsubmitted (Read Uncommitted)

Read submitted (Read Committed)

Repeatable read (Repeatable Read)

Serialization (Serializable)

Read unsubmitted (Read Uncommitted)

Read unsubmitted (Read Uncommitted)

If you want to learn a knowledge point, the best way is to practice it. All right, let's go to the database and set the read uncommitted isolation level for it. Let's practice it.

First set the transaction isolation level to read uncommitted, open transaction A, and query the data of id=1.

Set session transaction isolation level read uncommitted; begin; select * from account where id = 1

The results are as follows:

At this time, open another window to open mysql, set the current transaction isolation level to read uncommitted, open transaction B, and perform the update operation.

Set session transaction isolation level read uncommitted; begin; update account set balance=balance+20 where id = 1

Then go back to the window of transaction A, and then check the data of account table id=1. The results are as follows:

It can be found that under the read uncommitted (Read Uncommitted) isolation level, one transaction will read the uncommitted data of other transactions, that is, there is a dirty read problem. Transaction B has not even commit to the database, transaction A has read it, and it feels like a mess. In fact, read uncommitted is one of the lowest levels of isolation.

Read submitted (READ COMMITTED)

To avoid dirty reads, the database has a higher isolation level than read uncommitted, that is, read committed.

Set the current transaction isolation level to read committed (READ COMMITTED), open transaction A, and query the data of id=1 in account

Set session transaction isolation level read committed; begin; select * from account where id = 1

Open another window to open mysql, also set the transaction isolation level to read committed, open transaction B, and do the following

Set session transaction isolation level read committed; begin; update account set balance=balance+20 where id = 1

Then go back to the window of transaction A, and then check the account data, and find that the data has not changed:

Let's go to the window of transaction B to perform the commit operation:

Commit

Finally, I went back to the transaction A window to query and found that the data had changed:

It can be concluded that when the isolation level is set to committed read (READ COMMITTED), there is no dirty read problem, and the current transaction can only read data committed by other transactions. But if you think about it from the perspective of transaction A, are there any other problems?

What is the problem with reading the submitted isolation level?

In the same transaction A, the same query sql, read the same record (id=1), read the result is not the same, that is, can not be read repeatedly. Therefore, when the isolation level is set to read committed, there will also be a concurrency problem that cannot be repeated.

Repeatable read (Repeatable Read)

What if your boss requires that the query results must be consistent in the same transaction, that is, your boss asks you to solve unrepeatable concurrency problems? Boss, can't my concubines do it? Let's practice the isolation level of Repeatable Read.

Haha, the query results of steps 1, 2 and 6 are all the same, that is, is it happy that repeatable read has solved the problem of unrepeatable reading? finally solved the boss's problem.

Does the RR level solve the phantom reading problem?

Let's take a look at a hot issue on the Internet, about whether the problem of phantom reading has been solved at the RR level. Let's put it into practice:

As can be seen from the figure, steps 2 and 6 do not change the query result set. It seems that the RR level has solved the phantom reading problem. However, this phenomenon still exists at the RR level:

In fact, in the above figure, if there is no update accountsetbalance=200whereid=5; in transaction A, the result set queried by select*fromaccountwhereid > 2 is indeed unchanged, and there is no phantom reading problem in this case. However, with the coquettish operation of update, the same transaction and the same sql will find different result sets, which is in line with the definition of phantom reading.

This question, my dear friend, do you think it is an illusory reading problem?

Serialization (Serializable)

The first three database isolation levels all have some concurrency problems, so now let's take a big step and practice the SERIALIZABLE isolation level.

Set the transaction isolation level to Serializable, open transaction A, and query account table data.

Set session transaction isolation level serializable; select @ @ tx_isolation; begin; select * from account

Open another window to open mysql, set the transaction isolation level to Serializable, open transaction B, and insert a piece of data:

Set session transaction isolation level serializable; select @ @ tx_isolation; begin; insert into account (id,name,balance) value

The implementation results are as follows:

From the figure, when the database isolation level is set to serializable, the write operation of transaction B to the table is waiting for the read operation of transaction A. In fact, this is the strictest isolation level, and concurrency is not allowed for both reading and writing. It ensures the best security, but performance is a problem.

Implementation principle of MySql isolation level

There are two main ways to implement the isolation mechanism:

Read-write lock

Consistent snapshot read, or MVCC

MySql uses different locking policies (Locking Strategy) / MVCC to achieve four different isolation levels. The implementation principle of RR and RC is related to MVCC, while RU and Serializable are related to locks.

Read unsubmitted (Read Uncommitted)

Officially:

SELECT statements are performed in a nonlocking fashion, but a possible earlier version of a row might be used. Thus, using this isolation level, such reads are not consistent.

Read unsubmitted, adopt the read unlocked principle.

Transaction reads are unlocked and do not block the reading and writing of other transactions

Transaction writes block other transaction writes, but do not block other transaction reads

Serialization (Serializable)

The official saying:

InnoDB implicitly converts all plain SELECT statements to SELECT... FOR SHARE if autocommit is disabled. If autocommit is enabled, the SELECT is its own transaction. It therefore is known to be read only and can be serialized if performed as a consistent (nonlocking) read and need not block for other transactions. To force a plain SELECT to block if other transactions have modified the selected rows, disable autocommit.

All SELECT statements are implicitly converted to SELECT...FOR SHARE, that is, with shared locks.

Read plus shared lock, write plus exclusive lock, read and write mutually exclusive. If there is anything unsubmitted,

Some lines are being modified, and all select statements on these lines will block.

The realization principle of MVCC

MVCC, called multi-version concurrency control in Chinese, is a mechanism to reduce concurrency transaction conflicts and improve concurrency performance by reading historical versions of data. Its implementation depends on implicit fields, undo logs, snapshot reads & current reads, and Read View, so let's look at these knowledge points first.

Implicit field

For the InnoDB storage engine, there are two hidden columns DBTRXID,DBROLLPTR for each row of records, and a third hidden primary key column DBROWID if there is no primary key and non-NULL unique key in the table.

DBTRXID, which records the last modification (modification / update) of its transaction ID for each row, with a size of 6 bytes

DBROLLPTR, a hidden column that acts as a pointer to the undo log of the rollback segment, with a size of 7 bytes

DBROWID, a monotonously incrementing line ID with a size of 6 bytes

Undo log

When the transaction is not committed, modify the mirror image of the data (the old version before modification) and store it in the undo log. So that when the transaction is rolled back, the old version of the data is restored and the impact of the uncommitted transaction data on the database is undone.

Undo logs are logical logs. It can be thought that when a record is delete, a corresponding insert record is recorded in undo log, and when a record is update, it records a corresponding update record.

The place where the undo log is stored is the rollback segment.

When multiple transactions operate on a row of data in parallel, multiple versions of the data are modified by different transactions, and then a chain of Undo logs is connected by rolling back the pointer (DBROLLPTR).

Let's take a look at it through examples.

Mysql > select * from account; +-+ | id | name | balance | +-+ | 1 | Jay | 100 | +-+ 1 row in set (0.00 sec)

Suppose the table accout now has only one record, and the transaction Id inserted into that record is 100th.

If transaction B (transaction Id is 200), update the row record of id=1 and change the balance value to 90

After transaction B is modified, the Undo Log chain is formed as follows:

Snapshot Reading & current Reading

Snapshot read:

Read the visible version of the recorded data (there is an old version), unlocked, and ordinary select statements are snapshot reads, such as:

Select * from account where id > 2

Current read:

The latest version of the recorded data is read, which shows that the locked data is the current read.

Select * from account where id > 2 lock in share mode; select * from account where id > 2 for update

Read View

Read View is the read view generated when a transaction performs a snapshot read.

When a transaction performs a snapshot read, it generates a current snapshot of the database system, records any active read and write transactions in the current system, and puts them in a list.

Read View is mainly used for visibility judgment, that is, to determine which version of the data is visible in the current transaction.

To facilitate the discussion of Read View visibility rules below, define a few variables first

M_ids: the active read-write transaction ID in the current system, whose data structure is a List.

The smallest transaction ID in the minlimitid:m_ids transaction list

ID, the largest transaction in the maxlimitid:m_ids transaction list

Hongmeng official Strategic Cooperation to build HarmonyOS Technology Community

If DBTRXID

< minlimitid，表明生成该版本的事务在生成ReadView前已经提交(因为事务ID是递增的)，所以该版本可以被当前事务访问。 如果DBTRXID >

The largest transaction id in the m_ids list indicates that the transaction that generated the version was not generated until after the ReadView was generated, so the version cannot be accessed by the current transaction.

If minlimitid =

Attention! The biggest difference between RR and RC isolation level is that RC generates a ReadView before each read, while RR only generates a ReadView the first time the data is read.

The Analysis process of the unrepeatable Reading problem in submitted read (READ COMMITTED)

I think the best way to understand a new knowledge point is to live in the existing problems / phenomena and analyze its context. The implementation of RC is also related to MVCC. RC has the problem of repeated read concurrency, so let's analyze a wave of RC and take a look at the execution process.

Suppose there are two transactions executed in the system, the transaction ID is 100,200 respectively, and if there is old data, the insert transaction ID is 50 ha ~

Transaction A first executes the operation of query 1

# transaction ID 100begin; query 1:select * transaction = 1

Transaction B performs the update operation, and the undo log chain recorded by id = 1 is as follows

Begin; update account set balance = balance+20 where id = 1

Go back to transaction An and perform the operation of query 2

Begin; query 1:select * from account WHERE id = 1; query 2:select * from account WHERE id = 1

Query 2 performs analysis:

Transaction A regenerates a ReadView when it executes the SELECT statement. Because transaction B (200) is active, the m_ids list content of ReadView is [200].

Available from the undo log chain above, the latest version of balance is 1000, and its transaction ID is 200. it is in the active transaction list, so the current transaction (transaction A) is not visible.

Let's move on to the next version, the row record with balance 100and the transaction Id 50, which is less than the minimum record 200in the ID list of active transactions, so this version is visible, so the result of query 2 is to return the record balance=100 ~

We go back to transaction B and perform the commit operation, where the undo log chain remains the same

Begin; update account set balance = balance+20 where id = 1; commit

Go back to transaction An again and execute the operation of query 3

Begin; query 1:select * from account WHERE id = 1; query 2:select * from account WHERE id = 1; query 3:select * from account WHERE id = 1

Query 3 performs analysis:

Transaction A regenerates a ReadView when it executes the SELECT statement, because transaction B (200) has been committed and is no longer active, so the m_ids list content of ReadView is empty.

So transaction A reads the latest record directly and reads the data in the version balance = 120.

So, this is the process that RC has the problem of unrepeatable reading. If you don't understand, you can read it several times.

An Analysis of Repeatable Read to solve the problem of non-repeatable Reading

Let's take another look at how the RR isolation level solves the problem of unrepeatable read concurrency.

You may think that the examples of two concurrent transactions are too simple, okay! Now let's do something exciting and start three things.

Suppose there are two transactions executed in the system, the transaction ID is 100,200 and 300 respectively, and the transaction ID inserted by the stock data is 50 ~

# transaction A transaction ID 100th begin; UPDATE account SET balance = 1000 WHERE id = 1 cross # transaction transaction ID 200begin; / / start a transaction, taking advantage of the pit

At this point, in the account table, the undo log chain recorded by id = 1 is as follows:

# transaction transaction ID 300 begin; / / query 1:select * from account WHERE id = 1

Query 1 execution process analysis:

When transaction C executes the SELECT statement, it becomes a ReadView. Because transactions A (100) and B (200) are active, the content of the m_ids list for ReadView is [100200].

Available from the undo log chain above, the latest version of balance is 1000, and its transaction ID is 100. it is in the active transaction list, so the current transaction (transaction C) is not visible.

Let's move on to the next version, the row record with balance 100and the transaction Id 50, which is less than the minimum record 100in the ID list of active transactions, so this version is visible, so the result of query 1 is to return the record balance=100 ~

Next, let's commit transaction A:

# transaction ID begin; UPDATE account SET balance = 1000 WHERE id = 1; commit

In transaction B, perform an update operation to modify the record balance of id=1 to 2000. After the update, the undo log chain is as follows:

# transaction B transaction ID 200begin; / / start a transaction, UPDATE account SET balance = 2000 WHERE id = 1

Go back to transaction C and execute query 2

# transaction ID 300 begin; / / query 1:select * from account WHERE id = 1; / / query 2:select * from account WHERE id = 1

Query 2: perform analysis:

At the RR level, when executing query 2, because the previous ReadView has already been generated, take the previous ReadView directly, and the active transaction list is [100200].

Available from the undo log chain above, the latest version of balance is 2000, and its transaction ID is 200. it is in the active transaction list, so the current transaction (transaction C) is not visible.

Let's move on to the next version, which has a balance of 1000 and a transaction Id of 100. it is also in the active transaction list, so the current transaction (transaction C) is not visible.

Continue to find the next version. The row record balance is 100and the transaction Id is 50, which is less than the minimum record 100in the ID list of active transactions, so this version is visible. Therefore, the result of query 2 also returns the record balance=100.

Supplement to lock related concepts (attached):

Shared lock and exclusive lock

InnoDB implements standard row-level locks, including shared locks (s locks for short) and exclusive locks (x locks for short).

Shared lock (S lock): allows lock-holding transactions to read a row.

Exclusive lock (X lock): allows lock-holding transactions to update or delete a row.

If transaction T1 holds an s lock for row r, when another transaction T2 requests a lock for r, it does the following:

T2 request s lock is allowed immediately, as a result, T1 T2 holds the s lock of r line.

T2 request x lock cannot be allowed immediately

If T1 holds the x lock of r, then the x and s locks of T2 request r cannot be allowed immediately. T2 must wait for T1 to release the x lock, because the X lock is not compatible with any lock.

Record lock (Record Locks)

The record lock is the simplest row lock, locking only one row. Such as: SELECT C1 FROM t WHERE c1=10FOR UPDATE

Record locks are always added to the index, and even if a table does not have an index, InnoDB implicitly creates an index and uses it to implement record locking.

It will block other transactions from inserting, updating and deleting it.

Record the transaction data of the lock (keyword: lock_mode X locks rec butnotgap), record as follows:

RECORD LOCKS space id 58 page no 3 n bits 72 index `PRIMARY` of table `test`.`t` trx id 10078 lock_mode X locks rec but not gap Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 00: len 4; hex 80000000a; asc;; 1: len 6; hex 000000274f; asc'Oscape; 2: len 7; hex b60000019d0110; asc

Gap lock (Gap Locks)

A gap lock is a lock added between two indexes, either before the first index or after the last index.

A gap lock is used to lock an interval, not just every piece of data in that interval.

Gap locks only prevent other transactions from being inserted into the gap, and they do not prevent other transactions from acquiring gap locks on the same gap, so gap x lock and gap s lock have the same effect.

Next-Key Locks

A Next-key lock is a combination of a record lock and a gap lock, which refers to the lock added to a record and the gap in front of the record.

Analysis of Phantom Reading at RC level

Because there is a phantom reading problem in RC, let's first cut to the RC isolation level and analyze a wave.

Suppose the account table has four pieces of data.

Open transaction A, perform the current read, and query all records for id > 2.

Open transaction B again and insert a piece of data from id=5.

After transaction B successfully inserts the data, modify the record of id=3

Go back to transaction An and execute the current read query of id > 2 again

Transaction B can insert the data of id=5, but cannot update the data of id=3 and get stuck. It is proved that transaction A locks id= 3 and id=4 records while performing the current read, but does not lock the range of id > 2.

After transaction B is blocked, when switching back to transaction A to perform the current read operation, a deadlock occurs. Because transaction B will lock the new record (id=5) when it is in insert, transaction A performs the current read again. If you want to get the record of id > 2, you need to lock these three records of id=3,4,5. But the record of id=5 has been locked by transaction B, so transaction An is blocked by transaction B, while transaction B is still waiting for transaction A to release the lock on id=3, resulting in a deadlock.

Therefore, we can find that under the RC isolation level, locked statements such as select, update, delete, etc., use record locks, and the insertion of other transactions can still be executed, so there will be phantom readings.

Analysis of solving Phantom Reading at RR level

Because RR is to solve the problem of phantom reading, how to solve it? analyze a wave.

Suppose the account table has four pieces of data, at the RR level.

Open transaction A, perform the current read, and query all records for id > 2.

Open transaction B again and insert a piece of data from id=5.

It can be found that transaction B blocks when performing the insert operation ~ because transaction An is executing select. In the case of lock in share mode, not only the two records of id = 3jiao 4 were locked, but also a gap lock was added in the range of id > 2.

Therefore, we can find that under the RR isolation level, locked select, update, delete and other statements will use gap lock + key lock to lock the range between index records and avoid inserting records between ranges to avoid phantom row records.

Is it helpful for you to read the above content? If you want to know more about the relevant knowledge or read more related articles, please follow the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.