affair
Database transaction (abbreviated as transaction) is a logical unit in the execution of database management system, which consists of a limited sequence of database operations.
Generally speaking, database transactions are characterized by the following four characteristics:
- Atomicity: Transactions are executed as a whole, and operations on the database contained therein are either fully executed or not executed.
- Consistency: Transactions should ensure that the state of the database changes from one consistent state to another. The meaning of consistent state is that data in database should satisfy integrity constraints.
- Isolation: When multiple transactions are executed concurrently, the execution of one transaction should not affect the execution of other transactions.
- Durability: Changes made to the database by committed transactions should be permanently stored in the database.
Transaction control statement
- BEGIN or START TRANSACTION opens a transaction explicitly.
- COMMIT; COMMIT WORK can also be used, but both are equivalent. COMMIT commits transactions and makes all changes to the database permanent.
- ROLLBACK; some can use ROLLBACK WORK, but both are equivalent. Rollback terminates the user's transaction and undoes all ongoing uncommitted changes.
- SAVEPOINT identifier; SAVEPOINT allows you to create a savepoint in a transaction that can have more than one SAVEPOINT.
- RELEASE SAVEPOINT identifier; Deletes the savepoint of a transaction and throws an exception when there is no designated savepoint.
- ROLLBACK TO identifier; rolls back transactions to markup points.
- SET TRANSACTION; used to set the isolation level of transactions. InnoDB storage engine provides transaction isolation levels such as READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ and SERIA LIZABLE.
Two methods of mysql transaction processing:
-
Using BEGIN, ROLLBACK, COMMIT to achieve:
a. BEGIN Starts a Transaction
b. ROLLBACK transaction rollback
c. COMMIT transaction confirmation -
Open MySQL's manual submission mode:
SET AUTOCOMMIT=0 prohibits automatic submission
SET AUTOCOMMIT=1 Turns on automatic submissionUnder the default setting of the MySQL command line, transactions are automatically committed, that is, COMMIT operations are performed immediately after executing the SQL statement.
Example:
-
Method 1:
Take bank transfers as an example.mysql> CREATE TABLE `account` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `bankNo` varchar(20), `username` varchar(40), `money` float, PRIMARY KEY (`id`)); mysql> INSERT INTO `account` VALUES(1, '110', 'Zhang San', 10000); mysql> INSERT INTO `account` VALUES(2, '120', 'Li Si', 1000); # Create a new account table and add data. mysql> START TRANSACTION; Query OK, 0 rows affected (0.00 sec) # Zhang San Transfer 200 to Li Si mysql> UPDATE account SET money = money - 200 WHERE bankNo = 110; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> UPDATE account SET money = money + 200 WHERE bankNo = 120; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 #Submission of affairs mysql> COMMIT; Query OK, 0 rows affected (0.01 sec) #Check the transfer results and the transfer is successful. mysql> SELECT * FROM account; +----+--------+----------+-------+ | id | bankNo | username | money | +----+--------+----------+-------+ | 1 | 110 | Zhang San | 9800 | | 2 | 120 | Li Si | 1200 | +----+--------+----------+-------+ 2 rows in set (0.00 sec) # Zhang San Transfers 200 to Li Si mysql> START TRANSACTION; Query OK, 0 rows affected (0.00 sec) mysql> UPDATE account SET money = money - 200 WHERE bankNo = 110; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 #Assume that there was an error in updating Lisi's balance. The command failed to execute mysql> UPDATE account SET money = money + 200 WHERE bankNo = 120; mysql> ROLLBACK; Query OK, 0 rows affected (0.00 sec) # Check the balance of Zhang San's and Li Si's accounts. The transfer was unsuccessful. mysql> SELECT * FROM account; +----+--------+----------+-------+ | id | bankNo | username | money | +----+--------+----------+-------+ | 1 | 110 | Zhang San | 9800 | | 2 | 120 | Li Si | 1200 | +----+--------+----------+-------+ 2 rows in set (0.00 sec) -
Method 2:
Still take transfer as an example:mysql> CREATE TABLE `account` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `bankNo` varchar(20), `username` varchar(40) , `money` float, PRIMARY KEY (`id`)); mysql> INSERT INTO `account` VALUES(1, '110', 'Zhang San', 10000); mysql> INSERT INTO `account` VALUES(2, '120', 'Li Si', 1000); # Create a new account table and add data. #Automatic submission is prohibited mysql> SET AUTOCOMMIT= 0; Query OK, 0 rows affected (0.00 sec) # Zhang San Transfer 200 to Li Si mysql> UPDATE account SET money = money - 200 WHERE bankNo = 110; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> UPDATE account SET money = money + 200 WHERE bankNo = 120; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 #Submission of affairs mysql> COMMIT; Query OK, 0 rows affected (0.01 sec) #Check the transfer results and the transfer is successful. mysql> SELECT * FROM account; +----+--------+----------+-------+ | id | bankNo | username | money | +----+--------+----------+-------+ | 1 | 110 | Zhang San | 9800 | | 2 | 120 | Li Si | 1200 | +----+--------+----------+-------+ 2 rows in set (0.00 sec) # ROLLBACK rollback is still used when there are errors in the transfer process.
Be careful
- After a transaction is committed, it does not work to roll back the transaction because the transaction log has been cleared while committing the transaction.
- Innodb supports transactions in the mainstream storage engine supported by mysql, and Myisam does not support transactions.
summary
This paper summarizes the basic concepts and characteristics of database transactions. At the same time, the use of mysql transaction is explained through a case.