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 INSERT
s 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.