In multi-user database systems, multiple operations often need to be executed safely, without conflict, and with guaranteed data integrity. SQL provides a powerful mechanism for this through transactions and concurrency control. These tools ensure that changes to data are reliable, atomic, and consistent, even when multiple users are interacting with the database simultaneously.
Table of Contents
This chapter provides an in-depth exploration of:
- What transactions are and why they matter
- ACID properties
- How to manage transactions with SQL
- Concurrency control mechanisms
- Isolation levels and locking strategies
- Deadlocks and how to avoid them
- Best practices for reliable data processing
What is a Transaction?
A transaction is a sequence of one or more SQL operations executed as a single unit. The key idea is that all operations within a transaction must either complete successfully or leave the database unchanged.
Example:
START TRANSACTION;
UPDATE Accounts SET Balance = Balance - 500 WHERE AccountID = 1;
UPDATE Accounts SET Balance = Balance + 500 WHERE AccountID = 2;
COMMIT;
This example transfers money between accounts. If either update fails, the transaction should roll back.
ACID Properties
ACID is a foundational principle for transactional databases. It guarantees that a transaction behaves in a predictable and consistent manner.
| Property | Description |
|---|---|
| Atomicity | All operations succeed or none do (rollback on failure) |
| Consistency | Database transitions from one valid state to another |
| Isolation | Transactions do not interfere with each other |
| Durability | Once committed, changes are permanent—even in case of a crash |
Together, these ensure that data remains correct and reliable under concurrent access and failure scenarios.
Transaction Control Commands
START TRANSACTION / BEGIN
Begins a new transaction.
START TRANSACTION;
COMMIT
Saves all changes made in the transaction.
COMMIT;
ROLLBACK
Undoes all changes since the last START TRANSACTION.
ROLLBACK;
SAVEPOINT
Creates a save point within a transaction to which you can roll back.
SAVEPOINT sp1;
UPDATE Employees SET Salary = 0;
ROLLBACK TO sp1;
RELEASE SAVEPOINT
Removes a previously defined savepoint.
RELEASE SAVEPOINT sp1;
SET TRANSACTION
Defines transaction characteristics.
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
Isolation Levels – Controlling Concurrency
Isolation level defines how visible the intermediate state of a transaction is to others.
| Level | Description | Common Issues Prevented |
|---|---|---|
| READ UNCOMMITTED | Transactions can see uncommitted changes | None |
| READ COMMITTED | Only committed changes are visible | Prevents dirty reads |
| REPEATABLE READ | Same rows read twice will not change | Prevents dirty and non-repeatable reads |
| SERIALIZABLE | Transactions execute as if in serial order | Prevents dirty reads, phantom reads |
Examples:
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
Use higher isolation for accuracy, and lower isolation for performance.
Concurrency Issues
Dirty Reads
Reading data that has been modified by another uncommitted transaction.
Non-Repeatable Reads
Reading the same row twice within a transaction and getting different values.
Phantom Reads
A new row appears in a repeated query during a transaction.
Lost Updates
Two transactions overwrite each other’s updates.
Locks – Managing Access to Data
Locks prevent multiple users from accessing data in conflicting ways.
Types of Locks:
| Lock Type | Description |
|---|---|
| Shared (S) | Read-only lock; others can also read |
| Exclusive (X) | Write lock; prevents other access |
Row-Level vs Table-Level
- Row-level: Lock specific rows (better concurrency)
- Table-level: Lock entire table (simpler but restrictive)
Manual Locking:
LOCK TABLE Employees IN SHARE MODE; -- MySQL
SELECT * FROM Orders FOR UPDATE; -- PostgreSQL
Deadlocks
When two transactions hold locks and wait on each other.
Example:
- Transaction A locks row 1 and waits for row 2
- Transaction B locks row 2 and waits for row 1
Solution: Database detects and aborts one transaction.
Deadlock Prevention Techniques
- Access tables in consistent order
- Keep transactions short
- Use lowest necessary isolation level
- Avoid user input inside transactions
- Use timeouts and retry logic
Real-World Example: Online Banking System
Problem:
Transferring money between accounts should be atomic, isolated, and consistent.
Table:
CREATE TABLE Accounts (
AccountID INT PRIMARY KEY,
Balance DECIMAL(10,2)
);
Transaction:
START TRANSACTION;
UPDATE Accounts SET Balance = Balance - 1000 WHERE AccountID = 1;
UPDATE Accounts SET Balance = Balance + 1000 WHERE AccountID = 2;
COMMIT;
Using SAVEPOINT:
START TRANSACTION;
UPDATE Accounts SET Balance = Balance - 1000 WHERE AccountID = 1;
SAVEPOINT beforeCredit;
UPDATE Accounts SET Balance = Balance + 1000 WHERE AccountID = 2;
-- Suppose this fails
ROLLBACK TO beforeCredit;
-- Retry crediting or rollback completely
Transaction Management in Applications
Modern applications (e.g., web apps, mobile apps) use transactions to:
- Ensure consistency during business operations
- Maintain logs and audit trails
- Perform rollback on errors
Example in Python with psycopg2:
conn = psycopg2.connect(...)
cursor = conn.cursor()
try:
cursor.execute("BEGIN")
cursor.execute("UPDATE Accounts SET Balance = Balance - 500 WHERE ID = 1")
cursor.execute("UPDATE Accounts SET Balance = Balance + 500 WHERE ID = 2")
conn.commit()
except Exception as e:
conn.rollback()
print("Transaction failed:", e)
JDBC (Java):
conn.setAutoCommit(false);
try {
stmt.executeUpdate(...);
conn.commit();
} catch(SQLException e) {
conn.rollback();
}
Best Practices
- Use transactions around multiple related DML operations
- Use
COMMITexplicitly – don’t rely on auto-commit - Minimize transaction duration to avoid locks
- Catch exceptions and use
ROLLBACKwisely - Combine
SAVEPOINTfor complex transactions - Profile and optimize long-running transactions
Common Mistakes to Avoid
| Mistake | Risk |
|---|---|
| Forgetting to commit | Leaves locks or uncommitted changes |
| Holding transaction open for too long | Causes lock contention and timeouts |
| Mixing unrelated operations in one tx | Harder to roll back selectively |
| Not handling errors in stored procedures | Risk of partial data changes |
| Using SERIALIZABLE unnecessarily | Slower performance; deadlocks |
Monitoring Transactions
Use tools or queries to monitor active transactions:
PostgreSQL:
SELECT * FROM pg_stat_activity WHERE state = 'active';
SQL Server:
SELECT * FROM sys.dm_tran_active_transactions;
MySQL:
SHOW ENGINE INNODB STATUS;
These help identify long-running or blocked transactions.
Summary
Transactions are essential for ensuring correctness, reliability, and consistency in SQL-based systems. Combined with proper concurrency control, they form the backbone of robust, multi-user applications.
Key Takeaways:
- Transactions follow ACID principles
- Isolation levels control visibility and prevent conflicts
- Locks are crucial but must be used wisely
- Savepoints help manage complex logic
- Always handle transactions explicitly in applications
In the next chapter, we will explore User Access Control and Security in SQL, where you’ll learn how to manage users, roles, privileges, and protect your data against unauthorized access.