|
Basic Topics | Advanced Topics | Reference Topics |
| Documentation Home Page |
Transactions and Connections
Locking and Deadlocks
Isolation Levels
Configuration
XA Transactions
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 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 preempt one another.
Note that JDBC™ specifies that a transaction does not auto-commit until the statement is reused or closed. Consequently, if an interactive thread does not close or reuse the current statement, the transaction remains uncommitted and the thread retains all of its database locks.
Locking in InstantDB is at the table level. Row level locking is not currently supported.
InstantDB implements shareable read locks. So several transactions can query a table at the same time. Any transaction which which attempts to update a table will automatically be queued until all read locks have been released, at which point it will acquire an exclusive write lock.
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 explicitly committed, otherwise there may be no threads left to reuse 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 introduced to the database properties file to control transaction processing:
The possible values for transLevel are:
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.
The Java Transaction API (JTA) defines the means by which Java applications interact with XA compliant Transactions Managers (TM).
This style of transaction is somewhat different from the transaction processing model described above. In the XA model an XAResource (which maintains a one to one mapping with an XAConnection) may be switched between different transactions as the TM sees fit.
Further, an XAResource may be instructed to prepare a transaction. i.e. Allocate all necessary resources and guarantee that a commit will take place, or rollback if resources are not available.
Enhydra Enterprise's XA compliant driver wrapper provides much of this functionality on top of existing JDBC 1 drivers. This allows existing JDBC drivers to take part in managed transactions. However, 2-phase commits must be emulated when the driver wrapper is used.
InstantDB sub-classes Enhydra's StandardXAConnection and StandardXADataSource to create IdbXAConnection and IdbXADataSource classes respectively. These two classes allow the wrapper to perform genuine 2-phase commits provided the underlying JDBC driver implements the non-standard interface org.enhydra.instantdb.jdbc.ConnectionExtensions.
InstantDB's idbConnection class implements this ConnectionExtensions interface. So by using Enhydra Enterprise's XA driver wrapper, extended to use the above classes as the XADataSource and XAConnection, it is possible to use InstantDB with any XA compliant TM and to take advantage of genuine 2-phase commits.
In addition, the ConnectionExtensions interface has been implemented for the RmiJdbc driver. So it is possible to execute XA transactions and 2-phase commits across the network.
Note that the IdbXAConnection and IdbXADataSource classes only require a JDBC Connection to implement the ConnectionExtensions interface. They have no knowledge of InstantDB driver or database internals. Therefore any JDBC driver which chooses to implement the ConnectionExtensions interface can take advantage of the Enhydra Enterprise JDBC XA driver wrapper and perform genuine 2-phase commits.