FrontBase Documentation

Backtrack:   Welcome!     6. Original Documentation

Updated: 20-Nov-2000 prev — next Table of Contents

6.11. Database Synchronization

This document briefly describes the database concepts of transactions, isolation levels, locking discipline, and updatability. All concepts that are used for controlling simultaneous access to a FrontBase database.

Simultaneous access

One of the basic features of a database server is to provide users with parallel access to shared data, thus the database server must ensure that updates made to a database are performed in an orderly manner such 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 the 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, and you cannot commit a transaction that have failed, the only action to take 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, the updateability determines if the access is read only or read write, and 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. The updatability is quite important because transactions that are READ ONLY does not interfere.

Isolation level

SQL92 defines 4 isolation levels:

READ UNCOMMITTED
READ COMMITTED
REPEATABLE READ
SERIALIZABLE

and FrontBase defines one more:

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 not really existed.

Non-repeatable Read

A transaction read 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 would 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

In addition to updateability and isolation level, FrontBase introduces the concept of locking discipline. The locking discipline has the following values:

• PESSIMISTIC
• DEFERRED (also called upgradeable)
• OPTIMISTIC

PESSIMISTIC locking assumes that the given object will be changed, i.e. 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 further ado. When the transaction is committed, it is checked that the accessed objects weren't changed during the the transaction, if they were changed, the commit fails.

DEFERRED 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.

Locking and EOF

EOF is using OPTIMISTIC locking, a transaction is started by e.g. a fetch and is terminated when changes are saved. EOF only checks the objects that are going to be updated, which is not entirely correct, all objects that have been accessed should be checked. The user loads a number of rows, does some calculations and stores the result in a row. All the rows used for the calculation may be changed, which is undetected, and the result would be wrong.

FrontBase does implement OPTIMISTIC locking. The problem with EOF can be solved by allowing nested transactions on the client, start a transaction when the user select objects and commit it when the user saves the changes. Actual ROLLBACK and COMMIT should be made available to the user. If the server implements the locking, the locking in the EOF is redundant, and snapshots etc. may be turned off.

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!!

©2000 FrontBase, Inc. All rights reserved.