Locking and Concurrency Control

Difficulty: Advanced
Generated on: 2025-07-10 02:33:03
Category: SQL Cheatsheet for Database Development

---

SQL Locking and Concurrency Control: Advanced Cheatsheet

1. Quick Overview

Locking and concurrency control mechanisms are essential for maintaining data consistency and integrity in multi-user database environments. They manage simultaneous access to data, preventing data corruption and ensuring that transactions are executed in a predictable and reliable manner. This cheatsheet covers various techniques, from basic locking mechanisms to more advanced isolation levels and optimistic concurrency control.

When to Use:

• When multiple users/applications need to access and modify the same data concurrently.
• When data consistency and integrity are critical.
• When implementing complex business transactions.

2. Syntax

a) Explicit Locking (Table/Row Level)

-- MySQL (MyISAM tables don't support row-level locking, only table locks)
LOCK TABLES table_name WRITE; -- Exclusive lock (write access)
LOCK TABLES table_name READ;  -- Shared lock (read access)
UNLOCK TABLES;

-- PostgreSQL
LOCK TABLE table_name IN ACCESS EXCLUSIVE MODE; -- Most restrictive lock, prevents all access
LOCK TABLE table_name IN ACCESS SHARE MODE;     -- Allows concurrent reads, prevents writes
LOCK TABLE table_name IN ROW EXCLUSIVE MODE;   -- Allows concurrent reads, prevents other row exclusive locks
LOCK TABLE table_name IN ROW SHARE MODE;        -- Prevents exclusive locks, allows shared row locks
UNLOCK TABLE table_name;                       -- (Implicitly released at end of transaction)

-- SQL Server
-- Row-level locking is automatic in most cases based on isolation level
-- Use hints to influence locking behavior (generally avoid if possible)
SELECT * FROM table_name WITH (UPDLOCK, HOLDLOCK); -- Example hint

-- Oracle
-- Row-level locking is automatic in most cases based on isolation level
-- Use hints to influence locking behavior (generally avoid if possible)
SELECT * FROM table_name FOR UPDATE; -- Locks rows returned by the query

b) Transaction Isolation Levels (ANSI Standard)

-- Setting Transaction Isolation Level
-- MySQL
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

-- PostgreSQL
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

-- SQL Server
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SET TRANSACTION ISOLATION LEVEL SNAPSHOT; -- SQL Server Specific

-- Oracle
-- Oracle only supports READ COMMITTED and SERIALIZABLE.
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
-- Read Committed is the default.

c) Optimistic Locking

-- Add a version column to your table
ALTER TABLE table_name ADD COLUMN version INT DEFAULT 0;

-- Example Update Statement with Optimistic Locking
UPDATE table_name
SET column1 = 'new_value',
    version = version + 1
WHERE id = 123
  AND version = original_version; -- 'original_version' is the version read at the start of the transaction

-- Check if the update was successful (rows affected > 0)
-- If no rows were affected, it means another transaction modified the row in the meantime.

3. Common Use Cases

• Inventory Management: Preventing overselling by ensuring only one transaction can decrement the inventory count at a time.
• Banking Applications: Ensuring that debit and credit transactions are atomic and prevent double-spending.
• E-commerce: Preventing race conditions during checkout processes. For example, ensuring available quantity is correctly decremented.
• Reservation Systems: Preventing double-booking of resources (e.g., seats, rooms).
• Data Warehousing (ETL): Ensuring data integrity during complex data transformations and loading processes.

4. Best Practices

