What is acid in mysql-Mysql Tutorial-php.cn

This article will introduce to you acid in mysql. It has certain reference value. Friends in need can refer to it. I hope it will be helpful to everyone.

What is acid in mysql

1. Basic elements of transaction (ACID)

1. Atomicity: all operations after the transaction starts, Either do it all, or don’t do it at all. It’s impossible to get stuck in the middle. If an error occurs during transaction execution, it will be rolled back to the state before the transaction started, and all operations will be as if they did not happen. That is to say, affairs are an indivisible whole, just like atoms learned in chemistry, which are the basic units of matter.

2. Consistency: Before and after the transaction starts and ends, the integrity constraints of the database are not destroyed. For example, if A transfers money to B, it is impossible for A to deduct the money but B not to receive it. In fact, consistency is also a manifestation of atomicity

3. Isolation: At the same time, only one transaction is allowed to request the same data, and there is no interference between different transactions. For example, A is withdrawing money from a bank card. B cannot transfer money to this card before A's withdrawal process is completed. Serialization

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

2. Transaction concurrency issues

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

2. Non-repeatable reading: Transaction A reads the same data multiple times, and transaction B reads the same data multiple times in transaction A. The data was updated and submitted, resulting in inconsistent results when transaction A read the same data multiple times. The reading result is inconsistent with the last result

3. Phantom reading: System administrator A changed the grades of all students in the database from specific scores to ABCDE grades, but system administrator B inserted a record at this time Regarding the specific score records, when system administrator A completed the modification, he found that there was still one record that had not been modified. It was like an hallucination. This is called phantom reading. It was modified but then changed, resulting in a different result than expected

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

3. MySQL transaction isolation level

Transaction isolation level	Dirty read	Non-repeatable read	Phantom read
Read uncommitted (read- uncommitted)	Yes	Yes	Yes
read-committed	No	Yes	Yes
Repeatable-read	No	No	Yes
Serializable	No	No	No

The default transaction isolation level of mysql is repeatable-read

##4. Use examples to illustrate each isolation level

1. Read uncommitted:

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

　　　　(2) Before client A's transaction is committed, open another client B and update the table account:

　　　　　(3) At this time , although client B's transaction has not yet been submitted, client A can query B's updated data:

　 (4) Once client B The transaction is rolled back for some reason, and all operations will be revoked. Then the data queried by client A is actually dirty data:

　　 (5) In Client A executes the update statement update account set balance = balance - 50 where id =1. lilei's balance did not become 350, but actually 400. Isn't it strange? The data is inconsistent. If you think so, you are too naive. In the application, we will use 400-50=350, and we don’t know that other sessions have been rolled back. To solve this problem, we can use the read-committed isolation level

2. Read committed

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

　　　　　　(2) Before client A's transaction is committed, open another client B and update the table account:

# Client B's transaction has not yet been submitted. Client A cannot query B's updated data, which solves the dirty read problem:

　 (4) Client B's transaction Submit

　(5) Client A executes the same query as the previous step, and the result is inconsistent with the previous step, that is, a non-repeatable read problem occurs

3. Repeatable read

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

　　　　(2) Before client A's transaction is submitted, open another client B, update the table account and submit

　　　 (3) Before the client End A executes the query of step (1):

　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　aticbility A non-repeatable read problem occurs; then execute update balance = balance - 50 where id = 1, and the balance does not become 400-50=350. The balance value of lilei is calculated using the 350 in step (2), so it is 300, the consistency of the data has not been destroyed. This is a bit magical. Maybe it is a feature of mysql. When doing dml, the data can be read repeatedly 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 (0.00 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: 0

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

Copy after login

　(5) On client A Submit the transaction and query the initial value of 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)

Copy after login

rrree

[] When client A calculates the sum of balance, the value is 300 16000 2400 = 18700. The value of client B is not included. After client A submits, the sum of balance is calculated and it turns out to be 19300. This is because the value of client B is calculated. B’s 600 is included

，站在客户的角度，客户是看不到客户端B的，它会觉得是天下掉馅饼了，多了600块，这就是幻读，站在开发者的角度，数据的一致性并没有破坏。但是在应用程序中，我们得代码可能会把18700提交给用户了，如果你一定要避免这情况小概率状况的发生，那么就要采取下面要介绍的事务隔离级别“串行化”

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 (0.00 sec)

Copy after login

　　4.串行化

　　　　（1）打开一个客户端A，并设置当前事务模式为serializable，查询表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 (0.00 sec)

Copy after login

　　　　（2）打开一个客户端B，并设置当前事务模式为serializable，插入一条记录报错，表被锁了插入失败，mysql中事务隔离级别为serializable时会锁表，因此不会出现幻读的情况，这种隔离级别并发性极低，开发中很少会用到。

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(5,&#39;tom&#39;,0);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

Copy after login

补充：

　　1、SQL规范所规定的标准，不同的数据库具体的实现可能会有些差异

　　2、mysql中默认事务隔离级别是可重复读时并不会锁住读取到的行

　　3、事务隔离级别为读提交时，写数据只会锁住相应的行

　　4、事务隔离级别为可重复读时，如果有索引（包括主键索引）的时候，以索引列为条件更新数据，会存在间隙锁间隙锁、行锁、下一键锁的问题，从而锁住一些行；如果没有索引，更新数据时会锁住整张表。

　　5、事务隔离级别为串行化时，读写数据都会锁住整张表

　　 6、隔离级别越高，越能保证数据的完整性和一致性，但是对并发性能的影响也越大，鱼和熊掌不可兼得啊。对于多数应用程序，可以优先考虑把数据库系统的隔离级别设为Read Committed，它能够避免脏读取，而且具有较好的并发性能。尽管它会导致不可重复读、幻读这些并发问题，在可能出现这类问题的个别场合，可以由应用程序采用悲观锁或乐观锁来控制。