The focus of this article is to summarize MYSQL transactions.
What is a transaction
A transaction is simply a set of SQL executions that either all succeed or all fail. MYSQL transactions are implemented at the storage engine layer.
Transactions have ACID properties:
- Atomicity: A transaction must be treated as an indivisible unit;
- Consistency: the database always switches from one state to another;
- Isolation: Normally, transactions are not visible to other transactions until committed;
- Durablity: once a transaction is committed, the changes are persisted in the database permanently;
The most common example of a transaction is a bank transfer. Assume that Polo needs to transfer 1000 yuan to Lynn, as follows:
- Confirm that the polo account balance is more than 1000 yuan;
- Subtract 1000 yuan from polo’s account balance;
- Increase the balance of Lynn’s account by 1000 yuan;
The SQL statement is as follows:
mysql> BEGIN;
mysql> SELECT balance FROM bank_account WHERE uid=10001;
mysql> UPDATE bank_account SET balance=balance-1000 WHERE uid=10001;
mysql> UPDATE bank_account SET balance=balance+1000 WHERE uid=10002;
mysql> COMMIT;
Copy the codeMysql can START a TRANSACTION with BEGIN or START TRANSACTION. The above three steps guarantee data integrity in a single transaction, and all of them either succeed or fail.
MYSQL provides two transactional engines: Innodb and NDBCluster. By default, automatic COMMIT mode is used. A statement is executed to automatically COMMIT. The AUTOCOMMIT variable enables or disables automatic commit mode:
mysql> SHOW VARIABLES LIKE "AUTOCOMMIT"; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | autocommit | ON | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- + 1 row in the set (0.00 SEC) mysql > set the AUTOCOMMIT mode = 1Copy the codeAUTOCOMMIT=1 indicates that default commit is enabled. 0 indicates that default commit is disabled and manual commit is required.
Isolation level
Explanation of transaction isolation: Typically, transactions are not visible to other transactions until they are committed.
There are four isolation levels for databases, and of course MYSQL is the same. Respectively is:
- READ UNCOMMITTED
- READ COMMITTED
- REPEATABLE READ
- Serialization, SEAIALIZABLE
Two understandings about isolation levels
- As the book explains, each level specifies which changes are made within a transaction and which are visible within and between transactions.
- As I understand it, the isolation level determines when changes made to one transaction are visible to another.
The difference is whether there is intra-transaction visibility, but the operations within a transaction are visible at any level, with the emphasis on inter-transaction visibility.
MYSQL > create isolation levels for MYSQL > create isolation levels for MYSQL > create isolation levels
mysql> CREATE TABLE `student` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(32) NOT NULL DEFAULT '',
PRIMARY KEY (`id`)
) DEFAULT CHARSET=utf8 |
Copy the codeThere are only ID (primary key increment) and name fields
Uncommitted read
Changes in a transaction that are not committed are also visible to other transactions, commonly known as dirty reads. Very not recommended.
The example demonstrates that clients A and B set the isolation level to uncommitted reads
mysql> SET SESSION TX_ISOLATION='READ-UNCOMMITTED';
Copy the codeClients A and B start the transaction and query student
Mysql > BEGIN; mysql> SELECT * FROM student; The Empty set (0.00 SEC)Copy the codeCurrently, both clients A and B have empty data. Insert a new piece of data at customer side B
mysql> INSERT INTO student(name) VALUES("polo");
Query OK, 1 row affected (0.00 sec)
Copy the codeThe transaction has not yet been committed, so client A checks the student table as follows:
mysql> SELECT * FROM student; + - + -- -- -- -- -- -- + | | id name | + - + -- -- -- -- -- -- + | | 1 polo | + + -- -- -- -- -- -- - + 1 row in the set (0.00 SEC)Copy the codeAs you can see, client A sees B’s uncommitted changes. Client B performs the rollback as follows:
mysql> ROLLBACK
Copy the codeAfter success, client A looks at the student table:
mysql> SELECT * FROM student; The Empty set (0.00 SEC)Copy the codeThe output shows that client A’s view data is empty.
The above illustrates the dangers of uncommitted read isolation levels. Changes made to one uncommitted transaction are visible to another transaction and can easily lead to dirty reads. Do not use this level except in special circumstances
Has been submitted to read
Most database systems default to this level (MYSQL does not). Committed read level means that a transaction can only see the changes made by committed transactions, which solves the problem of uncommitted reads, that is, dirty reads.
To demonstrate that clients A and B set the isolation level to committed read, run the following command:
mysql> SET SESSION TX_ISOLATION='READ-COMMITTED';
Copy the codeClients A and B start the transaction and query student
mysql> BEGIN; mysql> SELECT * FROM student; The Empty set (0.00 SEC)Copy the codeThe result shows that both clients A and B are empty. Next, client B inserts a new piece of data but does not commit:
mysql> INSERT INTO student (name) VALUES('polo');
Copy the codeNext, client A looks at the student data:
mysql> SELECT * FROM student; The Empty set (0.00 SEC)Copy the codeNote that here, unlike above, there is no data in case client B has not committed the transaction. Client B commits the transaction:
mysql> COMMIT;
Copy the codeClient A checks the student table again.
mysql> SELECT * FROM student; + - + -- -- -- -- -- -- + | | id name | + - + -- -- -- -- -- -- + | | 1 polo | + + -- -- -- -- -- -- - + 1 row in the set (0.00 SEC)Copy the codeSo we have successfully read the customer.
As you can see from the above example, committed reads do not have the dirty read problem, but we can see that the same SELECT statement is executed twice in A transaction on client A with different results, so committed reads are also called unrepeatable reads. The same screening conditions may get different results.
Repeatable read
As the name suggests, solve the committed read non-repeatable read problem.
The example demonstrates that clients A and B set the isolation level to repeatable reads. First set the isolation level:
mysql> SET SESSION tx_isolation='REPEATABLE-READ'
Copy the codeClients A and B start the transaction and view it
mysql> BEGIN; mysql> SELECT * FROM student; + - + -- -- -- -- -- -- + | | id name | + - + -- -- -- -- -- -- + | | 1 polo | + + -- -- -- -- -- -- - + 1 rows in the set (0.00 SEC)Copy the codeClient B updates Polo to Adam and commits the transaction
mysql> UPDATE student SET name='adam' WHERE id=1;
mysql> COMMIT
Copy the codeClient A checks the student table as follows:
mysql> SELECT * FROM student; + - + -- -- -- -- -- -- + | | id name | + - + -- -- -- -- -- -- + | | 1 polo | + + -- -- -- -- -- -- - + 1 rows in the set (0.00 SEC)Copy the codeThe data on client A is not changed and there is no unrepeatable read problem.
Client A commits the transaction and looks at the Student table.
mysql> COMMIT; mysql> SELECT * FROM student; + - + -- -- -- -- -- -- + | | id name | + - + -- -- -- -- -- -- + | | 1 polo | + + -- -- -- -- -- -- - + 1 rows in the set (0.00 SEC)Copy the codeAs you can see from the example above, the same content can be read twice. This level does not solve the illusion problem. However, MYSQL has added MVCC to repeatable reads to solve this problem. The effect of phantom reads cannot be demonstrated here.
So what is illusory? First, the repeatable read lock scope is for the current query, such as execution
mysql> SELECT * FROM student WHERE id>=1
Copy the codeThe locked row is the row whose ID >=1. As another transaction executes and commits the following statement by default
mysql> INSERT INTO student (name) VALUES ('stephen');
Copy the codeThe new row is not locked. Student is now read
mysql> SELECT * FROM student WHERE id>=1; + - + -- -- -- -- -- -- -- -- -- + | | id name | + - + -- -- -- -- -- -- -- -- -- + | 1 | polo | | 2 | Stephen | + - + -- -- -- -- -- -- -- -- -- + 2 rows in the set (0.00 SEC)Copy the codePhantom reading appears. In addition to using MYSQL’s MVCC mechanism, you can solve this problem with serializable isolation levels.
serialization
Serialization is the highest isolation level and forces transactions to be executed serially. This level is only used when data consistency is critical and concurrency is not used.
The example demonstrates that clients A and B set the isolation level to serializable.
mysql> SET SESSION tx_isolation='SERIALIZABLE';
Copy the codeFirst, client A performs A query:
mysql> SELECT * FROM student WHERE id<4; + - + -- -- -- -- -- -- -- -- -- + | | id name | + - + -- -- -- -- -- -- -- -- -- + | 1 | polo | | 2 | Stephen | + - + -- -- -- -- -- -- -- -- -- + 2 rows in the set (0.00 SEC)Copy the codeNext, client B performs data addition:
mysql> INSERT INTO student (name) VALUES(‘yunteng’); Ok! INSERT statement is blocked because student (id<4) is locked. SQL > select student from student where id<3;
Summary figure
| Isolation level | English | Dirty read | Unrepeatable read | Phantom read | Read lock |
|---|---|---|---|---|---|
| Uncommitted read | READ UNCOMMITTED | is | is | is | no |
| Submit to read | READ COMMITTED | no | is | is | no |
| Repeatable read | REPEATABLE READ | no | no | is | no |
| serialization | SERIALIZABLE | no | no | no | is |
This is the end of the isolation level for transactions. The exact usage depends on the specific business scenario.