Shulou(Shulou.com)06/01 Report--

This article mainly tells you briefly what the MySql transaction isolation level is and its function. You can check the relevant professional terms on the Internet or find some related books to supplement them. We will not dabble here. Let's go straight to the topic. I hope this article can bring you some practical help. I hope this article can bring you some practical help.

First, the four characteristics of transactions (ACID)

There are four characteristics of a transaction that you have to understand before you can understand the transaction isolation level.

1. Atomicity (Atomicity)

After the transaction starts, either all operations are done or none are done. Affairs are an indivisible whole. The transaction makes an error during execution and is rolled back to the state before the start of the transaction to ensure the integrity of the transaction. Similar to the physical interpretation of atoms: basic particles that can no longer be separated in a chemical reaction. Atoms are inseparable in a chemical reaction.

2. Consistency (Consistency)

After the transaction starts and ends, it can ensure the correctness of the database integrity constraints, that is, the integrity of the data. For example, in the classic case of money transfer, when A transfers money to B, we must ensure that A deducts the money and B will receive the money. Personal understanding is similar to physical conservation of energy.

3. Isolation (Isolation)

Complete isolation between transactions. For example, A transfers money to a bank card to avoid the loss of the account amount caused by too many operations at the same time, so other operations on this card are not allowed before the end of A transfer.

4. Persistence (Durability)

The impact of transactions on data is permanent. The popular interpretation is that after the transaction is completed, the operation of the data will be closed (persisted). Once the transaction is completed, it is irreversible, and in the operation of the database, it is shown that once the transaction is completed, it cannot be rolled back.

Second, the concurrency of transactions

In the tide of the Internet, the value of programs is no longer in the traditional industry to help people solve some complex business logic. In the Internet era of the supremacy of user experience, the code is like the footsteps of programmers in Xierqi subway station, speed, speed or speed. Of course, you can't sit in the wrong direction. I originally wanted to go to Xizhimen and finally to Dongzhimen (understood as correct for the time being). Compared with the complex business logic of the traditional industry, the Internet pays more attention to the speed and passion that concurrency brings to the program. Of course, speeding comes at a price. In concurrent transactions, the poor programmer is about to run away.

1. Dirty reading

Also known as invalid data readout. One transaction reads data that has not been committed by another transaction is called dirty read.

For example, transaction T1 modifies a row of data, but has not yet committed, and transaction T2 reads the data modified by transaction T1, and then transaction T1 Rollback for some reason, then transaction T2 reads dirty data.

2. Non-repeatable

The same data read out multiple times in the same transaction is inconsistent.

For example, transaction T1 reads a certain data, transaction T2 reads and modifies the data, and T1 reads the data again in order to verify the read value, and gets different results.

3. Illusory reading

It's hard to express. Let's go straight to the example:

In the warehouse management, the administrator should enter the warehouse management for a batch of goods that have just arrived. Of course, before entering the warehouse, we must check whether there is a record of entering the warehouse to ensure correctness. Administrator An ensures that the item does not exist in the library and then carries out the storage operation for the item, if administrator B has already put the item into the warehouse because of the imminent hand. At this time, administrator A found that the item was already in the library. As if he had just had a phantom reading, he suddenly had something that didn't exist.

Note: the three questions do not seem to be easy to understand. Dirty reading focuses on the correctness of the data. Non-repeatability focuses on the modification of data, while phantom reading focuses on the addition and deletion of data.

3. Four transaction isolation levels of MySql

The previous section learned about the impact of high concurrency on transactions. The four isolation levels of transactions are the solutions to the above three problems.

Isolation level dirty read non-repeatable fantasy read uncommitted (read-uncommitted) is non-repeatable read (read-committed) whether it is repeatable read (repeatable-read) whether it is serializable (serializable) No

4. Sql demonstrates four isolation levels

Mysql version: 5.6

Storage engine: InnoDB

Tool: navicat

Build a table sentence:

