Using DBMS Features
Transactions combine several separate SQL executions, so that they can be seen as a single event that is executed completely (commit) or not at all (rollback). A typical example for a transaction is a money transfer. It consists of two steps: withdrawing an amount of money from one bank account and crediting another account with it. Both steps must be successful or they must be canceled. Transactions in SDBC are handled by the com.sun.star.sdbc.XConnection interface of connections. The transaction related methods of this interface are:
// transactions void setTransactionIsolation( [in] long level) long getTransactionIsolation() void setAutoCommit( [in] boolean autoCommit) boolean getAutoCommit() void commit() void rollback()
Usually all transactions are in auto commit mode, that means, a commit takes place after each single SQL command. Therefore to control a transaction manually, switch auto commit off using
setAutoCommit(false). The first SQL command without auto commit starts a transaction that is active until the corresponding methods have been committed or rolled back.
Afterwards, the auto commit mode can be reinstated using
Transactions bring about a synchronization problem. If data is read from a table, it is possible that the data has just been changed by a command of a transaction started by another process. If the other transaction is rolled back, there may be inconsistencies between the results and contents of the database.
Transaction isolation controls the behavior of the database in case of parallel transactions. There are several isolation levels:
|Values of constants com.sun.star.sdbc.TransactionIsolation|
|NONE||Indicates that transactions are not supported.|
|READ_UNCOMMITTED||Dirty reads, non-repeatable reads and phantom reads can occur. This level allows a row changed by one transaction to be read by another transaction before any changes in that row have been committed (a "dirty read"). If any of the changes are rolled back, the second transaction retrieves an invalid row.|
|READ_COMMITTED||Dirty reads are prevented; non-repeatable reads and phantom reads can occur. This level only prohibits a transaction from reading a row with uncommitted changes in it.|
|REPEATABLE_READ||Dirty reads and non-repeatable reads are prevented; phantom reads can occur. This level prohibits a transaction from reading a row with uncommitted changes in it, and it also prohibits the situation where one transaction reads a row, a second transaction alters the row, and the first transaction rereads the row, getting different values the second time (a "non-repeatable read").|
|SERIALIZABLE||Dirty reads, non-repeatable reads and phantom reads are prevented. This level includes the prohibitions in REPEATABLE_READ and further prohibits the situation where one transaction reads all rows that satisfy a WHERE condition, a second transaction inserts a row that satisfies that WHERE condition, and the first transaction rereads for the same condition, retrieving the additional "phantom" row in the second read.|
|Content on this page is licensed under the Public Documentation License (PDL).|