MySQL Transactions

MySQL transactions are mainly used to handle large-scale, complex data operations. For example, in a personnel management system, when you delete a person, you need to delete the basic information of the person as well as the related information of the person, such as email, articles, etc. In this way, these database operation statements constitute a transaction!

• In MySQL, only databases or tables that use the Innodb database engine support transactions.

• Transaction processing can be used to maintain the integrity of the database, ensuring that a batch of SQL statements is either executed entirely or not at all.

• A transaction is used to manage insert, update, delete statements

Generally, a transaction must meet4condition (ACID): Atomicity (Atomicity, or indivisibility), consistency (Consistency), isolation (Isolation, also known as independence), durability (Durability).

• Atomicity:All operations in a transaction must be completed entirely or not at all; they will not end in the middle of any process. If an error occurs during the execution of a transaction, it will be rolled back (Rollback) to the state before the transaction started, as if the transaction had never been executed.

• Consistency:The integrity of the database is not compromised before and after the transaction starts. This means that the data written must completely comply with all predefined rules, including the accuracy, consistency, and the ability of the subsequent database to spontaneously complete the预定的工作.

• Isolation:The database allows multiple concurrent transactions to read and write and modify its data simultaneously, and isolation can prevent data inconsistency due to interleaved execution when multiple transactions are executed concurrently. Transaction isolation is divided into different levels, including Read Uncommitted (Read uncommitted), Read Committed (read committed), Repeatable Read (repeatable read), and Serializable (Serializable).

• Persistence:After the transaction processing is completed, the modifications to the data are permanent, and even if the system fails, they will not be lost.

Under the default settings of the MySQL command line, transactions are automatically committed, that is, after executing an SQL statement, the COMMIT operation will be executed immediately. 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 statements:

• BEGIN or START TRANSACTION explicitly starts a transaction;

• COMMIT can also be used as COMMIT WORK, although both are equivalent. COMMIT will commit the transaction and make all modifications made to the database permanent;

• ROLLBACK can also be used as ROLLBACK WORK, although both are equivalent. Rolling back will end the user's transaction and undo all uncommitted changes in progress;

• SAVEPOINT identifier, SAVEPOINT allows the creation of a savepoint within a transaction; a transaction can have multiple SAVEPOINTS;

• RELEASE SAVEPOINT identifier deletes a transaction savepoint, and if no specified savepoint is present, executing this statement will throw an exception;

• ROLLBACK TO identifier rolls back the transaction to the marked point;

• SET TRANSACTION is used to set the transaction isolation level. The InnoDB storage engine provides transaction isolation levels including READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, and SERIALIZABLE.

MySQL transaction processing mainly has two methods:

1Using BEGIN, ROLLBACK, COMMIT to implement

• BEGIN Start a transaction

• ROLLBACK Transaction rollback

• COMMIT 　Transaction confirmation

2Directly use SET to change MySQL's automatic commit mode:

• SET AUTOCOMMIT=0 　 Disable automatic commit

• SET AUTOCOMMIT=1 Enable automatic commit

mysql> use w3codebox;
Database changed
mysql> CREATE TABLE w3codebox_transaction_test(id int(5)) engine=innodb; # Create data table
Query OK, 0 rows affected (0.04　sec)
　
mysql> select　*　from w3codebox_transaction_test;
Empty set (0.01　sec)
　
mysql> begin; # Start transaction
Query OK, 0 rows affected (0.00 sec)
　
mysql> insert into w3codebox_transaction_test value(5);
Query OK,　1　rows affected (0.01　sec)
　
mysql> insert into w3codebox_transaction_test value(6);
Query OK,　1　rows affected (0.00 sec)
　
mysql> commit; # Commit transaction
Query OK, 0 rows affected (0.01　sec)
　
mysql> select　*　from w3codebox_transaction_test;
+------+
| id |
+------+
|　5　　　　|
|　6　　　　|
+------+
2　rows in set (0.01　sec)
　
mysql> begin; # Start transaction
Query OK, 0 rows affected (0.00 sec)
　
mysql> insert into w3codebox_transaction_test values(7);
Query OK,　1　rows affected (0.00 sec)
　
mysql> rollback; # Rollback
Query OK, 0 rows affected (0.00 sec)
　
mysql> select　*　from w3codebox_transaction_test; # Because of rollback, data is not inserted
+------+
| id |
+------+
|　5　　　　|
|　6　　　　|
+------+
2　rows in set (0.01　sec)
　
mysql>

Example of PHP using transactions

<?php
$dbhost = 'localhost';　　//　MySQL server host address
$dbuser = 'root';　　　　　　　　　　　　//　MySQL username
$dbpass = '';123456';　　　　　　　　　　//　MySQL username and password
$conn = mysqli_connect($dbhost, $dbuser, $dbpass);
if(! $conn)
{
　　　　die('Connection failed: ' . mysqli_error($conn));
}
//　Set encoding to prevent Chinese garbled characters
mysqli_query($conn, "set names utf8);
mysqli_select_db($conn, 'w3codebox');
mysqli_query($conn, "SET AUTOCOMMIT=0");　//　Set not to auto-commit, because MYSQL default is to execute immediately
mysqli_begin_transaction($conn);　　　　　　　　　　　　//　Start Transaction Definition
　
if(!mysqli_query($conn, "insert into w3codebox_transaction_test(id) values(8)")
{
　　　　mysqli_query($conn, "ROLLBACK");　　　　　//　Rollback if Execution Fails
}
　
if(!mysqli_query($conn, "insert into w3codebox_transaction_test(id) values(9)")
{
　　　　mysqli_query($conn, "ROLLBACK");　　　　　　//　Rollback if Execution Fails
}
mysqli_commit($conn);　　　　　　　　　　　　//Execute Transaction
mysqli_close($conn);
?>