Monday, 27 August 2007
For any experienced database developer, using transactions might seem an obvious suggestion, but lightweight databases may
require configuration in order to use transactions. For example, MySql tables use the
MyISAM engine by default, which doesn't support transactions — in order to use transactions you need to set the
storage engine of your tables to
BDB. Also, whereas in Oracle, every statement occurs within a transaction, and you need an explicit
ROLLBACK to end the transaction, in databases such as MySQL and SQLite every
statement is its own transaction (and thus committed to the database immediately) unless you explicitly begin a transaction with a
BEGIN statement, or other database configuration command.
Benefits of Transactions
The primary benefit of using transactions is data integrity. Many database uses require storing data to multiple tables, or multiple rows to the same table in order to maintain a consistent data set. Using transactions ensures that other connections to the same database see either all the updates or none of them. This also applies in the case of interrupted connections — if the power goes off in the middle of a transaction, the database engine will roll back the transaction so it is as-if it was never started. If each statement is committed independently, then other connections may see partial updates, and there is no opportunity for automatic rollback on error.
A secondary benefit of using transactions is speed. There is often an overhead associated with actually committing the data to the database. If you've got 1000 rows to insert, committing after every row can cause quite a performance hit compared to committing once after all the inserts. Of course, this can work the other way too — if you do too much work in a transaction then the database engine can consume lots of space storing the not-yet-committed data or caching data for use by other database connections in order to maintain consistency, which causes a performance hit. As with every optimisation, if you're changing the boundaries of your transactions to gain performance, then it is important to measure both before and after the change.
Using Transactions in Oracle
Oracle databases are always in transaction mode, so all that's needed is to decide where to put the
ROLLBACK. When one transaction is finished, another is automatically started. There are some additional options that
can be specified for advanced usage — see the Oracle documentation for these.
INSERT INTO foo (a,b) VALUES (1,'hello'); INSERT INTO foo (a,b) VALUES (2,'goodbye'); COMMIT; INSERT INTO foo (a,b) VALUES (3,'banana'); COMMIT;
Using Transactions in SQLite
In SQLite, if you wish a transaction to cover more than one statement, then you must use a
TRANSACTION statement. The transaction ends when you execute a
ROLLBACK statement, and
the database reverts to auto-commit mode where each statement has its own transaction.
BEGIN; INSERT INTO foo (a,b) VALUES (1,'hello'); INSERT INTO foo (a,b) VALUES (2,'goodbye'); COMMIT; INSERT INTO foo (a,b) VALUES (3,'banana'); -- implicit COMMIT at end of statement -- with no preceding BEGIN
Using Transactions in MySQL
As mentioned above, by default all tables use the
MyISAM storage engine, so transaction support is disabled. By
changing a table to use the
BDB storage engines, transaction support can be enabled. For new
tables, this is done using the
TYPE parameters on the
CREATE TABLE statement:
CREATE TABLE foo ( bar INTEGER PRIMARY KEY, baz VARCHAR(80) NOT NULL ) ENGINE = InnoDB;
Existing tables can be changed to use the
InnoDB storage engine using
ALTER TABLE foo ENGINE = InnoDB;
You can also change the default storage engine using the
default-storage-engine server option on the server command
line, or in the server configuration file.
Once all the tables you're using have storage engines that support transactions, you have two choices. For a given connection,
you can set the
AUTOCOMMIT session variable to
0, in which case every statement within that connection is
part of a transaction, as for Oracle, or you can leave
AUTOCOMMIT set to
1 and start transactions
explicitly as for SQLite. In auto-commit mode for MySQL, transactions are started with
START TRANSACTION. To disable
AUTOCOMMIT for the current transaction, use the
You can configure the database to run this statement immediately upon opening a connection using the
server variable. This can be set in the configuration file, or using the following command:
SET GLOBAL init_connect='SET AUTOCOMMIT=0';
MySQL also supports additional transaction options — check the documentation for details.
One thing to watch out for is code that causes an automatic
COMMIT. Most databases cause an
ROLLBACK of any open transaction when a connection is closed, so it is important to make sure that all
changes are committed before closing the connection.
Also worth watching out for are commands that cause an automatic
COMMIT. The list varies depending on the database,
but generally DDL statements such as
CREATE TABLE will cause an automatic commit. It is probably best to avoid
interleaving DML statement with any other type of statement in order to avoid surprises. Check your database documentation for