MySQL transaction details

Time:2021-1-16

To learn something, we should start with its conceptFirst of all, what is a transaction?

A database transaction is a sequence of database operations that access and possibly operate various data items. These operations are either executed or not executed.

From this concept, a transaction is a set of SQL, and the set of SQL is either executed or not executed.

So the second common question is, what are the characteristics (points) of transactions? This is a very common question.

Four characteristics of transaction: a (atomicity) C (consistency) I (isolation) d (durability). How to understand these four characteristics?

1. Atomicity(atomicity): all operations after the start of a transaction are either completed or not done. It is impossible to do half of them. If an error occurs during the execution of a transaction, it will be rolled back to the state before the start of the transaction, that is, the transaction is an indivisible whole.

2. ConsistencyConsistency: before and after the transaction, the integrity constraints of the database are not broken. For example, if a transfers money to B, and a deducts money, B will receive it. There is no case that a side reduces money, but B side does not add money.

3. Isolation(isolation): concurrent transactions should not cause the database to be in an inconsistent state. Each transaction in the system should be executed like a unique transaction. No transaction should affect the existence of other transactions. If a transfers all the money to B at the same time, the two transactions should be carried out independently, and the balance should be confirmed before the transfer. Isolation will be described in detail later.

4. Persistence(durability) after the transaction is completed, all the updates of the transaction to the database will be saved to the database (disk), which can not be rolled back. It can also be understood that no matter whether the dB or the system fails, the data will be permanently saved on the disk and will not be lost.

Does MySQL support transactions? MySQL has a variety of storage engines, including MyISAM, InnoDB, memory, merge, etc. Among them, InnoDB and BDB support transactions, while MyISAM does not.

How does MySQL execute transactions?
1. Start the transaction

mysql> start transaction;
perhaps
mysql> begin;

2. Execute the SQL statements you want to run in the transaction
3. Execute the commit statement to complete the transaction and submit the data

For example, suppose there is a table student:

MySQL [test]> select * from student;
+----+----------+-----+
| id | name     | age |
+----+----------+-----+
|  1 | clark    |  30 |
|  2 | zhangsan |  28 |
|  3 | lisi     |  28 |
+----+----------+-----+

Let’s execute a transaction and change the age of Zhangsan to 22. The specific sentence is as follows:

#Session (transaction) 1
MySQL [test]> begin;
Query OK, 0 rows affected (0.01 sec)

MySQL [test]> 
MySQL [test]> 
MySQL [test]> 
MySQL [test]> 
MySQL [test]> update student set age = 22 where id = 2;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MySQL [test]> select * from student;
+----+----------+-----+
| id | name     | age |
+----+----------+-----+
|  1 | clark    |  30 |
|  2 | zhangsan |  22 |
|  3 | lisi     |  28 |
+----+----------+-----+
3 rows in set (0.01 sec)

