3.7 Transactions
MySQL 4.0 supports database transactions. Transactions allow you to group together a sequence of database queries to ensure that MySQL either completes the entire set of actions or fails to perform any of them. There's no in-between state.
A perfect example demonstrating the importance of transactions is transferring money from a bank account. Removing money takes two steps: first, the program checks whether the account contains enough funds; if it does, it then subtracts the money from the account and places it in another.
You don't want a second withdrawal to occur in between checking the account balance and removing the funds, because the other transaction could completely empty the account, leaving no more money for your request. This is a big problem for banks.
3.7.1 Creating Transaction-Supported Tables
In order to support transactions, MySQL introduced a new database table format, called InnoDB. The original format, MyISAM, can't be used with transactions. If you're running MySQL 4.0 or higher, you should have support for InnoDB tables. To check, run the following MySQL query:
mysql> SHOW VARIABLES LIKE 'have_innodb'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | have_innodb | YES | +---------------+-------+
It should say YES. By default, MySQL enables InnoDB tables as of Version 4.0, so if this variable is set to NO, you should rebuild MySQL and remove the --without-innodb flag from your configuration.
|
To create an InnoDB table, add TYPE = InnoDB to the end of the CREATE statement. For example, to replicate the programs table:
CREATE TABLE programs (id int(5) unsigned AUTO_INCREMENT,
title VARCHAR(50) not null,
channel_id int(5) unsigned not null,
primary key(id))
TYPE = InnoDB;This makes a programs table that supports transactions.
You can make MySQL convert an existing MyISAM table to the new format with the following command:
ALTER TABLE programs TYPE = InnoDB;
This converts programs to InnoDB. Substitute your table names for programs.
3.7.2 Using Transactions
To signal to MySQL that you want to begin a transaction, use the keyword BEGIN. (BEGIN WORK and START TRANSACTIONS are synonyms for BEGIN.) To end a transaction, use COMMIT. In PHP, you pass these keywords as part of your SQL inside of mysqli_query( ):
$user = array('rasmus', 'z.8cMpdFbNAPw');
mysqli_query($db, 'BEGIN');
foreach ($users as $user) {
mysqli_query($db, "INSERT INTO users VALUES(
'$user[0]', '$user[1]')";
}
mysqli_query('COMMIT');The SQL opens with BEGIN, and then PHP iterates through an array, creating a series of INSERTs. When the loop is done, COMMIT is sent.
This transaction will enter a group of user records into a database. It's unlikely to have any errors that require you to roll back the data. However, assume the users table requires that each username entry be UNIQUE. Then, this array causes a problem:
$users = array(array('rasmus', 'z.8cMpdFbNAPw'),
array('zeev' , 'asd34.23NNDeq'),
array('rasmus', 'z.8cMpdFbNAPw'));Since there are two entries in the array with a username of rasmus, MySQL issues an error when you attempt to enter the second rasmus into the table.
Here's the SQL to create that table:
CREATE TABLE users(username VARCHAR(50) UNIQUE,
password VARCHAR(50))
TYPE = InnoDBNow you can structure your code to catch the error and revert the transaction, as in Example 3-7.
Example 3-7. Querying using transactions
$error = 0;
// Start transaction
mysqli_query($db, 'BEGIN');
// Prepare INSERT statement
$sql = 'INSERT INTO users VALUES(?, ?)';
$stmt = mysqli_stmt_init($db);
mysqli_stmt_prepare($stmt, $sql);
mysqli_stmt_bind_param($stmt, 'ss', $username, $password);
// Add each new user one-by-one
foreach ($users as $user) {
list($username, $password) = $user;
// Abort if there's an error
if (!mysqli_stmt_execute($stmt)) {
$error = mysqli_errno($db);
break;
}
}
// Revert previous commits on error; otherwise, save
if ($error) {
mysqli_query($db, 'ROLLBACK');
} else {
mysqli_query($db, 'COMMIT');
}The mysqli_query('BEGIN'); starts the transaction. The foreach( ) iterates over the $users array. Inside, the bound variables are set to the correct elements of $users and the query is executed. Since mysqli_stmt_execute( ) returns true on success and false on failure, there's no need to check the return value of mysqli_errno( ) for every request. Instead, call it when you know there's an error, set $error to the return code, and break out of the foreach.
Since mysqli_errno( ) always returns a positive number when there's an error, you can use the value of $error to decide whether to commit or roll back the transaction. When $error is positive, it evaluates as true and you should issue the ROLLBACK command to revert the transaction. When it's 0, pass mysqli_query( ) COMMIT to store the rows in the database.
3.7.3 AUTOCOMMIT
By default, MySQL automatically commits any command you issue, unless it's wrapped inside of a transaction. However, you can reverse this behavior for InnoDB tables, so that commands are presumed to be part of a transaction and are flushed only when COMMIT is sent.
Enable this feature with the following command:
// Disable AUTOCOMMIT mysqli_autocommit($db, false); // This SQL statement is identical to the above function SET AUTOCOMMIT = 0;
Now your queries will be reflected in your current connection, but won't be saved unless you issue a COMMIT command. Example 3-8 is a modified version of the last script, Example 3-7, that uses this feature.
Example 3-8. Querying with AUTOCOMMIT disabled
$error = 0;
// Disable AUTOCOMMIT
mysqli_autocommit($db, false);
// Prepare INSERT statement
$sql = 'INSERT INTO users VALUES(?, ?)';
$stmt = mysqli_stmt_prepare($db, $sql);
mysqli_stmt_bind_param($stmt, 'ss', $username, $password);
// Add each new user one-by-one
foreach ($users as $user) {
list($username, $password) = $user;
// Abort if there's an error
if (!mysqli_stmt_execute($stmt)) {
$error = mysqli_errno($db);
break;
}
}
// Save if not an error
if (!$error) {
mysqli_query($db, 'COMMIT');
}Since AUTOCOMMIT is disabled, there's no need to ROLLBACK the transaction at the end of the code.
You can check the current status of AUTOCOMMIT with:
mysql> SELECT @@AUTOCOMMIT; +--------------+ | @@AUTOCOMMIT | +--------------+ | 1 | +--------------+ 1 row in set (0.00 sec)
The double at signs (@@) tell MySQL to retrieve a MySQL-specific variable instead of a user-defined variable.
