InstantDB Project
About InstantDB
Project Mail Lists
Short History
Reporting Bugs
Screen Shots
3rd Party Examples

CVS Repositories

Who We Are
News, Articles & Events
Getting Involved
Contact Us

Case Studies
On The Edge! -NEW-
Commercial Vendors

Transaction Processing

InstantDB supports ANSI standard transaction processing. All SQL statements always take place within a transaction. By default, as per the JDBC™ spec., auto-commit is enabled, so there is no need to perform explicit commits after every statement.

Commits and rollbacks can both be achieved using either the appropriate JDBC™ Connection method calls, or by using the SQL commit work, and rollback work statements. These can be interleaved if required.


Transactions and Connections

  Previous Release


     Advanced      Reference

Transactions in JDBC™ are tied to a Connection. At any one point in time, every connection has one transaction associated with it. InstantDB allows multiple threads to either share a connection, or to use their own. However InstantDB forces synchronization on the transaction before allowing a statement, or prepared statement, to execute. Consequently, threads which share a connection (and therefore a transaction) will not pre-empt one another.

Note that JDBC™ specifies that a transaction does not auto-commit until the statement is re-used or closed. Consequently, if an interactive thread does not close or re-use the current statement, the transaction remains un-committed and the thread retains all of its database locks.

Locking and Deadlocks

Locking in InstantDB is at the table level. Row level locking is not currently supported. Note also that all locks are Exclusive, i.e. there is no concept of sharing a table for read access. So a SELECT statement on a table will lock that table against all other SELECTs until the transaction completes.

If you have multiple threads sharing a connection, and then decide to let them open their own connections, make sure that the shared transaction gets explictly committed, otherwise there may be no threads left to re-use or close the last statement used and the shared transaction will be left open with its locks intact.

Deadlock detection is automatic and occurs before deadlock takes places. The thread which is about to enter a deadlock situation generates a SQLException which in turn generates a rollback. Note that deadlock situations cannot occur in auto-commit mode. Within single SQL statements, InstantDB ensures that tables are always locked in the same order, thus preventing deadlocks.

Isolation Levels

InstantDB supports SERIALIZABLE and READ_UNCOMMITTED isolation levels. Serializable is the default. Read-uncommitted ignores locks and does not perform any locking itself. In InstantDB, Read-uncommitted is therefore a read only mode. It is most likely to be suitable for ad-hoc queries.

The other isolation levels, REPEATABLE_READ and READ_COMMITTED rely on sharable locks which InstantDB does not yet support.

DDL statements (CREATE/DROP) are performed in a separate system transaction. They are not performed in the current connection transaction. The system transaction is always in the serializable mode. Consequently, read-uncommitted transactions will give the appearance of being able to create and drop tables and indexes, even though it cannot perform add, update or delete operations on those tables. This is simply because it has switched to the system transaction for the duration of the DDL statement. All DDL statements commit the current connection transaction before switching to the system transaction.

If a DDL statement goes awry, then the system transaction will rollback, and so will the current connection transaction.


Two properties have been instroduced to the database properties file to control transaction processing:
  • transLevel controls how the journal file is used.
  • transImports determines the number of rows imported in an IMPORT statement before the current transaction is committed.
The possible values for transLevel are:
  • 0 - No journalling takes place. This is not recommended and should only be used in cases where it is known that the database can be easily deleted and re-constructed from another source.
  • 1 - Normal journalling (default). Transactions are journalled until the first checkpoint is reached. i.e. All transactions are inactive. At this point the journal is reset. This prevents the journal file from growing indefinitely.
  • 2 - Full journalling. In this mode, separate checkpoint records are generated and the journal continues to grow. This is intended for future mirroring capabilites and for occasional diagnostic use. It should not be used during normal operation. During recovery, it is only necessary to replay the journal from the last checkpoint.

Generally speaking, the transaction processing is very efficient and there is no need to use anything other than transLevel 1. The journal is only ever written to at the end of the file and even during a rollback requires minimal file seeks. In fact, if you're using SCSI disks, you are likely to gain a performance advantage by placing the system files, and therefore the journal file, on a separate disk.

transImports is designed to ensure that IMPORT statements perform regular commits. This prevents the journal file from growing throughout the import statement. Its default value is 100.