At this time, we do not execute commit in session 1, and then we query the student record with id = 2 in another session window 2, and the age is still 28 (although the latest data 22 can be found in the current transaction session (Session 1). Because the previous session has no commit (the default transaction isolation level of MySQL is repeatable), we still read the data before the transaction is finished Data.

#Session (transaction) 2
MySQL [test]> select * from student;
+----+----------+-----+
| id | name     | age |
+----+----------+-----+
|  1 | clark    |  30 |
|  2 | zhangsan |  28 |
|  3 | lisi     |  28 |
+----+----------+-----+
3 rows in set (0.01 sec)

When I commit session window 1, the query result of session 2 is as follows:

#Session (transaction) 1
MySQL [test]> commit;
Query OK, 0 rows affected (0.00 sec)
#Session (transaction) 2
MySQL [test]> select * from student;
+----+----------+-----+
| id | name     | age |
+----+----------+-----+
|  1 | clark    |  30 |
|  2 | zhangsan |  22 |
|  3 | lisi     |  28 |
+----+----------+-----+
3 rows in set (0.01 sec)

The above is the execution process of a transaction. If we want to abort the transaction without committing, we can use rollback:

MySQL [test]> begin;
Query OK, 0 rows affected (0.01 sec)

MySQL [test]> update student set age = 32 where id = 1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MySQL [test]> select * from student where id = 1;
+----+-------+-----+
| id | name  | age |
+----+-------+-----+
|  1 | clark |  32 |
+----+-------+-----+
1 row in set (0.01 sec)

MySQL [test]> rollback;
Query OK, 0 rows affected (0.00 sec)

MySQL [test]> select * from student where id = 1;
+----+-------+-----+
| id | name  | age |
+----+-------+-----+
|  1 | clark |  30 |
+----+-------+-----+
1 row in set (0.00 sec)

PS:Talk about autocommit
By default, the state of autocommit is on, which means that all individual statements will be committed once they are executed, unless the statement is in begin . commit statement block. To view the autocommit value, you can use:

MySQL [test]> show variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | ON    |
+---------------+-------+
1 row in set (0.01 sec)

If the autocommit state is off, you need to explicitly issue a commit statement to commit the transaction. To disable autocommit, use the following command:

MySQL [test]> set autocommit = 0;

PS: DDL statements, such as create, alter and drop statements, cannot be rolled back.

After talking about the concept of transaction and the usage of transaction, there is a very common problem about transaction: the isolation level of transaction, which is also a routine test.

The isolation level of transaction describes the isolation degree of one transaction from other transactions in terms of resource or data modification when two or more transactions occur at the same time. There are four levels of transaction isolation: read uncommitted, read committed, repeatable read and serializable.

We mentioned earlier that the default transaction isolation level of MySQL is repeatable read. How to modify the isolation level? You can use set @ transaction_ Isolation =’xxx ‘; to modify the isolation level of a transaction.

Next, let me talk about the four transaction isolation levels in detail

1. Read not submitted(read uncommitted): as the name suggests, a transaction can read data written by another uncommitted transaction. Read uncommitted is also called dirty read (because it causes dirty read).

Let’s take an example,

If the boss gives you a salary, the transfer amount should have been 2W, but you accidentally input the wrong number, which is 2.5W. The money has been transferred to your account, but the transaction has not been submitted. At this time, you go to check your account and find that there is an extra 5K. You think that the salary has been increased. The boss immediately rolls back the transaction, changes the number to 2W, and then goes back to com MIT, you’re blinded. Just now, it’s still 2.5W, which leads to dirty reading

Specific query examples:

#Session 1 -- session 1 has no commit
MySQL [test]> select * from student;
+----+----------+-----+
| id | name     | age |
+----+----------+-----+
|  1 | clark    |  30 |
|  2 | zhangsan |  22 |
|  3 | lisi     |  28 |
+----+----------+-----+
3 rows in set (0.00 sec)

MySQL [test]> begin;
Query OK, 0 rows affected (0.00 sec)

MySQL [test]> update student set age = 29 where id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
#Conversation 2
MySQL [test]> set session transaction isolation level read uncommitted;
Query OK, 0 rows affected (0.00 sec)

MySQL [test]> 
MySQL [test]> 
MySQL [test]> select @@tx_isolation;
+------------------+
| @@tx_isolation   |
+------------------+
| READ-UNCOMMITTED |
+------------------+
1 row in set, 1 warning (0.00 sec)

MySQL [test]> select * from student;
+----+----------+-----+
| id | name     | age |
+----+----------+-----+
|  1 | clark    |  29 |
|  2 | zhangsan |  22 |
|  3 | lisi     |  28 |
+----+----------+-----+
3 rows in set (0.01 sec)

The uncommitted transactions of session 1 can be seen in session 2, which is read uncommitted. Dirty read will occur if the read is not committed (once session 1 transaction is rolled back)

2. Read committed: the current transaction can only read data submitted by another transaction, which is called non repeatable read because it causes non repeatable read.

For example:

#Conversation 1
MySQL [test]> set session transaction isolation level read committed;
Query OK, 0 rows affected (0.01 sec)

MySQL [test]> begin;
Query OK, 0 rows affected (0.01 sec)

MySQL [test]> select * from student;
+----+----------+-----+
| id | name     | age |
+----+----------+-----+
|  1 | clark    |  33 |
|  2 | zhangsan |  22 |
|  3 | lisi     |  28 |
|  0 | wangwu   |  45 |
|  0 | zhaoliu  |  22 |
+----+----------+-----+
5 rows in set (0.01 sec)

MySQL [test]> update student set age = 25 where id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MySQL [test]> commit;
Query OK, 0 rows affected (0.01 sec)
#Conversation 2
MySQL [test]> set session transaction isolation level read committed;
Query OK, 0 rows affected (0.00 sec)

MySQL [test]> begin;
Query OK, 0 rows affected (0.01 sec)

MySQL [test] > select * from student; // session (transaction) 1 -- before commit 
+----+----------+-----+
| id | name     | age |
+----+----------+-----+
|  1 | clark    |  33 |
|  2 | zhangsan |  22 |
|  3 | lisi     |  28 |
|  0 | wangwu   |  45 |
|  0 | zhaoliu  |  22 |
+----+----------+-----+
5 rows in set (0.01 sec)

MySQL [test] > select * from student; // session (transaction) 1 -- after commit
+----+----------+-----+
| id | name     | age |
+----+----------+-----+
|  1 | clark    |  25 |
|  2 | zhangsan |  22 |
|  3 | lisi     |  28 |
|  0 | wangwu   |  45 |
|  0 | zhaoliu  |  22 |
+----+----------+-----+
5 rows in set (0.01 sec)

It can be noted that in the same transaction session 2, the same select statement gets different results, with age equal to 30 once and age equal to 29 once. So there is no repeat reading

3. Repeatable read

A transaction can only see the same data through the first statement, even if another transaction has committed data. In the same transaction, a read is consistent in establishing a snapshot by the first read. With one exception, a transaction can read data changed in the same transaction.

When a transaction starts and the first read of data is performed, the read view is created and remains open until the end of the transaction. To provide the same result set before the end of a transaction, InnoDB uses row versioning and undo information. Suppose transaction 1 selects several rows and another transaction deletes them and commits the data. If transaction 1 is open, it should be able to see the row it selected at the beginning. Rows that have been deleted remain in the undo log space to fulfill transaction 1. Once the transaction 1 operation is completed, those rows are marked for deletion from the undo log. This is called multi version concurrency control (mvcc).

For example:

#Conversation 1
MySQL [test]> set session transaction isolation level repeatable read;
Query OK, 0 rows affected (0.00 sec)

MySQL [test]> 
MySQL [test]> 
MySQL [test]> 
MySQL [test]> begin;
Query OK, 0 rows affected (0.00 sec)

MySQL [test]> select * from student;
+----+----------+-----+
| id | name     | age |
+----+----------+-----+
|  1 | clark    |  25 |
|  2 | zhangsan |  22 |
|  3 | lisi     |  28 |
|  0 | wangwu   |  45 |
|  0 | zhaoliu  |  22 |
+----+----------+-----+
5 rows in set (0.01 sec)

MySQL [test]> update student set age = 27 where id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MySQL [test]> commit;
Query OK, 0 rows affected (0.00 sec)
#Conversation 2
MySQL [test]> set session transaction isolation level repeatable read;
Query OK, 0 rows affected (0.01 sec)

MySQL [test]> begin;
Query OK, 0 rows affected (0.01 sec)

MySQL [test] > select * from student; // session (transaction) 1 before commit
+----+----------+-----+
| id | name     | age |
+----+----------+-----+
|  1 | clark    |  25 |
|  2 | zhangsan |  22 |
|  3 | lisi     |  28 |
|  0 | wangwu   |  45 |
|  0 | zhaoliu  |  22 |
+----+----------+-----+
5 rows in set (0.01 sec)

MySQL [test] > select * from student; // after session (transaction) 1 commit
+----+----------+-----+
| id | name     | age |
+----+----------+-----+
|  1 | clark    |  25 |
|  2 | zhangsan |  22 |
|  3 | lisi     |  28 |
|  0 | wangwu   |  45 |
|  0 | zhaoliu  |  22 |
+----+----------+-----+
5 rows in set (0.01 sec)

Repeatable reading ensures that in a transaction, no matter how to check, the result is consistent, and it is not affected by whether other transactions commit or not. As can be seen from the above example, the query result of transaction 2 remains unchanged before and after transaction 1 is submitted.

This is what we said earlier: when a transaction starts and executes, the first time it reads data, the read view is created and remains open until the end of the transaction. To provide the same result set before the end of the transaction.

From the above results, it can be seen that repeatable reading will produce unreal reading. From the top, dirty reading, non repeatable reading and unreal reading are a bit similar. What’s the difference?

Dirty readingIt refers to a transactionreadI got itOther transactions were not committedData from,
Not repeatableIt refers to a transaction that is queried multiple times according to the same query conditionSame row recordThe value of is different
Illusory readingIt refers to a transaction that is found multiple times according to the same conditionNumber of record linesdissimilarity

Do you understand the differences? If you don’t understand, you can refine the bold font

Because repeatable reads produce unreal reads, there is a fourth level of transaction isolation: serializable

Serialization provides the highest level of isolation by locking all selected rows. This level is similar to repeatable read, but if autocommit is disabled, InnoDB implicitly converts all normal select statements to select Lock share mode; if autocommit is enabled, select is its own transaction, and serialization will wait for the locked row and always read the latest committed data.

Serialization, as the name suggests, is to completely serialize all read and write operations.
Serialization is the highest of all isolation levels
Every time you read, you need to obtain a table level shared lock, and read and write will block each other
Serialization costs a lot of resources and does not support concurrency well. It is only used in some scenarios.

Specific examples are not detailed, you can Google yourself

So much has been said above. Now to sum up, the following is a comparison table of each isolation level:

Isolation level Dirty reading Not repeatable Illusory reading
Read Uncommitted probably probably probably
Read Committed impossible probably probably
Repeatable Read impossible impossible probably
Serializable impossible impossible impossible

Recommended Today

DK7 switch’s support for string

Before JDK7, switch can only support byte, short, char, int or their corresponding encapsulation classes and enum types. After JDK7, switch supports string type. In the switch statement, the value of the expression cannot be null, otherwise NullPointerException will be thrown at runtime. Null cannot be used in the case clause, otherwise compilation errors will […]