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

This article mainly introduces what acid refers to in mysql, has a certain reference value, interested friends can refer to, I hope you can learn a lot after reading this article, the following let the editor take you to understand it.

1. The basic elements of a transaction (ACID)

1. Atomicity: after the transaction starts, either all operations are done or none are done, and it is impossible to stay in the middle. When an error occurs during the execution of the transaction, it is rolled back to the state before the start of the transaction, and all operations are as if they had not occurred. In other words, affairs are an indivisible whole, just like atoms learned in chemistry, are the basic units of matter.

2. Consistency: before and after the transaction starts, the integrity constraints of the database are not broken. For example, if A transfers money to B, it is impossible for A to deduct the money, but B does not receive it. In fact, consistency is also due to a manifestation of the atomic type.

3. Isolation: only one transaction is allowed to request the same data at a time, and there is no interference between different transactions. For example, An is withdrawing money from a bank card, and B cannot transfer money to this card until the withdrawal process of An is over. Serialization

4. Durability: after the transaction is completed, all updates made by the transaction to the database will be saved to the database and cannot be rolled back.

Second, the concurrency of transactions

1. Dirty reading: transaction A reads the data updated by transaction B, and then B rolls back the operation, then the data read by An is dirty data, which is inconsistent with the final actual data in the table.

2. Non-repeatable reading: transaction A reads the same data many times, and transaction B updates and commits the data in the process of reading the same data many times, resulting in inconsistent results when transaction A reads the same data many times. The reading result is inconsistent with the last result.

3, illusory reading: system administrator A changes the scores of all students in the database from specific scores to ABCDE grades, but system administrator B inserts a record of specific scores at this time. When system administrator A changes to the end, he finds that there is a record that has not been changed, as if there were hallucinations, which is called phantom reading. It was modified but changed again, resulting in a different result than expected.

Summary: non-repeatable reading and phantom reading are easy to be confused, non-repeatable reading focuses on modification, and phantom reading focuses on adding or deleting. To solve the problem of unrepeatable reading, you only need to lock the rows that meet the conditions, and to solve the phantom reading, you need to lock the table.

III. MySQL transaction isolation level

Transaction isolation level dirty read non-repeatable read uncommitted (read-uncommitted) is read committed (read-committed) is committed (repeatable-read) is serialized (serializable) No

The default transaction isolation level for mysql is repeatable-read

Fourth, use examples to illustrate the situation of each isolation level.

1. Read unsubmitted:

(1) Open a client A, set the current transaction mode to read uncommitted (uncommitted read), and query the initial value of the table account:

(2) before the transaction of client A commits, open another client B and update the table account:

(3) at this time, although the transaction of client B has not been committed, client A can query the updated data of B:

(4) once the transaction of client B is rolled back for some reason, all operations will be undone, and the data queried by client An is actually dirty data:

(5) execute the update statement update account set balance = balance-50 where id = 1 on client A. The balance of Lilei is not 350, but 400. Isn't it strange that the data are inconsistent? it would be naive if you think so. In the application, we will use 400-50 sessions 350, not knowing that other sessions have been rolled back. To solve this problem, you can read the submitted isolation level.

2. Read submitted

(1) Open a client A, set the current transaction mode to read committed (uncommitted read), and query the initial value of the table account:

(2) before the transaction of client A commits, open another client B and update the table account:

(3) at this time, the transaction of client B has not been committed, and client A cannot query the updated data of B, which solves the problem of dirty reading:

(4) transaction submission of client B

(5) client An executes the same query as the previous step, and the result is inconsistent with the previous step, that is, the problem of non-repeatable reading occurs.

3. Repeatable

(1) Open a client A, set the current transaction mode to repeatable read, and query table account

(2) before the transaction of client A commits, open another client B, update table account and commit

(3) perform the query in step (1) on client A:

(4) execute step (1). The balance of lilei is still 400. the query result of step (1) is the same, and there is no problem of non-repeatable reading. Then execute update balance = balance-50 where id = 1 the balance is not changed to 400-50, 350, the balance value of Lilei is calculated by 350 in step (2), so it is 300, the consistency of the data has not been broken, this is a bit magical, maybe it is the characteristic of mysql, when doing dml, the repeatable data is still calculated according to the real data in the table.