• Keep Transactions Short: Long-running transactions hold locks for extended periods, increasing the likelihood of contention.
• Acquire Locks in a Consistent Order: Avoid deadlocks by ensuring that transactions acquire locks in a predefined order.
• Use the Lowest Necessary Isolation Level: Higher isolation levels provide greater consistency but reduce concurrency. Choose the lowest level that meets your application’s requirements. READ COMMITTED is generally a good default.
• Avoid Table Locks: Table locks can severely limit concurrency. Favor row-level locking whenever possible.
• Use Indexes: Indexes can speed up queries and reduce the duration of locks.
• Monitor Lock Contention: Use database monitoring tools to identify and resolve lock contention issues.
• Implement Deadlock Detection and Resolution: Most databases have built-in deadlock detection mechanisms. Implement retry logic in your application to handle deadlocks gracefully.
• Consider Optimistic Locking for Low-Contention Scenarios: Optimistic locking can improve performance in situations where conflicts are rare.
• Use Database Connection Pooling: Reduces the overhead of establishing new database connections, improving performance.
• Proper Error Handling: Ensure transactions are rolled back in case of errors to release locks and maintain data integrity.

5. Examples

a) Inventory Management (Pessimistic Locking) - PostgreSQL

-- Sample Table
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    quantity INT NOT NULL
);

INSERT INTO products (name, quantity) VALUES ('Widget', 10);

-- Transaction to decrement inventory
BEGIN;

-- Lock the row to prevent concurrent updates
SELECT * FROM products WHERE id = 1 FOR UPDATE;

-- Check if there is enough quantity
SELECT quantity FROM products WHERE id = 1;

-- Simulate checking the quantity in application code.
-- Let's assume the quantity returned is 10.

-- Decrement the quantity
UPDATE products SET quantity = quantity - 2 WHERE id = 1;

COMMIT;

-- After the transaction, the quantity of Widget will be 8.

b) Banking Transaction (ACID Properties) - MySQL (InnoDB)

-- Sample Tables
CREATE TABLE accounts (
    id INT PRIMARY KEY,
    balance DECIMAL(10, 2) NOT NULL
);

INSERT INTO accounts (id, balance) VALUES (1, 100.00);
INSERT INTO accounts (id, balance) VALUES (2, 50.00);

-- Transaction to transfer money from account 1 to account 2
START TRANSACTION;

-- Debit from account 1
UPDATE accounts SET balance = balance - 20.00 WHERE id = 1;

-- Credit to account 2
UPDATE accounts SET balance = balance + 20.00 WHERE id = 2;

-- Commit the transaction
COMMIT;

-- If any error occurs, rollback the transaction
-- ROLLBACK;

-- After the transaction, account 1 will have 80.00 and account 2 will have 70.00.

c) Optimistic Locking Example - SQL Server

-- Sample Table
CREATE TABLE orders (
    id INT PRIMARY KEY,
    product VARCHAR(255) NOT NULL,
    quantity INT NOT NULL,
    version INT NOT NULL DEFAULT 0
);

INSERT INTO orders (id, product, quantity, version) VALUES (1, 'Gadget', 5, 0);

-- Simulate two concurrent updates
-- Transaction 1
BEGIN TRANSACTION;
SELECT id, product, quantity, version FROM orders WHERE id = 1;
-- Application reads version = 0
-- User updates quantity to 8

UPDATE orders SET quantity = 8, version = version + 1 WHERE id = 1 AND version = 0;
SELECT @@ROWCOUNT; -- Returns 1 if the update was successful
COMMIT TRANSACTION;

-- Transaction 2
BEGIN TRANSACTION;
SELECT id, product, quantity, version FROM orders WHERE id = 1;
-- Application reads version = 0
-- User updates quantity to 10

UPDATE orders SET quantity = 10, version = version + 1 WHERE id = 1 AND version = 0;
SELECT @@ROWCOUNT; -- Returns 0 because the version is now 1, indicating a conflict
ROLLBACK TRANSACTION;

d) Demonstrating Isolation Levels - PostgreSQL (Important: Run these in separate sessions)

Session 1:

BEGIN;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SELECT balance FROM accounts WHERE id = 1; -- Assume balance is 100
UPDATE accounts SET balance = balance - 10 WHERE id = 1;
SELECT balance FROM accounts WHERE id = 1; -- Still shows 100 because not committed yet
COMMIT;

Session 2:

