1. Four characteristics of transactions
A transaction is an integral unit of one or more SQL statements. A set of SQL statements in a transaction either succeed or fail. Four properties ACID
Atomic atomic
consistency
Isolation for concurrency in the database, the better the isolation, the lower the concurrency. It is based on innodb's locking mechanism
durability: redo log to ensure data persistence. In extreme cases, after commit, the server goes down and the data has not been persisted to disk, so you need to rely on redo log.
ACD relies on redo and undo logs. I relies on the locking mechanism.
2. Concurrent problems
Take the user table as an example.
select * from user; +----+----------+-----+-----+ | id | name | age | sex | +----+----------+-----+-----+ | 1 | zhangsan | 22 | M | | 2 | lisi | 22 | W | +----+----------+-----+-----+ 2 rows in set (0.00 sec)
1. Dirty reading
//Set the isolation level to read uncommitted mysql> set tx_isolation='READ-UNCOMMITTED'; Query OK, 0 rows affected, 1 warning (0.00 sec) affair A A1,mysql> begin; Query OK A2,mysql> update user set age=30 where name='zhangsan'; Query OK A3,mysql> rollback; Query OK affair B B1,mysql> begin; Query OK B2,mysql> select * from user where name='zhangsan'; +----+----------+-----+-----+ | id | name | age | sex | +----+----------+-----+-----+ | 1 | zhangsan | 22 | M | +----+----------+-----+-----+ B3,mysql> select * from user where name='zhangsan'; +----+----------+-----+-----+ | id | name | age | sex | +----+----------+-----+-----+ | 1 | zhangsan | 30 | M | +----+----------+-----+-----+ When transaction A，affair B All open transactions, First transaction B Query, executed B2，The results are as follows. affair A Start update，implement A2， affair B Query again and execute B3，Get the result, and the result needs to use the corresponding business. affair A Rollback in progress, not in progress commit. And affairs B yes A Uncommitted business has been used.
2. Non repeatable reading
//Set the isolation level to read committed; set tx_isolation='READ-COMMITTED'; Query OK, 0 rows affected, 1 warning (0.00 sec) affair A A1,mysql> begin; Query OK, 0 rows affected (0.00 sec) A2,mysql> update user set age=30 where name='zhangsan'; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 A3,mysql> commit; Query OK, 0 rows affected (0.00 sec) affair B begin; Query OK, 0 rows affected (0.00 sec) B1 ,mysql> select * from user where name='zhangsan'; +----+----------+-----+-----+ | id | name | age | sex | +----+----------+-----+-----+ | 1 | zhangsan | 22 | M | +----+----------+-----+-----+ 1 row in set (0.00 sec) B2,mysql> select * from user where name='zhangsan'; +----+----------+-----+-----+ | id | name | age | sex | +----+----------+-----+-----+ | 1 | zhangsan | 22 | M | +----+----------+-----+-----+ 1 row in set (0.00 sec) B3,mysql> select * from user where name='zhangsan'; +----+----------+-----+-----+ | id | name | age | sex | +----+----------+-----+-----+ | 1 | zhangsan | 30 | M | +----+----------+-----+-----+ 1 row in set (0.00 sec) mysql> commit; Query OK, 0 rows affected (0.00 sec) As above, in business A，implement A2，conduct updata after affair B Query again and execute B2，The query result is the same as before modification. When transaction A commit After the transaction B Query again and execute B3，Get the updated results.
3. Unreal reading
In many blogs, it is said that unreal reading is that transaction A adds or deletes the table, and then in transaction B, when you select, you will directly find the changes. After many experiments, I feel that this is A little inaccurate. You can see the following description.
Set the isolation level to repeatable read. This too innoDB Default isolation level.
mysql> select @@tx_isolation;
+-----------------+
| @@tx_isolation |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set, 1 warning (0.00 sec)
First, repeatable reading can solve the problems of dirty reading and non repeatable reading,
For solving the problem of non repeatable reading.
affair B In not commit Before the transaction, regardless of the transaction A How update，
affair B Metropolis and affairs A begin The result is the same as before.
Take a look at fantasy reading:
affair A:
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
Ａ1 mysql> insert into user(name, age, sex) values('aaa', 19, 'M');
Query OK, 1 row affected (0.00 sec)
A2 mysql> select * from user where age=19;
+----+------+-----+-----+
| id | name | age | sex |
+----+------+-----+-----+
| 15 | aaa | 19 | M |
+----+------+-----+-----+
1 row in set (0.00 sec)
A3 mysql> commit;
Query OK, 0 rows affected (0.00 sec)
affair B:
B1 mysql> begin;
Query OK, 0 rows affected (0.00 sec)
affair A insert Before, not commit
B2 mysql> select * from user where age=19;
Empty set (0.00 sec)
affair A insert After, not commit
B3 mysql> select * from user where age=19;
Empty set (0.01 sec)
affair A commit After, the query result is still empty.
B4 mysql> select * from user where age=19;
Empty set (0.00 sec)
But at this time, there has been unreal reading, but I still can't see it. I need to read it update.
B5 mysql> update user set name='aaa' where age=19;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1 Changed: 0 Warnings: 0
This is fantasy reading. The results are different up and down,
B6 mysql> select * from user where age=19;
+----+------+-----+-----+
| id | name | age | sex |
+----+------+-----+-----+
| 15 | aaa | 19 | M |
+----+------+-----+-----+
1 row in set (0.00 sec)
B7 mysql> commit;
Query OK, 0 rows affected (0.00 sec)
3. Isolation level
MySQL supports four isolation levels:
- 1,TRANSACTION_READ_UNCOMMITTED.
Read uncommitted. This indicates that one transaction can see the change of another transaction before committing. In this way, it is allowed to read dirty data, non repeatable reading and virtual reading.
- 2,TRANSACTION_READ_COMMITTED.
Read committed. Description reading uncommitted data is not allowed. This level still allows non repeatable reads and virtual reads.
- 3,TRANSACTION_REPEATABLE_READ.
Repeatable. This indicates that the transaction guarantees that the same data can be read again without failure, but virtual reading will still occur.
- 4,TRANSACTION_SERIALIZABLE.
Serialization. Is the highest transaction level, which prevents reading dirty data, non repeatable and virtual reads.
Serialization is the highest isolation level. After transaction A and transaction B are started, they can no longer be inserted or deleted.
Unreal reading in repeatable reading. insert or delete will not produce unreal reading without update.
4. Common statements
Query isolation level:
select @@tx_isolation;
Set isolation level:
set tx_isolation='Isolation level';
There are four selectable isolation levels:
read-uncommitted read-committed repeatable-read serializable
set tx_isolation='read-uncommitted';