Data Management and Control Systems — Week 5

Transaction Management and Concurrency Control

1. Introduction to Transactions

In our previous sessions, we focused on securing data at rest, its structure, governance, and storage. Now, we turn our attention to securing data in motion; that is, during the complex operations that change it. This brings us to the critical concept of a Transaction.

A transaction is a single, logical unit of work that accesses and potentially modifies the contents of a database. It is a sequence of one or more SQL operations that must be executed as a single, indivisible package. The fundamental principle of a transaction is that it must be completed in its entirety or not at all. This “all-or-nothing” property is what ensures the database remains in a consistent state even in the face of system failures or concurrent access.

Consider the canonical example of a banking system transferring ₦10,000 from Account A to Account B. This operation is not a single action; it involves two distinct steps: debiting ₦10,000 from Account A and crediting ₦10,000 to Account B. If the system crashes after the debit but before the credit, ₦10,000 would effectively vanish from the system, leading to a serious financial inconsistency and a loss of user trust. A transaction bundles these two steps together, guaranteeing that if any part of the sequence fails, the entire operation is reversed, as if it never happened.

2. ACID Properties

The reliability of transactions is defined by four core properties, collectively known as ACID. This acronym forms the bedrock of reliable transaction processing.

First, Atomicity embodies the “all-or-nothing” principle. It ensures that every operation within a transaction is completed successfully. If any operation fails, the entire transaction is aborted, and the database is rolled back to its state before the transaction began. The database management system, typically using a transaction log, enforces this.

Second, Consistency ensures that a transaction transforms the database from one valid state to another. This means that every transaction must adhere to all defined rules, including constraints, cascades, and triggers. In our bank transfer, consistency guarantees that the total money in the system (Account A + Account B) remains the same before and after the transaction; it doesn’t create or destroy money.

Third, Isolation is the property that deals with concurrency. It ensures that the execution of multiple transactions concurrently will result in a system state that is equivalent to a state achieved if those transactions were executed one after another, serially. This means that incomplete or intermediate states of a transaction are not visible to other concurrent transactions, preventing them from interfering with each other.

Finally, Durability guarantees that once a transaction has been committed, its changes are permanent. These changes must persist even in the event of a system failure, such as a power outage or crash. This is typically achieved by writing the transaction’s changes to a non-volatile transaction log before the commit is reported as successful.

3. Concurrency and Its Challenges

Modern databases are multi-user environments. Concurrency, the ability of the database to serve multiple users or processes simultaneously, is essential for performance. However, uncontrolled concurrent access to the same data items can lead to several integrity problems.

A Dirty Read occurs when a transaction reads data that has been written by another concurrent, uncommitted transaction. The risk is that the first transaction might later roll back, making the data read by the second transaction invalid, or “dirty.” For example, Transaction T1 updates a balance but hasn’t committed; Transaction T2 reads this new balance. If T1 then rolls back, T2 is operating on a balance that never officially existed.

A Lost Update happens when two transactions both read the same data and then try to update it based on the value they read. The second transaction’s update overwrites the first one, effectively causing the first update to be lost. Imagine two clerks simultaneously trying to update a product’s stock quantity. Both read the current stock as 10. The first clerk sells 2 units and updates the stock to 8. The second clerk sells 3 units and, based on the original 10, updates the stock to 7. The sale of the first 2 units is lost.

A Phantom Read occurs when a transaction re-executes a query returning a set of rows that satisfies a search condition and finds that the set has changed due to another recently committed transaction. For instance, a transaction calculating the total salary of all employees in the ‘Engineering’ department might get a different result if another transaction commits the addition of a new engineer between the first and second calculation.

4. Concurrency Control Techniques

To prevent these anomalies, database systems employ Concurrency Control techniques.

The most common is Locking. A lock is a mechanism that prevents a transaction from performing an operation on a data item that conflicts with an operation already granted to another transaction. There are two primary types:

Shared Locks (S-Locks) for read operations, which can be held by multiple transactions simultaneously, and

