MySQL transaction
MySQL transaction
MySQL transaction is mainly used to process data with large operations and high complexity. For example, in the personnel management system, if you delete a person, you need to delete the basic information of the person, and also delete the information related to the person, such as mailbox, articles, etc. In this way, these database operation statements constitute a transaction. !
In MySQL, only databases or tables using the Innodb database engine support transactions.
Transaction processing can be used to maintain the integrity of the database and ensure that batches of SQL statements are either all executed or not executed at all.
Transactions are used to manage insert, update, delete statements
Generally speaking, transactions must meet four conditions (ACID):: Atomicity, or indivisibility, consistency, isolation, independence, and durability.
Atomicity: All operations in a transaction will either be completed or not completed, and will not end in any intermediate link. If an error occurs during the execution of the transaction, it will be rolled back to the state before the transaction started, as if the transaction had never been executed.
Consistency: The integrity of the database is not destroyed before the transaction starts and after the transaction ends. This means that the data written must fully comply with all preset rules, including the accuracy and concatenation of the data, and that the subsequent database can spontaneously complete the predetermined work.
Isolation: The database allows multiple concurrent transactions to read, write and modify its data at the same time. Isolation can prevent data corruption due to cross execution when multiple transactions are executed concurrently. Inconsistent. Transaction isolation is divided into different levels, including read uncommitted, read committed, repeatable read and serializable.
Persistence: After the transaction is completed, the modification to the data is permanent and will not be lost even if the system fails.
Note: Under the default settings of the MySQL command line, transactions are automatically submitted, that is, the COMMIT operation will be performed immediately after executing the SQL statement. Therefore, to explicitly start a transaction, you must use the command BEGIN or START TRANSACTION, or execute the command SET AUTOCOMMIT=0 to disable the use of automatic commit for the current session.
Transaction control statement:
BEGIN or START TRANSACTION; explicitly start a transaction;
COMMIT; you can also use COMMIT WORK, but they are equivalent. COMMIT will commit the transaction and make all modifications to the database permanent;
ROLLBACK; you can use ROLLBACK WORK, but the two are equivalent. Rollback will end the user's transaction and undo all uncommitted modifications in progress;
SAVEPOINT identifier; SAVEPOINT allows the creation of a savepoint in a transaction, and there can be multiple SAVEPOINTs in a transaction;
RELEASE SAVEPOINT identifier; delete a transaction Save point. When there is no specified save point, executing this statement will throw an exception;
ROLLBACK TO identifier; roll back the transaction to the marked point;
SET TRANSACTION; used to set the isolation level of the transaction. The transaction isolation levels provided by the InnoDB storage engine are READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ and SERIALIZABLE.
There are two main methods for MYSQL transaction processing:
#1. Use BEGIN, ROLLBACK, COMMIT To achieve
BEGIN Start a transaction
ROLLBACK Transaction rollback
- ##COMMIT Transaction confirmation
#2. Use SET directly to change MySQL’s automatic commit mode:
- ##SET AUTOCOMMIT=0 prohibited Automatic submission
- ##SET AUTOCOMMIT=1 Turn on automatic submission
- Transaction test
mysql> use DEMO;
Database changed
mysql> CREATE TABLE demo_transaction_test( id int(5)) engine=innodb; # 创建数据表
Query OK, 0 rows affected (0.04 sec)
mysql> select * from demo_transaction_test;Empty set (0.01 sec)
mysql> begin; # 开始事务Query OK, 0 rows affected (0.00 sec)
mysql> insert into demo_transaction_test value(5);Query OK, 1 rows affected (0.01 sec)
mysql> insert into demo_transaction_test value(6);Query OK, 1 rows affected (0.00 sec)
mysql> commit; # 提交事务
Query OK, 0 rows affected (0.01 sec)
mysql> select * from demo_transaction_test;
+------+
| id |
+------+
| 5 |
| 6 |
+------+2 rows in set (0.01 sec)
mysql> begin; # 开始事务
Query OK, 0 rows affected (0.00 sec)
mysql> insert into demo_transaction_test values(7);
Query OK, 1 rows affected (0.00 sec)
mysql> rollback; # 回滚
Query OK, 0 rows affected (0.00 sec)
mysql> select * from demo_transaction_test; # 因为回滚所以数据没有插入+------+
| id |
+------+
| 5 |
| 6 |
+------+2 rows in set (0.01 sec)
mysql>
MySQL ORDER BY test:
<?php
$dbhost = 'localhost'; // mysql服务器主机地址
$dbuser = 'root'; // mysql用户名
$dbpass = 'root'; // mysql用户名密码
$conn = mysqli_connect($dbhost, $dbuser, $dbpass);
if(! $conn ){
die('连接失败: ' . mysqli_error($conn));}
// 设置编码，防止中文乱码
mysqli_query($conn, "set names utf8");
mysqli_select_db( $conn, 'DEMO' );
mysqli_query($conn, "SET AUTOCOMMIT=0"); // 设置为不自动提交，因为MYSQL默认立即执行
mysqli_begin_transaction($conn); // 开始事务定义
if(!mysqli_query($conn, "insert into demo_transaction_test (id) values(8)"))
{ mysqli_query($conn, "ROLLBACK");// 判断当执行失败时回滚}
if(!mysqli_query($conn, "insert into demo_transaction_test (id) values(9)")){
mysqli_query($conn, "ROLLBACK");// 判断执行失败时回滚
}
mysqli_commit($conn); //执行事务
mysqli_close($conn);
?>Related video tutorial recommendations：
