4. Concurrency Control

4.1 Transactions

Transaction States
Managing a Transaction
Using a Savepoint

4.2 Multi-User Environment

Sessions
The Necessity of Concurrency Control
Lost Update Problem
Temporary Update Problem
Incorrect Summary Problem

4.3 Locks

Lock Concept
Shared and Exclusive Locks
Two-Phase Locking
Deadlock
Lock Granularity
Lock Types
Dealing Deadlock

4. Concurrency Control

This chapter introduces you to transactions and concurrency control. In addition to these concepts, we will describe how DBMaker maintains concurrent access and data accuracy in a multi-user environment using the lock mechanism. Section 4.1 presents the transaction concept and the functions used in managing a transaction. Section 4.2 discusses the necessity of concurrency control in a database system. Section 4.3 explains the concurrency control techniques in DBMaker.

4.1 Transactions

In a database, a transaction is a work unit that is composed of one or more SQL statements. It is an atomic operation. That means it should either complete a series of statements entirely or do nothing at all. Atomic, permanent, consistent, isolated, and serializable are the properties of a transaction.

Transaction States

A transaction must be in one of the following states:

Active - When a transaction starts to execute, it immediately goes into an active state. In the active state, a transaction can perform various database operations.

Partially Committed - When a transaction reaches its last statement in DBMaker (such as COMMIT WORK), it enters into the partially committed state. At this point, the transaction has completed its execution, but can still be aborted if an error occurs during the actual output. In this situation, the result cannot be written to disk. Therefore, a hardware failure may preclude its successful completion.

Committed - When a transaction has completed its execution successfully it enters into the committed state.

Failed - When a transaction cannot proceed to a normal conclusion, it enters into the failed state. This may be caused by hardware or logic errors, or a user abort of the transaction during an active state.

Aborted - When a transaction has ended unsuccessfully, it enters into the aborted state. In this situation, any change or effect that a transaction has applied to the database must be rolled back.

The state diagram corresponding to a transaction is shown in Figure 4-1.

Figure 4-1: The transaction states

Managing a Transaction

When you connect to DBMaker, a transaction starts automatically and enters the active state. DBMaker will automatically begin a new transaction after the preceding transaction has been terminated.

In general, a transaction is committed automatically by DBMaker each time you execute a statement. This is known as autocommit mode. In this mode, the lifetime of a transaction is the lifetime of a single SQL statement. That means one transaction is terminated at the end of an SQL statement and another begins with the next SQL statement. Each SQL statement is an independent transaction.

If you want a transaction to remain uncommitted until several SQL statements have been executed, you can change to manual commit mode by issuing a SET AUTOCOMMIT OFF command. In this mode, you are responsible for committing a transaction by using the SQL command COMMIT WORK, and you can execute as many SQL statements as you need before ending the transaction. To end the transaction, you issue a COMMIT WORK command to commit your changes, or issue a ROLLBACK WORK command to abort any changes you have made and terminate the transaction.

When you want to return to the autocommit mode, issue a SET AUTOCOMMIT ON command. The default transaction mode is AUTOCOMMIT ON.

Note: After a transaction is terminated, all resources allocated to this transaction are released.

Using a Savepoint

A savepoint is an intermediate point that can be arbitrarily declared within the context of a transaction. Using a savepoint allows you to roll back the work performed after the savepoint was declared within a transaction.

For example, when you execute a transaction with a series of statements, an error occurs while executing the twentieth statement. If you mark a savepoint between the fifteenth and sixteenth statements, the first fifteen statements can be preserved. You can roll back to the savepoint and begin issuing commands from the sixteenth SQL statement after correcting the error. You do not need to abort the transaction and resubmit all the statements. Figure 4-2 shows and example of this.

However, if you do not mark a savepoint between the fifteenth and sixteenth statements, you need to abort this transaction and resubmit the first fifteen statements. This is inconvenient and wastes time. A savepoint solves this problem completely.

Figure 4-2: Using Savepoints

With the SAVEPOINT and ROLLBACK TO ... commands, you can mark a savepoint and roll back to a specific savepoint. The use of these commands is illustrated below for the SAVEPOINT command:

