Database Transactions: ACID, Isolation Levels & Deadlocks

Database transactions are fundamental for ensuring data consistency and integrity in modern applications. They enable safe, reliable operations even with simultaneous access by multiple users.

What are Transactions?

Definition and Basics

A transaction is a logical unit of work consisting of one or more database operations. Transactions must be treated as an atomic unit - either all operations are executed successfully or none at all.

Transaction Properties

-- Transaction as logical unit
BEGIN TRANSACTION;

-- Operation 1: Debit account
UPDATE Konten SET saldo = saldo - 100 WHERE konto_id = 1;

-- Operation 2: Credit account  
UPDATE Konten SET saldo = saldo + 100 WHERE konto_id = 2;

-- Either both operations succeed or none
COMMIT;
-- or on error: ROLLBACK;

ACID Properties

Atomicity

Atomicity ensures that a transaction is either executed completely or not at all.

-- Example of atomicity
CREATE TABLE Transaktionen (
    trans_id INT PRIMARY KEY AUTO_INCREMENT,
    von_konto INT,
    zu_konto INT,
    betrag DECIMAL(10,2),
    status VARCHAR(20),
    zeitpunkt TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Atomic transfer
DELIMITER //
CREATE PROCEDURE ueberweisung(
    IN von_konto_id INT,
    IN zu_konto_id INT,
    IN betrag DECIMAL(10,2)
)
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        RESIGNAL;
    END;
    
    START TRANSACTION;
    
    -- Check if balance is sufficient
    DECLARE aktuelles_saldo DECIMAL(10,2);
    SELECT saldo INTO aktuelles_saldo FROM Konten WHERE konto_id = von_konto_id;
    
    IF aktuelles_saldo < betrag THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Insufficient balance';
    END IF;
    
    -- Debit money
    UPDATE Konten SET saldo = saldo - betrag WHERE konto_id = von_konto_id;
    
    -- Credit money
    UPDATE Konten SET saldo = saldo + betrag WHERE konto_id = zu_konto_id;
    
    -- Log transaction
    INSERT INTO Transaktionen (von_konto, zu_konto, betrag, status)
    VALUES (von_konto_id, zu_konto_id, betrag, 'ERFOLGREICH');
    
    COMMIT;
END //
DELIMITER ;

-- Call
CALL ueberweisung(1, 2, 100.00);

Consistency

Consistency ensures that the database remains in a consistent state after a transaction.

-- Example of consistency rules
CREATE TABLE Konten (
    konto_id INT PRIMARY KEY,
    inhaber VARCHAR(100),
    saldo DECIMAL(10,2) NOT NULL,
    CHECK (saldo >= 0)  -- Balance must not be negative
);

CREATE TABLE Ueberweisungsregeln (
    regel_id INT PRIMARY KEY,
    max_betrag_pro_tag DECIMAL(10,2),
    max_anzahl_pro_tag INT
);

-- Consistency-ensuring transaction
DELIMITER //
CREATE PROCEDURE konsistente_ueberweisung(
    IN von_konto_id INT,
    IN zu_konto_id INT,
    IN betrag DECIMAL(10,2)
)
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        RESIGNAL;
    END;
    
    START TRANSACTION;
    
    -- Business rule: check maximum amount
    DECLARE max_betrag DECIMAL(10,2);
    SELECT max_betrag_pro_tag INTO max_betrag 
    FROM Ueberweisungsregeln WHERE regel_id = 1;
    
    IF betrag > max_betrag THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Betrag exceeds maximum';
    END IF;
    
    -- Balance check (enforced by CHECK constraint)
    DECLARE aktuelles_saldo DECIMAL(10,2);
    SELECT saldo INTO aktuelles_saldo FROM Konten WHERE konto_id = von_konto_id;
    
    IF aktuelles_saldo < betrag THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Insufficient funds';
    END IF;
    
    -- Execute operations
    UPDATE Konten SET saldo = saldo - betrag WHERE konto_id = von_konto_id;
    UPDATE Konten SET saldo = saldo + betrag WHERE konto_id = zu_konto_id;
    
    COMMIT;
END //
DELIMITER ;

Isolation

Isolation ensures that concurrently executed transactions do not interfere with each other.

-- Example of isolation
-- Session 1:
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
START TRANSACTION;

-- Session 2 (parallel):
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
START TRANSACTION;

-- Session 1 reads data
SELECT * FROM Konten WHERE konto_id = 1;

-- Session 2 modifies data
UPDATE Konten SET saldo = 1500 WHERE konto_id = 1;
COMMIT;

-- Session 1 reads again (depending on isolation level)
SELECT * FROM Konten WHERE konto_id = 1;

Durability

Durability ensures that changes from a transaction are permanently stored.

-- Example of durability
-- After COMMIT, changes are permanent
START TRANSACTION;
UPDATE Konten SET saldo = 2000 WHERE konto_id = 1;
COMMIT;  -- Changes are now permanent

-- Even in case of system crash, changes are preserved
-- (through write-ahead logging and other mechanisms)

Isolation Levels

READ UNCOMMITTED

Lowest isolation level - allows “dirty reads”.

-- READ UNCOMMITTED example
-- Session 1:
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
START TRANSACTION;
UPDATE Konten SET saldo = 500 WHERE konto_id = 1;
-- Not yet committed!

-- Session 2:
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT * FROM Konten WHERE konto_id = 1;
-- Reads the uncommitted value (500) - dirty read!

-- Session 1:
ROLLBACK;  -- Change is rolled back

-- Session 2 has read invalid data

READ COMMITTED

Prevents dirty reads, but allows non-repeatable reads.

-- READ COMMITTED example
-- Session 1:
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
START TRANSACTION;
SELECT saldo FROM Konten WHERE konto_id = 1;  -- Reads 1000

-- Session 2:
START TRANSACTION;
UPDATE Konten SET saldo = 1500 WHERE konto_id = 1;
COMMIT;

-- Session 1 reads again:
SELECT saldo FROM Konten WHERE konto_id = 1;  -- Now reads 1500
-- Non-repeatable read!

REPEATABLE READ

Prevents dirty reads and non-repeatable reads, but allows phantom reads.

-- REPEATABLE READ example
-- Session 1:
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION;
SELECT * FROM Konten WHERE inhaber LIKE 'A%';  -- Reads 3 accounts

-- Session 2:
START TRANSACTION;
INSERT INTO Konten VALUES (4, 'Anna Schmidt', 2000);
COMMIT;

-- Session 1 reads again:
SELECT * FROM Konten WHERE inhaber LIKE 'A%';  -- Still 3 accounts
-- New row is not seen (no phantom read in MySQL)

SERIALIZABLE

Highest isolation level - prevents all anomalies.

-- SERIALIZABLE example
-- Session 1:
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
START TRANSACTION;
SELECT AVG(saldo) FROM Konten;  -- Calculates average

-- Session 2:
START TRANSACTION;
INSERT INTO Konten VALUES (5, 'Bernd Mueller', 3000);
-- Waits until session 1 is finished!

-- Session 1:
COMMIT;

-- Session 2 can now proceed
COMMIT;

Concurrency Control

Pessimistic Concurrency Control

Locks resources proactively to prevent conflicts.

-- Pessimistic Locking Example
-- Explicit Locks
START TRANSACTION;

-- Lock row
SELECT * FROM Konten WHERE konto_id = 1 FOR UPDATE;

-- Other transactions must wait
-- Session 2:
SELECT * FROM Konten WHERE konto_id = 1 FOR UPDATE;
-- Waits until Session 1 commits/rolls back

-- Perform operations
UPDATE Konten SET saldo = saldo - 100 WHERE konto_id = 1;

COMMIT;  -- Lock is released

Optimistic Concurrency Control

Assumes that conflicts are rare and resolves them if needed.

-- Optimistic Concurrency with Version Column
CREATE TABLE Produkte (
    produkt_id INT PRIMARY KEY,
    name VARCHAR(100),
    preis DECIMAL(10,2),
    bestand INT,
    version INT DEFAULT 0
);

-- Update with version check
DELIMITER //
CREATE PROCEDURE update_produkt_optimistic(
    IN produkt_id INT,
    IN neuer_preis DECIMAL(10,2),
    IN erwartete_version INT
)
BEGIN
    DECLARE affected_rows INT;
    
    START TRANSACTION;
    
    UPDATE Produkte 
    SET preis = neuer_preis, version = version + 1
    WHERE produkt_id = produkt_id AND version = erwartete_version;
    
    SET affected_rows = ROW_COUNT();
    
    IF affected_rows = 0 THEN
        ROLLBACK;
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Concurrency conflict - product was modified';
    ELSE
        COMMIT;
    END IF;
END //
DELIMITER ;

-- Application
-- First read
SELECT produkt_id, name, preis, version FROM Produkte WHERE produkt_id = 1;

-- Update with expected version
CALL update_produkt_optimistic(1, 29.99, 5);

Deadlocks

Deadlock Detection and Prevention

Deadlocks occur when transactions wait for each other.

-- Deadlock Example
-- Session 1:
START TRANSACTION;
UPDATE Konten SET saldo = saldo - 100 WHERE konto_id = 1;
-- Waits for konto_id = 2
UPDATE Konten SET saldo = saldo + 100 WHERE konto_id = 2;

-- Session 2 (in parallel):
START TRANSACTION;
UPDATE Konten SET saldo = saldo - 50 WHERE konto_id = 2;
-- Waits for konto_id = 1
UPDATE Konten SET saldo = saldo + 50 WHERE konto_id = 1;

-- DEADLOCK! Both wait for each other

Deadlock Prevention Strategies

-- 1. Consistent lock ordering
DELIMITER //
CREATE PROCEDURE sichere_ueberweisung(
    IN von_konto_id INT,
    IN zu_konto_id INT,
    IN betrag DECIMAL(10,2)
)
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        RESIGNAL;
    END;
    
    -- Always lock in the same order
    IF von_konto_id < zu_konto_id THEN
        SET @lock1 = von_konto_id;
        SET @lock2 = zu_konto_id;
    ELSE
        SET @lock1 = zu_konto_id;
        SET @lock2 = von_konto_id;
    END IF;
    
    START TRANSACTION;
    
    -- Locks in consistent order
    SELECT * FROM Konten WHERE konto_id = @lock1 FOR UPDATE;
    SELECT * FROM Konten WHERE konto_id = @lock2 FOR UPDATE;
    
    -- Perform operations
    IF von_konto_id < zu_konto_id THEN
        UPDATE Konten SET saldo = saldo - betrag WHERE konto_id = von_konto_id;
        UPDATE Konten SET saldo = saldo + betrag WHERE konto_id = zu_konto_id;
    ELSE
        UPDATE Konten SET saldo = saldo + betrag WHERE konto_id = zu_konto_id;
        UPDATE Konten SET saldo = saldo - betrag WHERE konto_id = von_konto_id;
    END IF;
    
    COMMIT;
END //
DELIMITER ;

-- 2. Timeout-based retry
DELIMITER //
CREATE PROCEDURE ueberweisung_mit_retry(
    IN von_konto_id INT,
    IN zu_konto_id INT,
    IN betrag DECIMAL(10,2)
)
BEGIN
    DECLARE retry_count INT DEFAULT 0;
    DECLARE max_retries INT DEFAULT 3;
    DECLARE deadlock_detected BOOLEAN DEFAULT FALSE;
    
    retry_loop: WHILE retry_count < max_retries DO
        BEGIN
            DECLARE EXIT HANDLER FOR 1213  -- Deadlock error code
            BEGIN
                SET deadlock_detected = TRUE;
                SET retry_count = retry_count + 1;
                IF retry_count < max_retries THEN
                    -- Short wait before retry
                    DO SLEEP(0.1 * retry_count);
                END IF;
            END;
            
            -- Execute transaction
            CALL sichere_ueberweisung(von_konto_id, zu_konto_id, betrag);
            
            -- Successful - leave loop
            LEAVE retry_loop;
        END;
        
        IF deadlock_detected AND retry_count >= max_retries THEN
            SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Max retries exceeded';
        END IF;
        
        SET deadlock_detected = FALSE;
    END WHILE;
END //
DELIMITER ;

Deadlock Monitoring

-- Get deadlock information (MySQL)
SHOW ENGINE INNODB STATUS;

-- Monitor deadlock transactions
SELECT 
    r.trx_id waiting_trx_id,
    r.trx_mysql_thread_id waiting_thread,
    r.trx_query waiting_query,
    b.trx_id blocking_trx_id,
    b.trx_mysql_thread_id blocking_thread,
    b.trx_query blocking_query
FROM information_schema.innodb_lock_waits w
INNER JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id
INNER JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id;

-- Display lock status
SELECT 
    object_name,
    lock_type,
    lock_mode,
    lock_status,
    engine_transaction_id
FROM performance_schema.data_locks
WHERE object_name = 'Konten';

Transaction Management in Different Databases

MySQL/MariaDB

-- MySQL-specific features
-- Control autocommit
SET autocommit = 0;  -- Manual transaction control
SET autocommit = 1;  -- Automatic commit (default)

-- Savepoints for partial rollbacks
START TRANSACTION;
UPDATE Konten SET saldo = saldo - 100 WHERE konto_id = 1;
SAVEPOINT sp1;

UPDATE Konten SET saldo = saldo - 50 WHERE konto_id = 2;
SAVEPOINT sp2;

-- Rollback to savepoint
ROLLBACK TO sp1;
COMMIT;  -- Only first change remains

-- XA Transactions for distributed systems
XA START 'xid1';
UPDATE Konten SET saldo = saldo - 100 WHERE konto_id = 1;
XA END 'xid1';
XA PREPARE 'xid1';
XA COMMIT 'xid1';

PostgreSQL

-- PostgreSQL-specific features
-- Transaction Isolation Levels
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

-- Advisory Locks (application-level locking)
SELECT pg_advisory_lock(12345);  -- Acquire lock
SELECT pg_advisory_unlock(12345);  -- Release lock

-- Transaction Snapshots
BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT * FROM Konten WHERE konto_id = 1;

-- In another session:
UPDATE Konten SET saldo = 2000 WHERE konto_id = 1;
COMMIT;

-- Original session still sees old value
SELECT * FROM Konten WHERE konto_id = 1;

Oracle

-- Oracle-specific features
-- Read-Only Transactions
SET TRANSACTION READ ONLY;
SELECT * FROM Konten;  -- Guarantees consistent view

-- Autonomous Transactions
DELIMITER //
CREATE PROCEDURE log_transaktion(
    IN transaktion_id INT,
    IN beschreibung VARCHAR(200)
)
AS
BEGIN
    -- Autonomous Transaction
    PRAGMA AUTONOMOUS_TRANSACTION;
    
    INSERT INTO Transaktionslog (trans_id, beschreibung, zeitpunkt)
    VALUES (transaktion_id, beschreibung, SYSTIMESTAMP);
    
    COMMIT;  -- Commit only for autonomous transaction
END;
//

-- Savepoints
SAVEPOINT sp1;
-- Operations
ROLLBACK TO sp1;

Best Practices for Transaction Management

1. Keep Transactions Short

-- Bad: Long transaction
START TRANSACTION;
SELECT * FROM grosse_tabelle;  -- Long query
-- ... many other operations ...
UPDATE kleine_tabelle SET wert = 1;
COMMIT;

-- Good: Short, focused transaction
SELECT * FROM grosse_tabelle;  -- Outside the transaction
START TRANSACTION;
UPDATE kleine_tabelle SET wert = 1;
COMMIT;

2. Choose the Right Isolation Level

-- For most use cases
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- For analytical queries
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

-- For critical financial operations
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

3. Implement Error Handling

-- Robust error handling
DELIMITER //
CREATE PROCEDURE robuste_transaktion()
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE,
                                   @errno = MYSQL_ERRNO,
                                   @text = MESSAGE_TEXT;
        
        ROLLBACK;
        
        -- Logging
        INSERT INTO error_log (error_time, error_code, error_message)
        VALUES (NOW(), @errno, @text);
        
        -- Re-raise error
        RESIGNAL;
    END;
    
    START TRANSACTION;
    
    -- Transaction logic
    INSERT INTO tabelle1 (wert) VALUES (1);
    UPDATE tabelle2 SET wert = 2 WHERE id = 1;
    
    COMMIT;
END //
DELIMITER ;

4. Use Connection Pooling

// Java example with connection pool
import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.SQLException;

public class TransactionService {
    private DataSource dataSource;
    
    public void executeInTransaction(TransactionCallback callback) {
        Connection conn = null;
        try {
            conn = dataSource.getConnection();
            conn.setAutoCommit(false);
            
            callback.execute(conn);
            
            conn.commit();
        } catch (SQLException e) {
            if (conn != null) {
                try {
                    conn.rollback();
                } catch (SQLException ex) {
                    ex.printStackTrace();
                }
            }
            throw new RuntimeException("Transaction failed", e);
        } finally {
            if (conn != null) {
                try {
                    conn.setAutoCommit(true);
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }
    
    @FunctionalInterface
    public interface TransactionCallback {
        void execute(Connection conn) throws SQLException;
    }
}

Exam-Relevant Concepts

Important ACID Properties

Property	Description	Implementation
Atomicity	All or nothing	Rollback, Write-Ahead Logging
Consistency	Consistent state	Constraints, Triggers
Isolation	No interference	Locks, Isolation Levels
Durability	Permanent storage	Redo Logs, Checkpoints

Isolation Levels Comparison

Level	Dirty Reads	Non-Repeatable Reads	Phantom Reads	Performance
READ UNCOMMITTED	✅	✅	✅	Highest
READ COMMITTED	❌	✅	✅	High
REPEATABLE READ	❌	❌	✅ (MySQL: ❌)	Medium
SERIALIZABLE	❌	❌	❌	Lowest

Typical Exam Questions

1. Explain ACID properties
2. Compare isolation levels
3. Implement deadlock avoidance
4. Choose the right transaction strategies
5. Analyze concurrency problems

Summary

Transaction management is fundamental for reliable database applications:

• ACID properties guarantee data integrity
• Isolation levels control concurrency behavior
• Deadlock avoidance ensures system stability
• Optimistic vs Pessimistic concurrency control
• Best practices optimize performance and reliability

Good transaction design requires understanding application requirements and underlying database mechanisms.

---

Recommended Reading: Databases