A major objective in developing a database it to enables many users to access shared data concurrently. Concurrent access is relatively easy if all users are only reading data, as there is no way that they can interfere with one another, an example of this can be seen in the appendices in tests 9, 10 and 11. However, when two or more users are accessing the database simultaneously and at least one is updating data, there may be interference that can result in inconsistencies (Begg & Thomas, 1999). An example of this can be seen in the appendices in tests 18 and 19.
In a multi-session database system environment, there are usually two primary requirements. First the data must be read and modified in a consistent, predictable manner without the risk of loss or corruption (Elmsari & Navathe, 1994). That is to say, it must be kept in a consistent state. Second, concurrent data access by multiple users must be optimised with the highest possible performance to provide maximum productivity for all users of the system. In this report we will be discussing multi-user issues in databases including transaction management, recovery, locking, time stamping, concurrency control and indexes.
2. 0 Transactions Transactions are fundamental to the idea of controlled concurrent access to multi-user systems. A Transaction is a logical unit of a database processing that includes one or more database access operations. The operations may be insertion, deletion, modification or retrieval. The operation that forms a transaction can be embedded within an application program or can be specified interactively via SQL (Oxborrow, 1989). A Transaction consists of a series of actions carried out by a single user, or application program, which must be treated as an indivisible unit, e. g. real-time flight reservation.
If the operations carried out within a transaction are successful, all the changes made by that unit of work are committed to the database. A transaction transforms the database from one consistent state to another, if an operation during a transaction fails, the changes made by that transaction are rolled back, by doing this the database is restored to its state prior to the transaction. Facilities for fast-recovery of a database include, backing up; transaction logging; before-images and after-images and checkpoints. Backing up The backup utility allows you to back up the databases, database files, transaction logs, and write files.
There are two main types of database backups: full backups and incremental backups. Full backups create a copy of all data and journal files, providing a copy of the entire database at one point in time. All important data must be backed up fully on a daily basis (Gray & Reuter, 1993). Incremental backups create a copy of only the log files that have changed since the last backup. These files provide a copy of the changes made to the database since the last backup. Transaction Logging Transaction logging is a temporary maintenance tool used to track all database changes over a finite time period.
It functions as a write-ahead log of database activity therefore all insert, update, and delete transactions are recorded from beginning to end (Date, 2001). Descriptions of these changes are durably recorded on disk in a type of transaction log file known as an undo-redo log. There is an optional second log, called the redo log; this can be used to enhance system reliability by recording the transaction log in two places. Before Images and After Images The undo-redo (before-image file) contains a log of all recent database changes.
As transactions change the database during processing, the database manager writes one or more log records describing each change to the before-image file before making the change. It contains data that can restore the database to a consistent state after a failure by repeating (redoing) previously made changes. The redo log (after-image log) keeps a log of all database changes that have occurred after a full backup of the entire database. When recovering from failure you would restore the database from the backup and then reapply all changes made since the last backup, this process is called roll-forward.
It allows the database manager to read records from the after-image log and repeat all changes in the order originally made. Check Points These contain the identifiers of all transactions that are active at the time of the checkpoint. Checkpoints are markers on the log written after periodic incremental backup is performed (Toby, 1999). A checkpoint has two purposes; establish data consistency and secondly enable faster database recovery. As all database changes up to the checkpoint have been recorded in the transaction log, it makes it unnecessary to apply redo log entries prior to the checkpoint (Eaglestone, 1991).
Frequent checkpoints reduce the time required to recover in the event of a system failure. Example of Checkpoint 4. 0 Locking Locks are placed on data items to ensure that only one client at a time can access each data item. When the situation arises where a client requests for access to a data item which is in use by another client the former client must wait until the later client finishes the transaction process, as this is the stage at which the server unlocks the data (Eaglestone, 1991). Enforcing locks allows the server to grant access to the data items according to the time sequence of their request.
This is known as the resource lock, this prevents a user from reading and/or writing to a piece of data that is in use. An Example of this is shown in appendices 1. There are two types of resource locks: 1) Implicit versus Explicit – implicit locks are issued automatically by the DBMS based on an activity whereas explicit locks are issued by users requesting exclusive rights to the data (Toby, 1999). 2) Exclusive versus shared – Exclusive locks prevent other users from reading or updating the data. A shared lock allows others to read but not update the data (Eaglestone, 1991).