dmSQL> SAVEPOINT savepoint_name;

And the ROLLBACK TO ... command:

dmSQL> ROLLBACK TO savepoint_name;

The savepoint_name is specified by the user. After rolling back to a savepoint, the system resources, such as locks, that were obtained after the savepoint are released.

4.2 Multi-User Environment

When more than one user is accessing a database, you must consider what can happen when they try to access the same data at the same time.

Sessions

A connection is a communication pathway between a user and DBMaker. A communication pathway is established through the use of shared memory or a network. Before using the database resources, you must establish a connection to DBMaker using the SQL statement as follows:

dmSQL> CONNECT TO database_name user_name password;

When you connect to a DBMaker database, the specific connection is called a session. A session lasts from the time you connect to a DBMaker database until the time you disconnect from it. You can only start one session to a database at a time. Furthermore, a session can only have one active transaction at a time.

The Necessity of Concurrency Control

In a multi-user database system environment, more than one user can connect to a database at the same time. This can result in many transactions updating the same database simultaneously. If no concurrency control mechanism is used, there are several situations which could result in data inconsistency, such as:

The lost update problem

The temporary update problem

The incorrect summary problem

Lost Update Problem

The lost update problem, shown in Figure 4-3, occurs when two transactions update a data item at approximately the same time. Transactions T1 and T2 read and modify the value of X, but use different calculations to modify the value. This results in the transactions each containing a different value for X. T1 writes the value it holds for X to the database after it is read but before it is written by T2. T2 then writes the value it holds for X to the database, overwriting the value written by T1. The value written by T1 is lost.

Figure 4-3: The lost update problem

Temporary Update Problem

The temporary update problem, shown in Figure 4-4, occurs when a transaction updates a value, but is rolled back after another transaction updates the same value. Transaction T1 reads and modifies the value of X, writes it back to the database, and then continues with other commands. While transaction T1 continues executing, transaction T2 reads the value of X, modifies it to a new value, and writes it back to the database. Transaction T1 then fails before completion, and must roll back all values to restore the database to its original status. The database management system restores the original value of X, overwriting the value written by transaction T2. The value of X calculated by transaction T2 exists only temporarily.

Figure 4-4: The temporary update problem

Incorrect Summary Problem

The incorrect summary problem, shown in Figure 4-5, occurs when a transaction is calculating the aggregate sum of a number of records while other transactions are updating those records. Transaction T1 calculates the aggregate sum using the values of X and Y at the same time transaction T2 is modifying those values. Transaction T2 updates the value of X before transaction T1 uses it to calculate the sum, and updates the value of Y after transaction T1 uses it to calculate the sum. This results in transaction T1 using some values to calculate the sum before they are updated, and using others after they are updated. When both transactions complete, the value of the sum is incorrect with respect to the values in the database.

Figure 4-5: The incorrect summary problem

There are various techniques to solve the concurrency problems, such as locks and time stamps. The next section shows you how the locking technique is applied in DBMaker to control concurrent execution of transactions.

4.3 Locks

In this section, we will first give you a description of the lock concept. Then, we will introduce the DBMaker lock mechanism, including lock granularity and lock modes. Finally, we will show you how to deal with deadlock.

Lock Concept

In general, a multi-user database system uses several forms of locking to synchronize the access of concurrent transactions. Before accessing the data objects (such as tables and tuples), a transaction must lock those data objects.

DBMaker locking is fully automatic and does not require any user action. Implicit locking occurs in all SQL statements; the users do not need to explicitly lock any data objects in the database.

Shared and Exclusive Locks

In general, two types of locking are used to allow multiple-read with single-write operations in a multi-user database.

Share Locks (S) - A share lock means that a transaction involves a read operation on a data object. To support a higher degree of data concurrency, several transactions can acquire share locks on the same data object at the same time.

Exclusive Locks (X) - An exclusive lock means that a transaction involves an update operation on a data object. This transaction is the only one which can access the object until the exclusive lock is released.

Two-Phase Locking

