Using DBMS Features

From Apache OpenOffice Wiki
Jump to: navigation, search
  • Using DBMS Features



Transaction Handling

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 setAutoCommit(true).

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).
Personal tools
In other languages