BEGIN;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SELECT balance FROM accounts WHERE id = 1; -- Shows 100 before Session 1 commits
-- Waits until Session 1 commits
SELECT balance FROM accounts WHERE id = 1; -- Shows 90 after Session 1 commits
COMMIT;

If Session 2 was using READ UNCOMMITTED, the second SELECT would show 90 before Session 1 committed, potentially leading to incorrect calculations.

6. Common Pitfalls

• Deadlocks: Occur when two or more transactions are blocked indefinitely, waiting for each other to release locks.
  • Solution: Implement deadlock detection and retry mechanisms. Design transactions to acquire locks in a consistent order. Keep transactions short.
• Lost Updates: Occur when two transactions read the same data, and both update it based on the initial read value. The last update overwrites the previous one.
  • Solution: Use appropriate isolation levels (e.g., REPEATABLE READ or SERIALIZABLE) or optimistic locking.
• Dirty Reads: Occur when a transaction reads uncommitted data from another transaction. If the uncommitted transaction is rolled back, the first transaction has read invalid data.
  • Solution: Avoid READ UNCOMMITTED isolation level.
• Non-Repeatable Reads: Occur when a transaction reads the same row multiple times, but the value changes between reads due to another transaction committing updates.
  • Solution: Use REPEATABLE READ or SERIALIZABLE isolation levels.
• Phantom Reads: Occur when a transaction executes a query that returns a set of rows, and another transaction inserts or deletes rows that match the query criteria. When the first transaction re-executes the query, it sees different rows.
  • Solution: Use SERIALIZABLE isolation level.
• Over-Locking: Using overly restrictive locks can unnecessarily reduce concurrency.
  • Solution: Analyze application requirements and choose the least restrictive isolation level that provides the necessary data consistency.
• Ignoring Deadlock Errors: Failing to handle deadlock errors can lead to application failures and data inconsistencies.
  • Solution: Implement retry logic to automatically retry transactions that fail due to deadlocks.

7. Database Variations

Feature	MySQL (InnoDB)	PostgreSQL	SQL Server	Oracle
Default Isolation	REPEATABLE READ	READ COMMITTED	READ COMMITTED	READ COMMITTED
Isolation Levels	READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SERIALIZABLE	READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SERIALIZABLE	READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SERIALIZABLE, SNAPSHOT	READ COMMITTED, SERIALIZABLE
Explicit Locking	LOCK TABLES, Row-level locks automatic	LOCK TABLE, SELECT ... FOR UPDATE	Hints (e.g., UPDLOCK, HOLDLOCK)	SELECT ... FOR UPDATE
Optimistic Locking	Supported (via version column)	Supported (via version column)	Supported (via version column)	Supported (via version column)
Deadlock Detection	Yes	Yes	Yes	Yes
Table Locking Granularity	Table-level (MyISAM), Row-level (InnoDB)	Table, Row	Page, Row, Table	Row, Block, Table
MVCC Support	Yes (InnoDB)	Yes	Yes (Snapshot Isolation)	Yes

Key Differences:

• Default Isolation Level: Varies significantly between databases. MySQL (InnoDB) defaults to REPEATABLE READ, while PostgreSQL, SQL Server, and Oracle default to READ COMMITTED.
• Isolation Level Support: Oracle only supports READ COMMITTED and SERIALIZABLE.
• Explicit Locking Syntax: Varies significantly. SQL Server and Oracle rely heavily on hints to influence locking behavior.
• MVCC (Multi-Version Concurrency Control): All listed databases support MVCC, which allows readers and writers to operate concurrently without blocking each other excessively.
• Table Locking Granularity: MySQL’s MyISAM engine only supports table-level locking, which can severely limit concurrency. InnoDB supports row-level locking.

This cheatsheet provides a comprehensive overview of SQL locking and concurrency control. Remember to choose the appropriate techniques based on your specific application requirements and database system. Always test your locking strategies thoroughly to ensure data consistency and performance.