English
English
Transaction is one of the key mechanisms to ensure data consistency and integrity when using PHP's PDO (PHP Data Objects) for database operations. PDO::beginTransaction() is a common method to start transactions. Many developers will wonder when they are beginners what does this function have to do with the so-called "autocommit mode"? This article will explore the connection between the two in depth and analyze it in detail through examples.
In most databases (such as MySQL), by default, each independently executed SQL statement is executed as an independent transaction and automatically committed. This behavior is called autocommit. In other words, if you unspecifiedly start a transaction, the database will immediately save the result of the operation after executing an INSERT , UPDATE , or DELETE statement.
When you call PDO::beginTransaction() , PDO will automatically turn off the current auto-commit mode and start a new transaction. At this time, all subsequent write operations to the database (such as INSERT , UPDATE , DELETE ) will not be submitted immediately until you explicitly call PDO::commit() or PDO::rollBack() . In other words, the function of this function is to "turn off automatic submission and enable manual submission".
Here is its core relationship with the automatic submission pattern:
When beginTransaction() is not called : submit immediately after each statement is executed (automatic submission mode is enabled).
After calling beginTransaction() : Turn off the automatic submission mode and enter the transaction control state.
It is worth noting that after the transaction is executed (whether commit() or rollBack() ), PDO will usually automatically restore the connection to automatic commit mode so that subsequent statements can continue to execute as default behavior.
<?php
try {
$pdo = new PDO("mysql:host=localhost;dbname=testdb", "username", "password");
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
// Start a transaction，Automatic submission is closed
$pdo->beginTransaction();
// Insert the first record
$pdo->exec("INSERT INTO users (name, email) VALUES ('Alice', '[email protected]')");
// Insert the second record
$pdo->exec("INSERT INTO users (name, email) VALUES ('Bob', '[email protected]')");
// Submit transactions
$pdo->commit();
echo "Data has been successfully written。";
} catch (Exception $e) {
$pdo->rollBack(); // If there is an exception，Roll back transactions
echo "Transaction failed：" . $e->getMessage();
}
?>
In the above code, the inserted record will be actually written to the database only after commit() is successfully called. If an exception occurs midway, calling rollBack() will undo all uncommitted changes. The entire process occurs when the automatic submission mode is turned off.
You can observe changes in the automatic submission pattern through SQL statements:
$pdo->query("SELECT @@autocommit")->fetchColumn(); // return 1 Indicates on，0 Indicates close
After executing beginTransaction() , running the query may return 0, indicating that the automatic submission is closed. This state will last until after the transaction is over.
Although in some drivers you can use PDO::setAttribute() to set the auto-commit state, for example:
$pdo->setAttribute(PDO::ATTR_AUTOCOMMIT, false);
But this is not recommended for two reasons:
Not all PDO drivers support this property, and improper use can cause compatibility issues;
It is recommended to use standard transaction APIs (i.e. beginTransaction() , commit() , rollBack() ) to manage transaction state to ensure cross-platform consistency.
The function of PDO::beginTransaction() is to turn off the automatic commit mode and manually manage transactions;
Automatic commit mode is enabled by default when no transaction is used;
Once commit() or rollBack() is called, the transaction ends and automatic commits are usually resumed;
Using transactions can improve the security and consistency of data operations, especially in scenarios where multiple write operations must be "all successful or all failed".
Understanding the relationship between beginTransaction() and automatic submission will help you write more robust and controllable database operation code. Remember, transactions are not enemies of performance, but patron saint of data correctness.
