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
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
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:
The possible values for transLevel
- transLevel controls how the journal
file is used.
- transImports determines the number of
rows imported in an IMPORT statement before the current transaction
- 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.