Database Tip: Use Transactions

Monday, 27 August 2007

Here's another Database Tip, to follow on from my previous one on creating appropriate indexes. This time the focus is transactions.

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 InnoDB or BDB. Also, whereas in Oracle, every statement occurs within a transaction, and you need an explicit COMMIT or 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 COMMIT or 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 BEGIN or BEGIN TRANSACTION statement. The transaction ends when you execute a COMMIT or 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 InnoDB or BDB storage engines, transaction support can be enabled. For new tables, this is done using the ENGINE or 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:

    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 BEGIN, BEGIN WORK or START TRANSACTION. To disable AUTOCOMMIT for the current transaction, use the SET statement:

    SET AUTOCOMMIT=0;

You can configure the database to run this statement immediately upon opening a connection using the init_connect 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.

Automatic ROLLBACK and COMMIT

One thing to watch out for is code that causes an automatic ROLLBACK or COMMIT. Most databases cause an automatic 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 details.

Posted by Anthony Williams
[/ database /] permanent link
Stumble It! | Submit to Reddit | Submit to DZone

Comment on this post

If you liked this post, why not subscribe to the RSS feed or Follow me on Twitter? You can also subscribe to this blog by email using the form on the left.

Design and Content Copyright © 2005-2024 Just Software Solutions Ltd. All rights reserved. | Privacy Policy