Exclusive Locks (X-Locks) for write operations, which allow only one transaction to hold the lock on a data item at a time.

Another technique is Timestamp Ordering, where each transaction is assigned a unique timestamp. The system then ensures that the execution of transactions is equivalent to a serial execution in timestamp order by forcing transactions to abort and restart if they attempt to read or write data in a way that violates this order.

A third approach is Optimistic Concurrency Control. This method operates on the assumption that conflicts are rare. Transactions are allowed to proceed without locking, performing their operations in a private workspace. Only when a transaction is ready to commit does the system check for conflicts with other committed transactions. If a conflict is detected, the transaction is rolled back and must restart. This is efficient in environments with low contention.

5. Transaction Commands in SQL

In SQL, transaction control is straightforward. We explicitly manage transactions using commands like BEGIN (or START TRANSACTION) to mark the start of a transaction block, COMMIT to permanently save all changes made by the transaction, and ROLLBACK to undo all changes made in the transaction, reverting the database to its state before the transaction began.

Let’s look at the banking transfer example in SQL:

BEGIN; - Start the transaction

 UPDATE accounts SET balance = balance - 10000 WHERE account_id = 1; - Debit
 UPDATE accounts SET balance = balance + 10000 WHERE account_id = 2; - Credit

 - If we reach this point without errors, we commit.
 COMMIT;

 - If an error occurred in either UPDATE, we would instead execute:
 ROLLBACK;

The purpose of this block is to ensure the atomicity of the debit and credit operations. If the second UPDATE fails, perhaps due to a constraint violation, issuing a ROLLBACK would undo the first UPDATE, ensuring the accounts are left in a consistent state.

6. Isolation Levels

While perfect isolation (serializable execution) is the ideal, it often comes with a significant performance cost due to extensive locking. To provide flexibility, the SQL standard defines four Isolation Levels that allow developers to choose the degree of isolation and, by extension, the types of concurrency anomalies they are willing to tolerate in exchange for performance.

READ UNCOMMITTED is the lowest level. It allows dirty reads, non-repeatable reads, and phantom reads. It offers the best performance but the least safety.

READ COMMITTED, a common default in many databases, prevents dirty reads but allows non-repeatable reads and phantom reads.

REPEATABLE READ prevents dirty reads and non-repeatable reads but may still allow phantom reads.

Finally, SERIALIZABLE is the highest level, which fully isolates transactions, preventing all three anomalies, but at the cost of the highest lock overhead and potential for deadlocks.

The choice is a classic trade-off. You can set the isolation level for a transaction in SQL, for example: SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;. Selecting the appropriate level requires a careful analysis of the application’s tolerance for inconsistency versus its performance requirements.

Real-Life Example: Online Banking Transactions

An online banking platform is a perfect illustration of ACID properties in action. When a customer initiates a funds transfer, the system begins a transaction. It debits one account and credits another. The atomicity property ensures that if the network connection drops after the debit but before the credit, the entire transaction is rolled back upon timeout, and the customer’s original balance is restored.

To prevent issues like double withdrawals, where two simultaneous requests to withdraw from the same account might both read the old balance before either updates it, the system must implement robust concurrency control. Using a high isolation level like REPEATABLE READ or SERIALIZABLE on the accounts table, or implementing application-level pessimistic locking (e.g., “SELECT FOR UPDATE”), would ensure that once a transaction reads an account balance for update, it is locked until the transaction completes, preventing a lost update. Furthermore, durable logging ensures that once the “Transfer Successful” message is displayed, the transaction is permanently recorded and will survive a system crash.

Classroom Discussion / Group Activity

Activity: “Simulating Concurrency Problems”

Instructions: In your groups, you will be assigned a specific concurrency anomaly to simulate (Dirty Read, Lost Update, or Phantom Read).

1. Using the provided BankAccounts table, write two separate SQL transaction scripts that, when run concurrently, would demonstrate the assigned anomaly.

2. Execute these scripts in your database lab environment, timing the execution of each statement to force the anomaly to occur.

3. Observe and document the incorrect outcome.

