GBase 8s Database Locking and Concurrent Transaction Scheduling

This article explores the transaction processing features of the GBase 8s database, revealing how it ensures ACID properties and effectively manages concurrent transactions.

Transaction Concepts and ACID Properties

Transaction

A transaction is a collection of operations on a database that are treated as a single unit, executing in order without interference from other operations.

A transaction is a set of operations on a database. This set is an indivisible, logically functional unit of work; all operations in the set must either be executed entirely or not at all during a single execution of the transaction.

Transaction Properties (ACID)

• Atomicity

All operations in the transaction set are either performed entirely or not performed at all during the transaction's execution.

• Consistency

The result of the transaction execution must leave the database in a consistent state.

• Isolation

All operations of a transaction are isolated from other transactions or operations. Concurrently executing transactions must not affect each other; the result of a transaction running alone must be the same as when it runs concurrently with other transactions.

• Durability

Durability means that once a transaction completes and is successfully committed, the results of all its operations on the database are permanently retained in the database.

Scheduling of Concurrent Transactions

1. Concurrency Issues and Concurrent Operations

In real-world applications, multiple transactions from different users often execute concurrently, interleaving with each other. This is known as concurrency in transactions.

Concurrent transactions can affect each other, disrupting normal execution, failing to guarantee ACID properties, and causing data errors.

Concurrent operations (Simultaneous Concurrency) refer to the operations within concurrent transactions. The biggest issue is that they easily lead to database inconsistency.

Example

Two ticket offices sell tickets for the same train on the same day. Transaction T1 at the first ticket office proceeds as follows:
(1) Read the current remaining number of tickets A, assuming A=50;
(2) Sell one ticket, the remaining number becomes A=A-1, i.e., A=49.

Transaction T2 at the second ticket office:
(1) Read the current remaining number of tickets A, assuming A=50;
(2) Sell one ticket, the remaining number becomes A=A-1, i.e., A=49.

If T1 and T2 execute concurrently in the following order:
(1) Read the current remaining number of tickets A, assuming A=50;
(2) Read the current remaining number of tickets A, assuming A=50;
(3) Sell one ticket, the remaining number becomes A=A-1, i.e., A=49;
(4) Sell one ticket, the remaining number becomes A=A-1, i.e., A=49.

The result is that only one ticket is reflected as sold, while actually two were sold. This could lead to the same seat being sold twice, causing a data inconsistency error in the database.

Concurrent operations cause data inconsistency problems, as shown in the following table. Transaction T1 modifies data object A (total tickets) to 49, but T2 reads A as 50 before T1's modification. T2 then modifies the value to 49 and writes it back to the database, overwriting T1's modification. Now A=49, but after selling two tickets the total should be 48, while the database still shows 49. This is clearly a data inconsistency error.

2. Scheduling

A schedule refers to the order in which transactions are executed. When multiple transactions execute one after another, it is called a serial schedule. When they execute simultaneously, it is called a concurrent schedule.

3. Conflicts

A conflict occurs when two operations (read or write) in a transaction schedule would change the result of their respective transactions if their execution order were swapped.

Operations from different transactions on different data objects (whether read or write) do not conflict; read operations from different transactions on the same data object also do not conflict.

4. Locking

The primary technique for concurrency control is locking. The objects of locking are data objects in the database, such as tables, records, attributes, and indexes in a relational database. A transaction requests a lock on a data object before performing operations on it.

After locking, transaction T gains control over the data object; until T releases its lock, no other transaction can perform any operation on that data object.

Locking is a queuing mechanism that orders parallel tasks according to the sequence of locks, turning parallel tasks into serial tasks.

5. Problems Caused by Locking

Locking technology partially solves data consistency issues resulting from concurrent transaction processing, but it introduces new problems such as livelocks and deadlocks.

• Livelock

In concurrent transaction processing, if transaction T1 locks data object R, and transaction T2 requests a lock on R, T2 must wait for T1 to release the lock. If transaction T3 also requests a lock on R, and after T1 releases the lock the system responds to T3's request first, T3 is allowed to lock R. T2 must still wait for T3 to release the lock. Then transaction T4 requests a lock on R, and after T3 releases it, the system responds to T4's request... T2 could wait indefinitely, unable to lock R. This is a livelock (live lock).

A livelock occurs because a transaction never gets the lock it needs. A first-come, first-served strategy can avoid livelocks. When multiple transactions request to lock the same data object, they are queued in the order of their lock requests. Once the lock on the data object is released, the first transaction in the queue is granted the lock.

• Deadlock

If transaction T1 locks data object R1, T2 locks R2, and then T1 requests a lock on R2, it must wait because T2 holds the lock on R2. Meanwhile, T2 requests a lock on R1, and it must wait because T1 holds the lock on R1. Now T1 waits for T2 to release the lock on R2, while T2 waits for T1 to release the lock on R1. Both transactions wait for each other forever and cannot proceed. This is a deadlock.

Deadlock: When two or more transactions in the system are waiting, each for another to release a lock so they can continue, none of them can proceed. This phenomenon is called a deadlock state.

Reproducing a Deadlock Scenario

In GBase mode (session 1):

Both sessions are in the same database. They each place a shared (S) lock on table a and table b respectively. When an update is attempted, session 1 waits for session 2 to release the S lock on table a, while session 2 waits for session 1 to release the S lock on table b, causing a deadlock.

In Oracle mode, however, this use case does not produce a deadlock. This is because in GBase mode, after starting a transaction, you can ensure that both sessions commit after performing updates, thus allowing both to proceed simultaneously.

In Oracle mode, only multi-statement mode is available. DML statements automatically start a transaction, which is ended by a commit/rollback, and DDL statements also commit the transaction. As a result, the two sessions may not execute concurrently; parallelism becomes serial execution, preventing a deadlock.

Conclusion

The transaction processing capabilities and concurrency control mechanisms of the GBase 8s database provide enterprises with a reliable, efficient, and secure data processing platform. Whether for financial transactions or online transaction processing, GBase 8s ensures data integrity and consistency, meeting enterprise requirements for database systems.