FrontBase Documentation

Backtrack:
Welcome!
4. Administering a FrontBase Server
Updated: 20-Nov-2000
prev — next
Table of Contents

4.7. Understanding Transactions

This article describes the database concepts of transactions, isolation levels, locking discipline, and updatability as implemented by FrontBase to manage simultaneous access.

Simultaneous access
One of the basic features of a database server is to provide users with parallel access to shared data. The database server must ensure that updates made to a database are performed in an orderly manner so that data is not corrupted or lost.

Transactions
A transaction is used to control users access to the database. A user cannot access the database without a transaction, and all operations are performed in the context of a transaction. All changes made to the database by a user in the context of a transaction are made visible to other users when the transaction is committed. A transaction is, as seen from the outside, a single atomic operation.
During its existence a transaction may fail. When a transaction fails, it may not be committed. The only course of action is to start all over again with the hope that the transaction will not fail the next time around. A database server can, in principle, fail transactions at will, but a good server will only fail a transaction for a good reason. The only good reason is a deadlock.
When a transaction is created it is assigned an isolation level, an updateability, and a locking discipline. The isolation level determines how isolated a transaction is from other transactions. Updateability determines if the access is read only or read write. The locking discipline determines the type of lock used to synchronize access to database.

Updatability
The updatability can be READ ONLY or READ WRITE. A transaction which has the updatability of READ ONLY cannot modify the database.

Isolation level
SQL92 defines 4 isolation levels:

READ UNCOMMITTED

READ COMMITTED

REPEATABLE READ

SERIALIZABLE

FrontBase defines one more isolation level:

VERSIONED

Users accessing data in the database may experience the following phenomena:

Dirty reads
One transaction is writing some data to the database. The second is then reading that data, but the first rolls the transaction back. The second transaction has now read data that did not really exist.
Non-repeatable read
A transaction reads a row. A second transaction updates the values of the row and does a COMMIT. If the first transaction reads the row again it will get a different result.
Phantom
One transaction select some data in the database. A second transaction updates or inserts rows that satisfy the predicates that the first transaction used. The second transaction is committed. If the first transaction performs the select again, it will get a different result.

The table below shows which phenomena a given isolation level permits:

Dirty Reads Non-repeatable Phantom

READ UNCOMMITTED Yes Yes Yes

READ COMMITTED No Yes Yes

REPEATABLE READ No No Yes

SERIALIZABLE No No No

VERSIONED No No No

The amount of data that is locked is reflected by the isolation level. With READ UNCOMMITTED, nothing is locked. With READ COMMITTED, nothing is locked, but only data that is committed is read. REPEATABLE READ locks rows as they are selected. SERIALIZABLE locks the whole table. When you use FrontBase, READ UNCOMMITTED is upgraded to READ COMMITTED.
The VERSIONED isolation level is only valid for READ ONLY transactions and will keep the current version of the database for the duration of the transaction. Other transactions may modify the database, but the changes will not be visible to the VERSIONED transaction. Any number of VERSIONED transactions can be ongoing at the same time, sharing committed versions of the database.

Locking Discipline
FrontBase introduces the concept of locking discipline. The locking discipline may have the following values:

PESSIMISTIC

DEFERRED

OPTIMISTIC

PESSIMISTIC locking assumes that the given object will be changed. A transaction must wait until the object is available (unlocked). When a transaction is waiting there is a possibility for deadlocks. Deadlocks are detected and broken by failing one of the transactions causing the dead lock.
OPTIMISTIC locking assumes that a given object isn't changed by other transactions, and any changes are performed without delay. When the transaction is committed, FrontBase checks that the accessed objects weren't changed during the the transaction. If they were changed, the commit fails.
DEFERRED (sometimes called "upgradable") is a version of PESSIMISTIC locking which assumes that objects are only read. Initially, the lock is a read lock and if the object is updated, the lock is upgraded to a write lock.

	Dirty Reads	Non-repeatable	Phantom
`READ UNCOMMITTED`	Yes	Yes	Yes
`READ COMMITTED`	No	Yes	Yes
`REPEATABLE READ`	No	No	Yes
`SERIALIZABLE`	No	No	No
`VERSIONED`	No	No	No

If you have feedback or questions on this document, please send e-mail to doc-feedback@frontbase.com. Please reference the section number and topic. Thanks!!