MySQL transactions are mainly used to deal with data with large amount of operations and high complexity. For example, in the personnel management system, if you delete a person, you need to delete not only the basic information of the person, but also the information related to the person, such as mailboxes, articles, etc., so that these database operation statements constitute a transaction!
In MySQL, only databases or tables that use the Innodb database engine support transactions.
Transactions can be used to maintain the integrity of the database, ensuring that batches of SQL statements are either executed or not executed.
Transactions are used to manage insert,update,delete statements
Generally speaking, a transaction must satisfy four conditions (ACID): atomicity ( A Tomicity, or indivisibility), consistency ( C Onsistency), isolation ( I Solation, also known as independence, persistence ( D Urability).
原子性： All operations in a transaction (transaction) are either completed or not completed, and do not end at some point in the middle. An error occurs during the execution of a transaction and is Rollback back to its state before the transaction starts, as if the transaction had never been executed.
一致性： The integrity of the database is not compromised before the transaction starts and after the transaction ends. This means that the data written must fully comply with all the preset rules, including the accuracy and concatenation of the data, and that the subsequent database can spontaneously complete the scheduled work.
隔离性： The ability of database to allow multiple concurrent transactions to read, write and modify its data at the same time, isolation can prevent data inconsistency caused by cross execution when multiple transactions are executed concurrently. Transaction isolation is divided into different levels, including read uncommitted (Read uncommitted), read commit (read committed), repeatable read (repeatable read) and serialization (Serializable).
持久性： After the end of the transaction, the modification of the data is permanent, even if the system failure will not be lost.
Under the default setting of the MySQL command line, transactions are committed automatically, that is, the COMMIT operation is performed immediately after the SQL statement is executed. So to explicitly open a transaction, you must use the command BEGIN or START TRANSACTION, or execute the command SET AUTOCOMMIT=0 to disable autocommit using the current session.
Transaction control statement: ¶
BEGIN or START TRANSACTION explicitly open a transaction
COMMIT can also use COMMIT WORK, but the two are equivalent. COMMIT commits the transaction and makes all changes made to the database permanent
ROLLBACK can also use ROLLBACK WORK, but the two are equivalent. A rollback will end the user’s transaction and undo any uncommitted changes in progress
SAVEPOINT identifier,SAVEPOINT allows you to create a SavePoint in a transaction, and there can be multiple SAVEPOINT in a transaction
RELEASE SAVEPOINT identifier deletes a SavePoint for a transaction, and executing the statement throws an exception when there is no SavePoint specified
ROLLBACK TO identifier rolls back the transaction to the marked point
SET TRANSACTION is used to set the isolation level of the transaction. The isolation levels provided by the InnoDB storage engine for transactions are READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, and SERIALIZABLE.
There are two main methods of MYSQL transaction processing: ¶
1、用 BEGIN, ROLLBACK, COMMIT来实现
BEGIN Start a transaction
ROLLBACK Transaction rollback
COMMIT Transaction confirmation
2、直接用 SET 来改变 MySQL 的自动提交模式:
SET AUTOCOMMIT=0 Automatic submission is prohibited
SET AUTOCOMMIT=1 Turn on automatic submission
Transaction testing ¶
mysql>useRUNOOB;Databasechangedmysql>CREATETABLErunoob_transaction_test(idint(5))engine=innodb;#
创建数据表QueryOK,0rowsaffected(0.04sec)mysql>select\*fromrunoob_transaction_test;Emptyset(0.01sec)mysql>begin;#
开始事务QueryOK,0rowsaffected(0.00sec)mysql>insertintorunoob_transaction_testvalue(5);QueryOK,1rowsaffected(0.01sec)mysql>insertintorunoob_transaction_testvalue(6);QueryOK,1rowsaffected(0.00sec)mysql>commit;#
提交事务QueryOK,0rowsaffected(0.01sec)mysql>select\*fromrunoob_transaction_test;
+------+ \|id\| +------+ \|5\| \|6\|
+------+2rowsinset(0.01sec)mysql>begin;#
开始事务QueryOK,0rowsaffected(0.00sec)mysql>insertintorunoob_transaction_testvalues(7);QueryOK,1rowsaffected(0.00sec)mysql>rollback;#
回滚QueryOK,0rowsaffected(0.00sec)mysql>select\*fromrunoob_transaction_test;#
因为回滚所以数据没有插入+------+ \|id\| +------+ \|5\| \|6\|
+------+2rowsinset(0.01sec)mysql>
Using transaction instances in PHP ¶
MySQL ORDER BY Test: ¶
<?php$dbhost='localhost';//mysql服务器主机地址$dbuser='root';//mysql用户名$dbpass='123456';//mysql用户名密码$conn=mysqli_connect($dbhost,$dbuser,$dbpass);if(!$conn){die('连接失败:'.mysqli_error($conn));}//设置编码，防止中文乱码mysqli_query($conn,"set
names utf8");mysqli_select_db($conn,'RUNOOB');mysqli_query($conn,"SET
AUTOCOMMIT=0");//设置为不自动提交，因为MYSQL默认立即执行mysqli_begin_transaction($conn);//开始事务定义if(!mysqli_query($conn,"insert
into runoob_transaction_test (id)
values(8)")){mysqli_query($conn,"ROLLBACK");//判断当执行失败时回滚}if(!mysqli_query($conn,"insert
into runoob_transaction_test (id)
values(9)")){mysqli_query($conn,"ROLLBACK");//判断执行失败时回滚}mysqli_commit($conn);//执行事务mysqli_close($conn);?>