|
Transaction ProcessingCommits 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.
|
 |
Previous Release Basic 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 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. 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:
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. |
||