CREATE TABLE `tb_ bank` (`id` int (11) NOT NULL AUTO_INCREMENT, `name` varchar (16) COLLATE utf8_bin DEFAULT NULL, `demot` int (11) DEFAULT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;INSERT INTO `demo`.`tb _ bank` (`id`, `name`, `demot`) VALUES (1, 'Xiaoming', 1000)

1. Demonstrate through sql-dirty reading of read-uncommitted

(2) dirty reading caused by read-uncommit

Dirty reading means that two transactions, one of which can read the uncommitted data of the other transaction.

Scene: 200 yuan for session1 and 100 yuan for session2. The base number is 1000. The correct result for successful completion should be 900 yuan. But let's assume that the session2 forward is rolled back for some reason. The correct result at this time should be 800 yuan.

Demonstrate the steps:

① create two new session (sessions, represented as two query windows in navicat and also two windows in the mysql command line), and execute each

Select @ @ tx_isolation;// query the current transaction isolation level set session transaction isolation level read uncommitted;// sets the transaction isolation level to read uncommitted

Both session of ② start transaction

Start transaction;// starts a transaction

③ session1 and session2: prove that the account balance before the execution of the two operations is 1000

Select * from tb_bank where id=1;// query result is 1000

④ session2: at this point, it is assumed that the update of session2 is executed first.

Update tb_bank set account = account + 100where id=1

⑤ session1: session1 starts execution before session2 commit.

Select * from tb_bank where id=1;// query result: 1100

⑥ session2: for some reason, the transfer fails and the transaction is rolled back.

Rollback;// transaction rollback commit;// commit transaction

⑦ then session1 starts to transfer out, and session1 thinks that 1100 of the query result in ⑤ is the correct data.

Update tb_bank set account=1100-200 where id=1; commit

⑧ session1 and session2 query results

Select * from tb_bank where id=1;// query result: 900

At this time, we found that the final data inconsistency was caused by the dirty reading of session1. The correct result should be 800

At this point, how can we avoid dirty reading and increase the isolation of transactions to read-commit?

(2) read-commit solves dirty reading.

Reset the data to restore the data to account=1000

① create two new session, which are set separately

Set session transaction isolation level read committed;// sets the isolation level to non-repeatable

Repeat the ②③④ steps in (1)

⑤ session1 executes query

The result of the select * from tb_bank where id=1;// query is 1000, which means that the unrepeatable isolation level effectively isolates the transactions of the two sessions.

At this time, we find that after upgrading the transaction isolation to read-committed;, the two transactions are effectively isolated, so that the transaction in session1 can not query the changes to the data of the transaction in session2. Effectively avoid dirty reading.

2. Demonstration through sql-non-repeatable reading of read-committed

(1) non-repeatable reading of read-commit

Reset the data to restore the data to account=1000

The so-called unrepeatable read means that one transaction cannot read the data of another uncommitted transaction, but can read the committed data. At this time, the results of the two reads are inconsistent. So it can't be repeated.

Under the READ COMMITTED isolation level, a snapshot is regenerated for each read, so each snapshot is up-to-date, so each SELECT in a transaction can also see changes that have been made by other commit transactions.

Scenario: session1 carries on the account inquiry, session2 carries on the account transfer 100.

When session1 opens a transaction and is ready to query and update the account, session2 also opens a transaction to update the account. The correct result should be that the query reads the same result after the session1 starts the transaction.

① create two new session, which are set separately

Set session transaction isolation level read committed

② session1 and session2 start the transaction respectively

Start transaction

③ session1's first query:

Select * from tb_bank where id=1;// query result: 1000

④ session2 to update:

Update tb_bank set account = account+100 where id=1; select * from tb_bank where id=1;// query result: 1100

⑤ session1 second query:

Select * from tb_bank where id=1;// query result: 1100. Compared with the query results in ③, the results of the two queries in session1 are inconsistent.

If you look at the query results, you can see that the results of repeated reads during the opening of the transaction in session1 are inconsistent, so you can see that the read commit transaction isolation level is not repeatable. Obviously, this result is not what we want.

(2) repeatable-read can be read repeatedly

Reset the data to restore the data to account=1000

① create two new session, which are set separately

Set session transaction isolation level repeatable read

Repeat the ②③④ in (1)

⑤ session1 second query:

Select * from tb_bank where id=1;// query result: 1000

From the results, it can be seen that under the isolation level of repeatable-read, the results of multiple reads are not affected by other transactions. It's repeatable. A question arises here. The result session1 read is still the result before the update of session2. Can you get the correct 1200 result if you continue to transfer to 100 in session1?

Continue:

⑥ session1 is transferred to 100:

Update tb_bank set account=account+100 where id=1

I feel cheated when I come here. Lock. The update statement for session1 is blocked. Execution can continue in session1 only after the update statement commit in session2. The execution result of session is 1200. At this time, it is found that session1 is not calculated with 1000 read 100, because the MVCC mechanism is used under the repeatable isolation level, and the select operation does not update the version number, but is a snapshot read (historical version). Insert, update, and delete update the version number, which is the current read (current version).

3. Demo through sql-Phantom Reading of repeatable-read

In business logic, we usually get the data in the database first, and then determine whether the condition conforms to our business logic in the business. If so, we can insert part of the data. But snapshot reading of mysql can produce unexpected results in the process.

Scene simulation:

When session1 starts a transaction, first check whether there is any account information for Xiao Zhang, and then insert one if not. This is where session2 performs the same operation as session1.

Preparation: insert two pieces of data

INSERT INTO `demo`.`tb _ bank` (`id`, `name`, `demot`) VALUES (2, 'Xiao Hong', 800); INSERT INTO `demo`.`tb _ bank` (`id`, `name`, `account`) VALUES (3, 'Xiaolei', 6000)

(1) Phantom reading of repeatable-read

Both new session of ① will be executed.

Set session transaction isolation level repeatable read; start transaction; select * from tb_bank;// query result: (this step is very important and directly determines the time when the snapshot is generated)

The result is:

② session2 insert data

INSERT INTO `demo`.`tb _ bank` (`id`, `name`, `roomt`) VALUES (4, 'Xiao Zhang', 8000); select * from tb_bank

Results the data was inserted successfully. At this point, session2 commits the transaction

Commit

③ session1 to insert

Before inserting, let's check whether the current session1 has id=4 data.

Select * from tb_bank

As a result, there is no such record in the session1. According to our usual business logic, this should be the data that can be successfully inserted into the id=4. Continue to execute:

INSERT INTO `demo`.`tb _ bank` (`id`, `name`, `roomt`) VALUES (4, 'Xiao Zhang', 8000)

As a result, the insertion failed, indicating that the entry already exists, but there is no such data in our query. Why did the insertion fail?

Because the select statement in ① generates a snapshot, subsequent reads (without read locks) are snapshot reads, that is, before the end of the current transaction, the result of all reads is the snapshot version of the first snapshot read. The question comes again, why didn't the select statement in the ② step read the snapshot version? Because the update statement updates the snapshot version of the current transaction. Refer to the fifth chapter for details.

(2) repeatable-read uses current reading to solve phantom reading.

Repeat the ①② in (1)

③ session1 to insert

Before inserting, let's check whether the current session1 has id=4 data.

Select * from tb_bank

As a result, there is no such record in the session1. According to our usual business logic, this should be the data that can be successfully inserted into the id=4.

Select * from tb_bank lock in share mode;// uses current read

Result: it is found that Xiao Zhang's account information is already in the current result. According to the business logic, we will not continue to perform the insert operation.

At this point, we found that using the current read avoids the phantom reading at the repeatable-read isolation level.

4. Serializable isolation level

At this level, we no longer do the sql demonstration of serializable to avoid illusion, after all, the whole table is locked.

Current read and snapshot read

I would like to set up a separate blog for current reading and snapshot reading, but in order to sum up the phantom reading, I will briefly explain snapshot reading and current reading in this chapter. Add another MVCC,next-key blog post later.

1. Snapshot reading: that is, consistent unlocked reading.

Under the ① InnoDB storage engine, query statements perform snapshot reads by default.

The first read operation in the next transaction under the ② RR isolation level produces a snapshot of the data.

The ③ update,insert,delete operation updates the snapshot.

Snapshot reads under four transaction isolation levels are different:

① read-uncommitted and read-committed level: each read generates a new snapshot, and each read is up-to-date, so the select result at the RC level can see the changes made by other transactions to the current data, and the RU level can even read the data from other uncommitted transactions. Therefore, the data can not be read repeatedly at these two levels.

② repeatable-read level: concurrency control based on MVCC with extremely high concurrency performance. The first read produces a snapshot of the read data, and then the read operation is consistent with the result of the first read if no snapshot update occurs in the current transaction. Snapshots are generated in transactions, and snapshots in different transactions are completely isolated.

③ serializable level: from MVCC concurrency control to lock-based concurrency control. No difference between snapshot read and current read, all read operations are current read, read plus read lock (S lock), write plus write lock (X lock). Under the Serializable isolation level, there are read-write conflicts, so the degree of concurrency decreases sharply. (lock table, not recommended)

2. Current reading: that is, consistent locked reading.

How to generate the current read

① select... Lock in share mode

② select... For update

③ update,insert,delete operations are all current reads.

After reading, you also need to ensure that the current record cannot be modified by other concurrent transactions, and the current record needs to be locked. ① adds S lock (shared lock) and ②③ X lock (exclusive lock) to the read record.

3. Summary of questions

Why are all ① update,insert,delete operations currently read?

To put it simply, if the current read is not performed, the integrity constraints of the data may be broken. Especially in high concurrency environment.

Analyze the execution steps of the update statement: update table set. Where...

The InnoDB engine first carries out the query of where, the result set of the query performs the current read from the first item, then performs the update operation, and then reads the second piece of data and executes the update operation. So each execution of the update is accompanied by the current read. The same is true of delete. After all, the data must be found before it can be deleted. Insert is a little different in that you need to perform a unique key check before the insert operation is performed. [related recommendation: MySQL tutorial]

What is the MySql transaction isolation level and its role will first tell you here, for other related issues you want to know can continue to pay attention to our industry information. Our section will capture some industry news and professional knowledge to share with you every day.

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.