Transactions

SQLite supports database transactions. Transactions are good for ensuring database consistency, but they serve a second purpose in SQLite: speed. When a set of queries are grouped together inside a transaction, SQLite executes them significantly faster than if they were performed individually. The more queries you throw at SQLite simultaneously, the larger the percentage increase in speed.

When SQLite creates a connection or makes a query, it does a certain amount of setup; likewise, when it closes a connection or completes a query, it again must perform a sequence of housecleaning tasks. These duties are relatively expensive, but SQLite needs to do this only once per transaction, regardless of how many queries are inside the transaction. This translates into a performance improvement.

However, there’s a downside to using transactions in SQLite: when you wrap all your calls into a transaction, SQLite locks the entire database file, and the locked file cannot be accessed by other users. (More finely grained locking capabilities are a benefit of using a “real” database instead of SQLite.) If you’re more concerned about overall system responsiveness than with optimizing for a specific action, benchmark your site to evaluate whether using transactions in this manner is appropriate in your script.

To signal to SQLite that you want to begin a transaction, use the keyword BEGIN; to end a transaction, use COMMIT. In PHP, pass these keywords as part of your SQL inside of sqlite_query( ):

$users = array(array('rasmus', 'z.8cMpdFbNAPw'), 
               array('zeev'  , 'asd34.23NNDeq'));

$sql = 'BEGIN;';
foreach ($users as $user) {
    $sql .= "INSERT INTO users 
                 VALUES('${user[0]}', '${user[1]}');";
}
$sql .= 'COMMIT;';

sqlite_query($db, $sql);

The SQL opens with BEGIN, and then PHP iterates through an array, appending a series of INSERTs to $sql. When the loop is done, COMMIT is appended. SQL statements are separated by semicolons (;). This lets SQLite know to move from one statement to another. Unlike the MySQL extensions, it is always acceptable to combine multiple SQL statements in a line, even if you’re not within a transaction.

You can also spread out a transaction over multiple calls to sqlite_query( ) like this:

$users = array(array('rasmus', 'z.8cMpdFbNAPw'), 
               array('zeev'  , 'asd34.23NNDeq'));

sqlite_query($db, 'BEGIN;');

foreach ($users as $user) {
        // Assume data is already escaped
    $sql = "INSERT INTO users 
                VALUES('${user[0]}', '${user[1]}');";
    sqlite_query($db, $sql);
}

sqlite_query($db, 'COMMIT;');

It is more efficient to make just a single query; however, spreading your queries out gives you the opportunity to undo, or roll back, a transaction.

For instance, here’s a modification of the previous example that aborts the transaction if an error is found:

function add_users($db, $users) {
    $error = false;
    
    // Start transaction
    sqlite_query($db, 'BEGIN;');
    
    // Add each new user one-by-one
    foreach ($users as $user) {
        $sql = "INSERT INTO users 
                    VALUES('${user[0]}', '${user[1]}');";
        sqlite_query($db, $sql);
    
        // Abort if there's an error
        if (sqlite_last_error($db)) {
            $error = true;
            break;
        }
    }
    
    // Revert previous commits on error; otherwise, save
    if ($error) {
        sqlite_query($db, 'ROLLBACK;');
    } else {
        sqlite_query($db, 'COMMIT;');
    }

    return !$error;
}

This function does the same loop through $users, but now it checks sqlite_last_error( ) after every INSERT. If there’s an error, the function returns a true value, so $error gets set and you break out of the loop. When there are no errors, sqlite_last_error( ) returns 0.

Instead of automatically committing the transaction, check $error. If an error is found, reverse the transaction by executing the ROLLBACK command. Issuing a ROLLBACK instructs SQLite to revert the status of the database to its condition before BEGIN was sent.

Here is an example that triggers a rollback:

$db = sqlite_open('/www/support/users.db');

$users = array(array('rasmus', 'z.8cMpdFbNAPw'), 
               array('zeev'  , 'asd34.23NNDeq'),
               array('rasmus', 'z.8cMpdFbNAPw'));

add_users($db, $users);

Assume the users table requires that each username entry be UNIQUE. Since there are two entries in the array with a username of rasmus, SQLite issues an error when you attempt to enter the second rasmus into the table.

You could ignore the error and proceed, but as things currently stand, the entire set of users is skipped. A more sophisticated example would examine the specific value returned by sqlite_last_error( ) and take different actions on a case-by-case basis. This would let you skip over a minor error like this but also let you revert the transaction if a more drastic error occurred.

Get Upgrading to PHP 5 now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.