Mysql > select * from account +-+ | id | name | balance | +-+ | 1 | lilei | 400 | | 2 | hanmei | 16000 | 3 | lucy | 2400 | +-+ 3 rows in set Sec) mysql > update account set balance = balance-50 where id = 1 Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0mysql > select * from account +-+ | id | name | balance | +-+ | 1 | lilei | 300 | 2 | hanmei | 16000 | 3 | lucy | 2400 | +-+ 3 rows in set (0.00 sec)

(5) submit the transaction in client An and query the initial value of the table account

Mysql > commit;Query OK, 0 rows affected (0.00 sec) mysql > select * from account +-+ | id | name | balance | +-+ | 1 | lilei | 300 | 2 | hanmei | 16000 | 3 | lucy | 2400 | +-+ 3 rows in set (0.00 sec)

(6) start the transaction in client B, add a new data, in which the balance field value is 600, and submit

Mysql > start transaction;Query OK, 0 rows affected (0.00 sec) mysql > insert into account values; Query OK, 1 row affected (0.00 sec) mysql > commit;Query OK, 0 rows affected (0.01 sec)

(7) when client A calculates the sum of balance, the value is 300,16000,2400,18700, and the value of client B is not taken into account. Client A submits and then calculates the sum of balance, but it becomes 19300. This is because the sum of client B is counted.

From the customer's point of view, the customer can not see client B, it will feel that the world has lost its pie, more than 600 yuan, this is illusory reading, from the developer's point of view, the consistency of the data has not been broken. But in the application, our code may commit 18700 to the user, and if you must avoid this low probability situation, you should adopt the transaction isolation level "serialization" described below.

Mysql > select sum (balance) from account;+-+ | sum (balance) | +-+ | 18700 | +-+ 1 row in set (0.00 sec) mysql > commit; Query OK, 0 rows affected (0.00 sec) mysql > select sum (balance) from account +-+ | sum (balance) | +-+ | 19300 | +-+ 1 row in set (sec)

4. Serialization

(1) Open a client A, set the current transaction mode to serializable, and query the initial value of the table account:

Mysql > set session transaction isolation level serializable;Query OK, 0 rows affected (0.00 sec) mysql > start transaction;Query OK, 0 rows affected (0.00 sec) mysql > select * from account +-+ | id | name | balance | +-+ | 1 | lilei | 10000 | | 2 | hanmei | 10000 | 3 | lucy | 10000 | | 4 | lily | 10000 | +-+ 4 rows in set (10000 sec)

(2) Open a client B, and set the current transaction mode to serializable, insert a record and report an error, and the table is locked and failed to insert. When the transaction isolation level in mysql is serializable, the table will be locked, so there will be no illusory reading. The concurrency of this isolation level is very low, and it is rarely used in development.

Mysql > set session transaction isolation level serializable;Query OK, 0 rows affected (0.00 sec) mysql > start transaction;Query OK, 0 rows affected (0.00 sec) mysql > insert into account values; ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

Add:

1. There may be some differences in the specific implementation of different databases according to the standards stipulated in the SQL specification.

2. The default transaction isolation level in mysql is repeatable and the rows read will not be locked.

3. When the transaction isolation level is read commit, the write data will only lock the corresponding rows.

4. When the transaction isolation level is repeatable, if there is an index (including the primary key index) and the data is updated with the index as a condition, there will be the problem of gap lock, row lock and next key lock, which will lock some rows; if there is no index, the whole table will be locked when the data is updated.

5. When the transaction isolation level is serialized, both read and write data will lock the entire table

6. The higher the isolation level, the more you can ensure the integrity and consistency of the data, but the greater the impact on concurrent performance, you can't have both. For most applications, priority can be given to setting the isolation level of the database system to Read Committed, which can avoid dirty reads and has good concurrency performance. Although it can lead to concurrency problems such as unrepeatable reading and phantom reading, it can be controlled by the application using pessimistic or optimistic locks in individual situations where such problems may occur.

Thank you for reading this article carefully. I hope the article "what does acid in mysql refer to" shared by the editor will be helpful to everyone? at the same time, I also hope that you will support us and pay attention to the industry information channel. More related knowledge is waiting for you to learn!

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.