Introduction to GBase 8s Locking and Concurrency 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.

Concept of Transactions

A transaction is a set of operations in a database that are treated as a whole. This set of operations is executed in an ordered manner without being affected by other operations. A transaction (Transaction) is an indivisible logical unit of work, where all operations must either be fully executed or completely undone during a single execution.

Transaction Properties (ACID Properties)

• Atomicity: All operations within the transaction must be either fully executed or not executed at all.
• Consistency: The transaction should leave the database in a consistent state after execution.
• Isolation: The operations within a transaction are isolated from other operations, ensuring that concurrent transactions do not affect each other.
• Durability: Once a transaction is successfully committed, its effects on the database must be permanent.

Concurrency Transaction Scheduling

1. Concurrency Issues and Operations

In real-world applications, multiple transactions from different users often execute concurrently, leading to transaction concurrency issues. These concurrent transactions can interfere with each other, compromising the ACID properties and causing data errors.

Simultaneous Concurrency in databases refers to concurrent operations arising from concurrent transactions. The main issue is that these operations can lead to database inconsistencies.

Example:

Consider two ticket counters selling tickets for the same day and train. The first transaction (T1) performed by Counter 1 is:

1. Read the current remaining tickets, assuming there are 50.
2. Sell a ticket, reducing the remaining number to 49.

The second transaction (T2) by Counter 2 is:

1. Read the current remaining tickets, assuming there are 50.
2. Sell a ticket, reducing the remaining number to 49.

If T1 and T2 execute concurrently in the following order:

1. Read the remaining tickets as 50 (by T1).
2. Read the remaining tickets as 50 (by T2).
3. Sell a ticket, reducing the remaining number to 49 (by T1).
4. Sell a ticket, reducing the remaining number to 49 (by T2).

Although two tickets were sold, the final remaining count is 49, leading to a database inconsistency, as both transactions overwrote each other’s changes.

2. Scheduling

Scheduling refers to the execution order of transactions:

• Serial Schedule: Transactions execute sequentially.
• Concurrent Schedule: Transactions execute simultaneously.

3. Conflicts

A conflict occurs when two operations (read or write) from different transactions, if executed in a different order, would lead to different results. Operations on different data objects do not conflict.

4. Locking

Locking is a primary technique for concurrency control. Data objects like tables, records, attributes, and indexes are locked before a transaction performs any operations. The transaction that locks a data object controls it until the lock is released.

Locking ensures that parallel tasks are queued in the order of lock requests, effectively turning concurrent tasks into serial ones.

5. Locking Issues

While locking resolves data inconsistency issues caused by concurrent transactions, it introduces new problems, such as livelocks and deadlocks.

Livelock Issue

During the processing of concurrent transactions, if transaction T1 locks data object R, and then transaction T2 requests a lock on the same data object R, T2 must wait for T1 to release the lock on R. If transaction T3 also requests a lock on R, after T1 releases the lock on R, the system will first respond to T3's request, allowing T3 to lock R. At this point, T2 must continue to wait for T3 to release the lock on R. Then, transaction T4 requests a lock on R, and once T3 releases the lock on R, the system responds to T4’s request, and so on. T2 may end up waiting indefinitely and never acquiring the lock on R. This situation is called a livelock.

Livelock is also known as starvation, caused by a transaction never obtaining a lock. To avoid livelock, a first-come, first-served strategy can be used. When multiple transactions request locks on the same data object, the transactions can be queued according to the order of the lock requests. Once the lock on the data object is released, the system grants the lock to the first transaction in the queue.

Deadlock Issue

A deadlock occurs when transaction T1 locks data object R1, and transaction T2 locks data object R2. Then, T1 requests a lock on R2, but since T2 has already locked R2, T1 must wait for T2 to release the lock on R2. Meanwhile, T2 requests a lock on R1, but since T1 has already locked R1, T2 must wait for T1 to release the lock on R1. In this situation, both T1 and T2 are waiting for the other to release their respective locks, and neither transaction can continue execution. This results in a deadlock.

A deadlock occurs when two or more transactions are in a waiting state, and each transaction is waiting for the other to release its lock in order to proceed. This creates a situation where none of the transactions can continue execution, and the system is said to have entered a deadlock state.

Reproducing Deadlock Example in GBase Mode

Session 1 (GBase Mode):

Create database if not exists testdb with buffered log;
Database testdb;
Create table if not exists a(c1 int, c2 int);
Create table if not exists b(c1 int, c2 int);
Insert into a values(1,1);
Insert into a values(2,2);
Insert into b values(1,1);
Insert into b values(1,1);
!sh /tmp/rd129/onmode_I143_rd129
Set isolation to repeatable read;
Set lock mode to wait;
Begin;
Select * from b;                   -- Add S lock on table b
!echo "sql start sleep 5"
!sleep 15
!echo "sql sleep 5 done"
Update a set c1=6 where c2=2;      -- Attempt to add X lock on table a
Commit;
Close database;
!cat /tmp/rd129/out.log
Drop database testdb;

Session 2 (GBase Mode):

Onmode -I 143
(
Dbaccess -e-m testdb - <<!
Set isolation to repeatable read;
Set lock mode to wait;
Begin;
Select * from a;                 -- Add S lock on table a
!echo "sql start sleep 5"
!sleep 10
!echo "sql sleep 5 done"
Update b set c1=6 where c2=2;   -- Attempt to add X lock on table b
Commit;
!
) >/tmp/rd129/out.log 2>&1 &

In this scenario, Session 1 is waiting for Session 2 to release the lock on table a, while Session 2 is waiting for Session 1 to release the lock on table b, creating a deadlock.

In Oracle mode, the same example does not lead to a deadlock because Oracle uses multi-statement mode, where DML statements automatically initiate transactions and ensure more serialized execution.

GBase database's transaction processing and concurrency control provide a reliable, efficient, and secure data processing platform. Whether it's financial transactions or online transaction processing, GBase 8s ensures data integrity and consistency, meeting the demands of enterprise-level database systems.