4. Discuss and implement a solution. Which concurrency control technique, changing the isolation level, using explicit locks (SELECT … FOR UPDATE), or restructuring the transaction, would prevent this issue? Implement your solution and demonstrate that the anomaly no longer occurs.

Practical / Lab Session

Lab 5: Implementing Transaction Control

This lab will give you hands-on experience with the power of transactions and the effect of isolation levels.

Objective: To simulate fund transfers using transactions, observe rollback behavior, and test different isolation levels.

Steps:

1. Create the BankAccounts table and populate it with sample data.

CREATE TABLE BankAccounts (
 account_id SERIAL PRIMARY KEY,
 account_name VARCHAR(50),
 balance NUMERIC(12,2)
 );

 INSERT INTO BankAccounts (account_name, balance) VALUES
 ('Alice', 50000.00),
 ('Bob', 30000.00);

To confirm:

--Always use this to confirm
SELECT * FROM BankAccounts;

2. Simulate a Fund Transfer: Write a transaction to transfer ₦10,000 from Alice to Bob. Use BEGIN and COMMIT.

BEGIN;

UPDATE BankAccounts
SET balance = balance - 10000
WHERE account_name = 'Alice';

UPDATE BankAccounts
SET balance = balance + 10000
WHERE account_name = 'Bob';

COMMIT;

3. Simulate a Failure and Rollback: Write a transaction that includes an intentional error to see atomicity in action.

BEGIN;

UPDATE BankAccounts
SET balance = balance - 10000
WHERE account_name = 'Alice';

-- Intentional error to trigger rollback
SELECT 1/0;

UPDATE BankAccounts
SET balance = balance + 10000
WHERE account_name = 'Bob';

COMMIT;

After running this, you should observe that Alice’s balance was not debited because the entire transaction was rolled back.

4. Test Isolation Levels: Open two database sessions (e.g., two terminals or tabs). In both, set the isolation level and attempt concurrent operations.

Session 1: Start with READ COMMITTED

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

BEGIN;

UPDATE BankAccounts
SET balance = balance - 5000
WHERE account_name = 'Alice';
-- Leave this transaction open without committing

At this point, the row for Alice is locked by Session 1.

Session 2: Also use READ COMMITTED

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

SELECT balance FROM BankAccounts
WHERE account_name = 'Alice';

EXPECTED RESULT (READ COMMITTED)

You should not see the deducted ₦5,000. Session 2 will only see Alice’s last committed balance, because uncommitted updates from other transactions are not visible.

Weekly Assignment 5

Deliverables:

1. An SQL script that creates two concurrent transactions attempting to modify the same account data. The script should demonstrate a concurrency problem (e.g., Lost Update) and then show a corrected version using proper transaction control (e.g., with SERIALIZABLE isolation or pessimistic locking).

2. A short report (2 pages) analyzing which concurrency issues were demonstrated in the first version and explaining how your chosen control technique prevented them in the second version.

3. Screenshots of the query outputs from both the problematic and the corrected scenarios.

Due: Week 6

Reflection Question to ponder for your own learning: “Why is maintaining transaction atomicity and isolation critical in systems like online payments, medical databases, or e-voting platforms?”

Summary / Key Takeaways

A Transaction is a logical unit of work that must complete entirely or not at all, ensuring database consistency during operations that change data.

The ACID properties (Atomicity, Consistency, Isolation, Durability) are the guarantees that make transactions reliable and trustworthy.

Concurrency enables performance but introduces challenges like Dirty Reads, Lost Updates, and Phantom Reads.

Concurrency Control Techniques like Locking, Timestamp Ordering, and Optimistic Control are used to manage simultaneous access and prevent anomalies.

SQL provides commands (BEGIN, COMMIT, ROLLBACK) to define transactions and allows the selection of Isolation Levels (READ UNCOMMITTED to SERIALIZABLE) to balance performance against the risk of concurrency anomalies.

In critical systems like finance and healthcare, high isolation levels and robust transaction management are non-negotiable for ensuring data integrity, preventing financial loss, and maintaining life-critical accuracy.