4. Administering a FrontBase Server
Table of Contents
This article describes the database concepts of transactions, isolation levels, locking discipline, and updatability as implemented by FrontBase to manage simultaneous access.
Simultaneous accessOne 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.
TransactionsA 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.
UpdatabilityThe updatability can be
READ WRITE. A transaction which has the updatability of
READ ONLYcannot modify the database.
Isolation levelSQL92 defines 4 isolation levels:
FrontBase defines one more isolation level:
Users accessing data in the database may experience the following phenomena:
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.
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.
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
Yes Yes Yes
No Yes Yes
No No Yes
No No No
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 READlocks rows as they are selected.
SERIALIZABLElocks the whole table. When you use FrontBase,
READ UNCOMMITTEDis upgraded to
VERSIONEDisolation level is only valid for
READ ONLYtransactions 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
VERSIONEDtransaction. Any number of
VERSIONEDtransactions can be ongoing at the same time, sharing committed versions of the database.
Locking DisciplineFrontBase introduces the concept of locking discipline. The locking discipline may have the following values:
PESSIMISTIClocking 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.
OPTIMISTIClocking 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
PESSIMISTIClocking 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.
If you have feedback or questions on this document, please send e-mail to email@example.com. Please reference the section number and topic. Thanks!!
©2000 FrontBase, Inc. All rights reserved.