The two-phase locking protocol is used to ensure the serializability of transactions. In the two-phase locking protocol, each transaction must issue all lock requests before it can issue any unlock requests. As its name suggests, this protocol can be divided into two phases:

Expanding (growing) phase - This phase allows the transaction to issue any new lock requests that are required. Unlock requests are not permitted in this phase.

Shrinking phase - This phase allows the transaction to release locks that were acquired in the expanding phase. New lock requests are not permitted in this phase.

The two-phase locking protocol is currently used by DBMaker to provide concurrency control by serializing transactions.

Deadlock

When two or more transactions are waiting for the release of data locked by the other transactions before it can proceed, a deadlock occurs. Figure 4-6 shows an example of deadlock that occurs with transactions T1 and T2. T1 is waiting for T2 to release the share lock of X, while T2 is waiting for T1 to release the share lock of Y. Therefore deadlock occurs and the system will wait indefinitely.

Figure 4-6: The deadlock problem

Lock Granularity

There are three granularity levels of data locks in DBMaker: relation (table), page, and tuple (row). A relation contains several pages, and a page contains several tuples.

A lock applied on a higher level implies that the lower levels will have the same lock applied to them also. For example, if a user gets an exclusive lock (X lock) on a relation, all pages and tuples that are included in this relation will have the X lock applied to them also. Therefore, no user can access any tuple or page from this relation. However, if a user gets an X lock on a tuple, another user can get an X lock on another tuple simultaneously. There is no interference between two objects at the same level when using the X lock. Figure 4-7 shows the lock granularity (levels) in DBMaker.

Relation

Page

Tuple

Figure 4-7: Lock granularity

Using a higher lock granularity results in a lower degree of data concurrency. In contrast, the higher lock granularity uses fewer system resources (such as shared memory). Obviously, selecting the lock granularity level is a trade-off between concurrency and resources. The default lock granularity level is page in DBMaker, but if you require a different lock granularity it can be specified when you are creating a table. Refer to the chapter "DBMaker Storage Architecture" for more information.

Lock Types

The main lock modes (types) supported in DBMaker are shared (S) and exclusive (X) locks. More than one user can have an S lock on a data object simultaneously, but only one user can have an X lock on a data object. In addition to S and X locks, another lock mode called an intention lock is supported by DBMaker.

When a data object is locked, the system will automatically assign an intention lock to the next higher granularity object. For example, an S lock specified on a tuple will generate an intention S (IS) lock on the page which includes this tuple, and an IS lock on the relation which the tuple belongs to. The supported intention lock modes are:

IS - Indicates that the S lock is specified at a lower granularity.

IX - Indicates that the X lock is specified at a lower granularity.

SIX - Indicates that an S lock is specified at the current granularity and an X lock is specified at a lower granularity. This is a combination of S and IX locks.

The result from the compatibility of each of the lock modes is listed in Table 4-1. T represents true, which means the matrix for each of the two lock modes are compatible and can exist on a data object simultaneously. F represents false, which means the matrix for each of the two lock modes are not compatible and can not exist simultaneously.

Table 4-1: Compatibility matrix for lock modes

If a lock request on a data object conflicts with an existing lock on that object, this request will not execute until the existing lock is released, or until the waiting time for the lock request times out. If the error message 'Lock timeout' is returned to the user, the waiting time for the lock has expired. The default waiting time is 5 seconds. However, users can specify a different waiting time by setting the value of the DB_LTIMO keyword in the dmconfig.ini file to another value according to their individual requirements. The following example shows an example for setting the waiting time to 8 seconds.

DB_LTIMO = 8;

Dealing Deadlock

By analyzing the "wait for" graph, DBMaker can automatically detect a deadlock situation. If a deadlock is detected, a victim transaction will be aborted to solve the deadlock problem.

In the previous example (Figure 4-6), DBMaker will detect deadlock when transaction T2 issues an X lock on Y. In such a situation, transaction T2 will be aborted to resolve the deadlock problem. The user executing transaction T2 will receive the error message: "transaction aborted due to deadlock". Figure 4-8 shows an example of this.

Figure 4-8: Dealing Deadlock