MySQL Transactions

MySQL transactions are primarily used for handling large and complex data operations. For example, in a personnel management system, when you delete a person, you need to delete their basic information as well as related information such as mailboxes and articles. These database operation statements together form a transaction!

• Only databases or tables using the InnoDB database engine in MySQL support transactions.

• Transaction processing helps maintain database integrity, ensuring that batches of SQL statements are either fully executed or not executed at all.

• Transactions are used to manage insert, update, and delete statements.

Generally, a transaction must meet four conditions (ACID): Atomicity (Atomicity, or indivisibility), Consistency (Consistency), Isolation (Isolation, also known as independence), and Durability (Durability).

• Atomicity: All operations in a transaction either complete entirely or do not complete at all, never stopping midway. If an error occurs during transaction execution, the transaction rolls back to its state before execution, as if the transaction had never been executed.

• Consistency: The integrity of the database remains intact before and after the transaction. This means that the data written must fully comply with all preset rules, including data accuracy, consistency, and the ability for the database to autonomously complete predetermined tasks.

• Isolation: The database allows multiple concurrent transactions to read, write, and modify its data. Isolation prevents data inconsistency due to interleaved execution of multiple transactions. Transaction isolation levels include Read Uncommitted, Read Committed, Repeatable Read, and Serializable.

• Durability: After transaction processing, modifications to the data are permanent and will not be lost even if there is a system failure.

In the default settings of MySQL command line, transactions are automatically committed, meaning that after an SQL statement is executed, a COMMIT operation is immediately performed. Therefore, to explicitly start a transaction, you must use the commands BEGIN or START TRANSACTION, or execute the command SET AUTOCOMMIT=0 to disable automatic commit for the current session.

Transaction Control Statements:

• BEGIN or START TRANSACTION explicitly starts a transaction;
• COMMIT or COMMIT WORK (both are equivalent) commits the transaction and makes all modifications to the database permanent;
• ROLLBACK or ROLLBACK WORK (both are equivalent) rolls back the transaction and undoes all uncommitted modifications;
• SAVEPOINT identifier allows creating a savepoint within a transaction, and multiple savepoints can exist within one transaction;
• RELEASE SAVEPOINT identifier deletes a savepoint within a transaction. Executing this statement when no specified savepoint exists throws an exception;
• ROLLBACK TO identifier rolls back the transaction to the marked point;
• SET TRANSACTION sets the isolation level for the transaction. InnoDB storage engine provides transaction isolation levels such as READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, and SERIALIZABLE.

MySQL Transaction Processing Methods:

1. Using BEGIN, ROLLBACK, COMMIT:

   • BEGIN starts a transaction
   • ROLLBACK rolls back the transaction
   • COMMIT confirms the transaction

2. Directly changing MySQL's auto-commit mode with SET:

   • SET AUTOCOMMIT=0 disables auto-commit
   • SET AUTOCOMMIT=1 enables auto-commit

Transaction Testing

mysql> use tutorialpro;
Database changed
mysql> CREATE TABLE tutorialpro_transaction_test( id int(5)) engine=innodb;  # Create a data table
Query OK, 0 rows affected (0.04 sec)

mysql> select * from tutorialpro_transaction_test;
Empty set (0.01 sec)

mysql> begin;  # Start transaction
Query OK, 0 rows affected (0.00 sec)

mysql> insert into tutorialpro_transaction_test value(5);
Query OK, 1 rows affected (0.01 sec)

mysql> insert into tutorialpro_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 tutorialpro_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 tutorialpro_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 tutorialpro_transaction_test;   # Data not inserted due to rollback
+------+
| id   |
+------+
| 5    |
| 6    |
+------+
2 rows in set (0.01 sec)

mysql>

PHP Example Using Transactions

MySQL ORDER BY Testing:

<?php
$dbhost = 'localhost';  // MySQL server host address
$dbuser = 'root';       // MySQL username
$dbpass = '123456';     // MySQL user password
$conn = mysqli_connect($dbhost, $dbuser, $dbpass);
if(! $conn )
{
    die('Connection failed: ' . mysqli_error($conn));
}
// Set encoding to prevent Chinese乱码
mysqli_query($conn, "set names utf8");
mysqli_select_db( $conn, 'tutorialpro' );
mysqli_query($conn, "SET AUTOCOMMIT=0"); // Set to not auto-commit, as MySQL defaults to immediate execution
mysqli_begin_transaction($conn);            // Start transaction definition

if(!mysqli_query($conn, "insert into tutorialpro_transaction_test (id) values(8)"))
{
    mysqli_query($conn, "ROLLBACK");     // Rollback if execution fails
}

if(!mysqli_query($conn, "insert into tutorialpro_transaction_test (id) values(9)"))
{
    mysqli_query($conn, "ROLLBACK");      // Rollback if execution fails
}
mysqli_commit($conn);            // Execute transaction
mysqli_close($conn);
?>