Active State — Transaction States | OneNoughtOne

0/278

Database Management SystemsTransaction States

Transaction States

LevelIntermediate

Duration75 mins

TopicTransaction States

1 / 5

Active State

The Living Transaction

When you execute a SQL statement like BEGIN TRANSACTION or implicitly start database operations, you're not just sending commands into a void—you're giving birth to a living entity within the database management system. This entity, your transaction, has a lifecycle with distinct states, each representing a critical phase in its journey toward completion or failure. The Active state is where every transaction begins its life. It's the state in which your transaction is currently executing, reading data, performing calculations, and making modifications. Understanding this state deeply is essential because: It's where most transaction time is spent — Complex transactions may remain Active for seconds, minutes, or even hours It's where ACID properties are actively maintained — The system works continuously to ensure isolation and consistency It's where resource consumption occurs — Locks, memory, log space, and CPU cycles are allocated It determines performance characteristics — How transactions behave while Active directly impacts system throughput

What You Will Learn

By the end of this page, you will understand the formal definition of the Active state, comprehend what occurs internally when a transaction is Active, recognize the operations permitted in this state, and appreciate the resource management and isolation mechanisms the DBMS employs to support Active transactions.

Formal Definition of the Active State

Let's establish a precise, formal understanding of what it means for a transaction to be in the Active state. Formal Definition: A transaction T is in the Active state if and only if: T has been initiated (either explicitly via BEGIN TRANSACTION or implicitly via the first statement) T has not yet executed its final operation (the commit or abort request) T has not encountered a failure that would transition it to the Failed state More formally, using state machine notation: T \in Active ⟺ (initiated(T) = true) \land (final_op_executed(T) = false) \land (failure_detected(T) = false) Key Characteristics of Active Transactions:

Defining Properties of Active State

•Currently Executing — The transaction is in the midst of performing its operations. It may be reading data, computing results, or writing modifications.
•Holding Resources — Active transactions typically hold locks, consume buffer pool space, and occupy entries in transaction tables.
•Generating Log Records — Every modification creates log records that are essential for recovery and rollback.
•Subject to Isolation Protocols — The transaction's reads and writes are governed by the configured isolation level.
•Interruptible by Failure — At any moment, a failure (system crash, constraint violation, deadlock detection) can transition the transaction out of Active state.

The Active State in Transaction Diagrams: In the classic transaction state diagram (which we'll explore fully in this module), the Active state is the entry point—the initial state that every transaction occupies immediately after initialization. From Active, a transaction can only transition to: Partially Committed — When all operations complete successfully and the commit is requested Failed — When any error or failure condition is detected There is no direct path from Active to Committed or Aborted—the transaction must pass through intermediate states first. This structure ensures that the DBMS has checkpoint opportunities to manage resources and verify correctness.

Transaction Initiation: Entering the Active State

How does a transaction enter the Active state? This seemingly simple question has nuanced answers depending on the database system and configuration. Explicit Transaction Start: The most straightforward way to initiate a transaction is through an explicit command: -- SQL Standard BEGIN TRANSACTION; -- or in some systems START TRANSACTION; When this command executes, the DBMS: Allocates a Transaction ID (TID or XID) — A unique identifier for tracking Creates a transaction descriptor — An in-memory structure holding transaction metadata Records the transaction in the active transaction table — A system structure tracking all live transactions Sets the transaction state to Active Notes the start timestamp — Used for MVCC and timeout detection

transaction-initiation.sql

-- Explicit transaction with isolation level specification
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
 
-- Check our transaction ID (PostgreSQL specific)
SELECT txid_current();
 
-- Our transaction is now Active
-- Any operations we perform here are part of this transaction
 
-- Reading data (the transaction holds shared locks or uses MVCC)
SELECT * FROM accounts WHERE account_id = 1001;
 
-- Modifying data (the transaction acquires exclusive locks)
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1001;
 
-- More operations can follow...
-- Transaction remains Active until we COMMIT or encounter an error

Implicit Transaction Start: Many database systems support autocommit mode where each individual statement is automatically wrapped in its own transaction: -- With autocommit enabled (default in many systems) UPDATE accounts SET balance = balance - 100; -- This IS a complete transaction In autocommit mode, the statement above: Implicitly begins a transaction (entering Active state) Executes the UPDATE Implicitly commits (transitioning through Partially Committed to Committed) All of this happens atomically from the user's perspective, but internally the state transitions still occur. Session-Level Transaction Settings: Some systems allow session-level control over implicit transaction behavior:

Implicit Transaction Modes by Database System
Database	Default Mode	Command to Change	Effect
PostgreSQL	Autocommit ON	BEGIN (starts explicit)	Each statement is its own transaction unless explicitly grouped
MySQL	Autocommit ON	SET autocommit = 0	Statements accumulate until explicit COMMIT/ROLLBACK
SQL Server	Autocommit ON	SET IMPLICIT_TRANSACTIONS ON	Statements begin transactions automatically; COMMIT required
Oracle	Autocommit OFF	SET AUTOCOMMIT ON	Each statement becomes its own transaction

Best Practice: Explicit Transactions

For any operation involving multiple statements that must succeed or fail together, always use explicit transaction boundaries. This makes your code's transactional intent clear, prevents accidental partial updates, and gives you control over isolation levels and other transaction properties.

Internal Structures for Active Transactions

When a transaction enters the Active state, the DBMS creates and maintains several internal data structures. Understanding these structures provides insight into the overhead of transactions and the sophistication of modern database systems. 1. Transaction Descriptor (Transaction Control Block): This is the primary data structure representing the transaction. It typically contains:

Transaction Descriptor Fields
Field	Description	Example Value
Transaction ID	Unique identifier for this transaction	TID-2847291
State	Current state in the transaction lifecycle	ACTIVE
Start Timestamp	When the transaction began (for MVCC, timeouts)	2024-01-15 14:32:01.234
Isolation Level	The configured isolation semantics	SERIALIZABLE
Read/Write Mode	Whether transaction can modify data	READ_WRITE
Lock List Pointer	Reference to locks held by this transaction	0x7F4A2100
Undo Log Pointer	Reference to undo log entries	0x8F3B1200
Session/Connection ID	The client connection owning this transaction	ConnID-4521
Savepoint Stack	Stack of declared savepoints	[SP1, SP2]
Deadlock Priority	Used for victim selection in deadlock resolution	NORMAL

2. Active Transaction Table (Transaction Table): The DBMS maintains a system-wide table of all active transactions. This table is crucial for: Concurrency control — Determining what transactions are running and their timestamps Recovery — Identifying incomplete transactions after a crash Deadlock detection — Building wait-for graphs Monitoring — Providing visibility into system activity The table is typically kept in shared memory for fast access and is protected by latches (lightweight locks). 3. Lock Table Entry: For each lock acquired, an entry is created (or updated) in the lock table:

lock-table-structure.pseudo
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
// Pseudocode representation of lock table structures
 
struct LockTableEntry {
    ResourceID      resource;       // What is locked (table, row, page, etc.)
    LockMode        mode;           // SHARED, EXCLUSIVE, UPDATE, etc.
    TransactionID   holder;         // Transaction holding the lock
    WaitQueue       waiters;        // Transactions waiting for this lock
    GrantedCount    count;          // For counting acquired lock grants
}
 
struct TransactionLockList {
    TransactionID   txn;            // Owner transaction
    LockEntry[]     held_locks;     // All locks held by this transaction
                                    // Used for lock release at commit/abort
}
 
// When transaction T acquires a lock on row R:
lock_entry = find_or_create_lock_entry(R);
if (compatible(lock_entry.mode, requested_mode)) {
    grant_lock(lock_entry, T, requested_mode);
    add_to_transaction_lock_list(T, lock_entry);
} else {
    add_to_wait_queue(lock_entry.waiters, T);
    // T may now be blocked, still in Active state but suspended
}

4. Log Buffer Entries: As an Active transaction makes modifications, log records are generated. These records reside in the log buffer before being flushed to stable storage: Undo Log Records — Information needed to reverse the operation (old values) Redo Log Records — Information needed to replay the operation (new values) Transaction Begin Record — Marks the start of the transaction Checkpoint References — Links to system checkpoints 5. Buffer Pool Pages: Active transactions may be working with data pages in the buffer pool. The buffer manager tracks: Pin counts — How many transactions are using each page Dirty flags — Whether pages have been modified LSN (Log Sequence Number) — Which log record last modified each page

Resource Overhead of Active Transactions

Every Active transaction consumes system resources: memory for descriptors and locks, log space for undo/redo records, and potentially buffer pool pages. This is why long-running transactions can stress system resources and why transaction timeouts exist. A well-designed application keeps transactions short and focused.

Operations Permitted While Active

During the Active state, a transaction can perform a wide variety of operations. Understanding what's possible—and what constraints apply—is essential for effective transaction programming. Read Operations:

Reading Data While Active

•SELECT queries — Retrieve data from tables, views, and the results of subqueries. The data seen depends on isolation level.
•Function calls that read data — Stored functions, aggregate computations, and procedural code that accesses tables.
•Index lookups — Using indexes to efficiently locate data (transparent to application code).
•Read-only cursors — Iterating over result sets returned by queries.
•System catalog queries — Reading database metadata (schemas, permissions, object definitions).

Write Operations:

Modifying Data While Active

•INSERT statements — Add new rows to tables. Generates redo log records and potentially updates indexes.
•UPDATE statements — Modify existing rows. Generates both undo (old values) and redo (new values) log records.
•DELETE statements — Remove rows (usually marks them as deleted rather than physically removing immediately).
•DDL operations — In some systems, CREATE/ALTER/DROP can be transactional and performed while Active.
•Sequence/identity operations — Generating new sequence values (note: these may not rollback).

Control Operations:

Transaction Control Operations in Active State
Operation	Purpose	Effect on State
SAVEPOINT name	Create a savepoint for partial rollback	Remains Active; savepoint recorded
ROLLBACK TO name	Undo work back to a savepoint	Remains Active; partial undo performed
RELEASE SAVEPOINT	Discard a savepoint (merge into main transaction)	Remains Active; savepoint removed
SET CONSTRAINTS	Change constraint checking mode	Remains Active; affects constraint timing
LOCK TABLE	Explicitly acquire table-level locks	Remains Active; lock granted or wait
SET LOCAL variable	Set session variable for transaction duration	Remains Active; variable set

savepoint-example.sql
SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
BEGIN TRANSACTION;
 
-- First, insert a new customer
INSERT INTO customers (customer_id, name, email) 
VALUES (1001, 'Alice Smith', 'alice@example.com');
 
-- Create a savepoint after customer creation
SAVEPOINT after_customer;
 
-- Attempt to insert an order
INSERT INTO orders (order_id, customer_id, total) 
VALUES (5001, 1001, 250.00);
 
-- Oops, we realize the order should be for a different amount
-- Roll back just the order, keeping the customer
ROLLBACK TO after_customer;
 
-- Insert the correct order
INSERT INTO orders (order_id, customer_id, total) 
VALUES (5001, 1001, 275.00);
 
-- Transaction is still Active at this point
-- We can continue with more operations or commit
 
COMMIT;  -- This would transition us to Partially Committed

Operations That Exit Active State

Two categories of operations cause a transaction to leave the Active state: (1) A COMMIT request, which transitions to Partially Committed, and (2) Any failure, error, or ROLLBACK request, which transitions to Failed. While Active, errors like constraint violations, deadlock detection, or system failures immediately move the transaction to Failed state.

Concurrency Control for Active Transactions

While in the Active state, transactions interact with other concurrent transactions. The DBMS employs sophisticated concurrency control mechanisms to ensure isolation while maximizing throughput. Lock-Based Concurrency Control: In traditional lock-based systems, Active transactions acquire locks as they access data: Shared Locks (S) — Acquired for read operations; multiple transactions can hold S locks simultaneously Exclusive Locks (X) — Acquired for write operations; only one transaction can hold an X lock Update Locks (U) — A hybrid used to prevent certain deadlock scenarios Intent Locks (IS, IX, SIX) — Hierarchical locks indicating intent at finer granularity When a lock cannot be immediately granted (due to conflict), the requesting transaction waits —it remains in the Active state but is not currently executing.

Converting Mermaid diagram...

MVCC (Multi-Version Concurrency Control): Modern systems like PostgreSQL, Oracle, and MySQL InnoDB use MVCC to reduce lock contention: Writers don't block readers — Read operations access an appropriate version of the data based on the transaction's snapshot Multiple versions coexist — Each modification creates a new version rather than overwriting in place Snapshot isolation — Active transactions see a consistent snapshot from their start time With MVCC, Active transactions spend less time waiting, but they may need to make visibility decisions (is this version visible to my snapshot?).

mvcc-example.sql

PostgreSQL

-- Session 1: Start a transaction
BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
-- This transaction gets snapshot at this moment
 
SELECT balance FROM accounts WHERE id = 100;
-- Returns: 1000 (let's say)
 
-- While Session 1 is Active, Session 2 runs:
 
-- Session 2:
BEGIN;
UPDATE accounts SET balance = 1500 WHERE id = 100;
COMMIT;
-- Session 2 completes
 
-- Back in Session 1 (still Active):
SELECT balance FROM accounts WHERE id = 100;
-- Still returns: 1000
-- Because Session 1 sees its original snapshot
 
-- Session 1 cannot see Session 2's change until it commits and
-- starts a new transaction (depending on isolation level)
 
COMMIT;

Deadlock Handling While Active: When multiple Active transactions wait for each other's locks in a circular manner, a deadlock occurs. The DBMS handles this by: Deadlock Detection — Periodically scanning wait-for graphs to find cycles Victim Selection — Choosing one transaction to abort (based on various criteria like age, work done, priority) State Transition — The victim transaction moves from Active to Failed The selected victim receives a deadlock error, and its current operation fails. The application must handle this error, typically by retrying the entire transaction.

Minimizing Contention

To reduce the time transactions spend waiting while Active: (1) Access resources in a consistent order to prevent deadlocks, (2) Keep transactions short to minimize lock hold times, (3) Use appropriate isolation levels—don't use SERIALIZABLE when READ COMMITTED suffices, (4) Consider optimistic concurrency control for low-conflict workloads.

Monitoring Active Transactions

Database administrators and developers need visibility into Active transactions for troubleshooting, performance tuning, and capacity planning. Modern database systems provide rich monitoring capabilities. PostgreSQL: Viewing Active Transactions:

monitoring-postgresql.sql

PostgreSQL

-- View all active transactions (Active state)
SELECT 
    pid,
    usename,
    state,
    backend_xid AS transaction_id,
    xact_start AS transaction_start,
    query_start,
    now() - xact_start AS transaction_duration,
    query AS current_query,
    wait_event_type,
    wait_event
FROM pg_stat_activity
WHERE state != 'idle' 
  AND backend_xid IS NOT NULL;
 
-- Find long-running transactions (potential problems)
SELECT 
    pid,
    usename,
    now() - xact_start AS duration,
    query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
  AND now() - xact_start > interval '5 minutes';
 
-- View locks held by active transactions
SELECT 
    l.pid,
    l.locktype,
    l.mode,
    l.granted,
    l.relation::regclass AS table_name,
    a.query
FROM pg_locks l
JOIN pg_stat_activity a ON l.pid = a.pid
WHERE NOT l.granted OR l.mode LIKE '%Exclusive%';

MySQL: Monitoring Active Transactions:

monitoring-mysql.sql

MySQL

-- View current InnoDB transactions
SELECT 
    trx_id,
    trx_state,
    trx_started,
    TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS duration_seconds,
    trx_rows_modified,
    trx_rows_locked,
    trx_isolation_level,
    trx_query
FROM information_schema.INNODB_TRX
WHERE trx_state = 'RUNNING';  -- Active transactions
 
-- View processes with current query
SHOW FULL PROCESSLIST;
 
-- Performance Schema for detailed transaction monitoring
SELECT 
    THREAD_ID,
    EVENT_NAME,
    STATE,
    TRX_ID,
    GTID,
    XID
FROM performance_schema.events_transactions_current
WHERE STATE = 'ACTIVE';

SQL Server: Monitoring Transactions:

monitoring-sqlserver.sql

SQL Server

-- View open transactions
DBCC OPENTRAN;
 
-- Detailed view using DMVs
SELECT 
    t.session_id,
    t.transaction_id,
    t.transaction_begin_time,
    DATEDIFF(SECOND, t.transaction_begin_time, GETDATE()) AS duration_seconds,
    t.transaction_type,  -- 1 = Read/Write, 2 = Read-Only
    t.transaction_state, -- 0 = Initializing, 1 = Initialized/Active
    s.login_name,
    s.host_name,
    r.command,
    r.wait_type
FROM sys.dm_tran_active_transactions t
LEFT JOIN sys.dm_exec_sessions s ON t.session_id = s.session_id
LEFT JOIN sys.dm_exec_requests r ON t.session_id = r.session_id
WHERE t.transaction_state = 1;  -- Active transactions
 
-- Find blocking chains
SELECT 
    blocking.session_id AS blocking_session,
    blocked.session_id AS blocked_session,
    blocked.wait_type,
    blocked.wait_time
FROM sys.dm_exec_requests blocked
JOIN sys.dm_exec_sessions blocking ON blocked.blocking_session_id = blocking.session_id;

Long-Running Active Transactions

Transactions that remain in the Active state for long periods can cause serious problems: lock contention (blocking other transactions), MVCC bloat (preventing vacuum/old version cleanup), log accumulation (filling transaction logs), and resource exhaustion (memory, connections). Set appropriate timeouts and monitor for runaway transactions.

Best Practices for Managing Active Transactions

Effective management of transactions in the Active state is crucial for system performance and reliability. Here are best practices developed from decades of database engineering experience:

Transaction Design Best Practices

•Keep transactions short — Minimize the time between BEGIN and COMMIT. Long Active transactions hold locks, block others, and consume resources. Aim for transactions measured in milliseconds, not seconds.
•Do expensive computation outside transactions — Perform complex calculations, API calls, or user interactions before starting the transaction. Only include actual database operations inside transaction boundaries.
•Access objects in consistent order — When transactions touch multiple tables, always access them in the same order. This prevents deadlock cycles where T1 waits for T2 waits for T1.
•Use appropriate isolation level — Don't use SERIALIZABLE by default. Choose the least restrictive level that meets your consistency requirements. READ COMMITTED is sufficient for many applications.
•Handle errors gracefully — Always wrap transactional code in error handling that properly commits or rolls back. A failed BEGIN...COMMIT block should never leave a transaction hanging.
•Use savepoints strategically — For complex transactions with recoverable sections, savepoints allow partial rollback without abandoning all work. But too many savepoints add overhead.
•Set statement and transaction timeouts — Prevent runaway transactions by configuring maximum duration. When a transaction exceeds the timeout, it automatically transitions to Failed.

transaction-best-practices.py
Python
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
import psycopg2
from contextlib import contextmanager
 
@contextmanager
def transaction(connection, isolation_level=None):
    """
    Context manager for properly handling transactions.
    Ensures commit on success, rollback on failure.
    """
    cursor = connection.cursor()
    
    try:
        # Optionally set isolation level before transaction
        if isolation_level:
            connection.set_isolation_level(isolation_level)
        
        # Start explicit transaction (entering Active state)
        cursor.execute("BEGIN")
        
        yield cursor
        
        # If we reach here, operations succeeded
        # Request commit (transition to Partially Committed)
        connection.commit()
        
    except Exception as e:
        # Any error triggers rollback (transition to Failed → Aborted)
        connection.rollback()
        raise  # Re-raise so caller knows what happened
    
    finally:
        cursor.close()
 
 
# Usage example: Transfer funds between accounts
def transfer_funds(connection, from_account, to_account, amount):
    """
    Atomic funds transfer using proper transaction management.
    """
    # Do validation OUTSIDE the transaction
    if amount <= 0:
        raise ValueError("Transfer amount must be positive")
    
    # Keep the actual transaction as short as possible
    with transaction(connection) as cursor:
        # Check balance (may acquire lock depending on isolation)
        cursor.execute(
            "SELECT balance FROM accounts WHERE id = %s FOR UPDATE",
            (from_account,)
        )
        balance = cursor.fetchone()[0]
        
        if balance < amount:
            # This will trigger rollback via exception
            raise ValueError("Insufficient funds")
        
        # Perform the transfer
        cursor.execute(
            "UPDATE accounts SET balance = balance - %s WHERE id = %s",
            (amount, from_account)
        )
        cursor.execute(
            "UPDATE accounts SET balance = balance + %s WHERE id = %s",
            (amount, to_account)
        )
        
        # Transaction commits automatically when context exits successfully
        # The time spent in Active state is minimal

The Art of Transaction Boundaries

Deciding what to include in a transaction is both a science and an art. The science: include exactly those operations that must be atomic. The art: balance consistency requirements against performance impact. Experienced engineers develop intuition for where to draw transaction boundaries based on application semantics and system characteristics.

Database Management SystemsTransaction States

Transaction States

LevelIntermediate

Duration75 mins

TopicTransaction States

1 / 5

Active State

The Living Transaction

When you execute a SQL statement like BEGIN TRANSACTION or implicitly start database operations, you're not just sending commands into a void—you're giving birth to a living entity within the database management system. This entity, your transaction, has a lifecycle with distinct states, each representing a critical phase in its journey toward completion or failure. The Active state is where every transaction begins its life. It's the state in which your transaction is currently executing, reading data, performing calculations, and making modifications. Understanding this state deeply is essential because: It's where most transaction time is spent — Complex transactions may remain Active for seconds, minutes, or even hours It's where ACID properties are actively maintained — The system works continuously to ensure isolation and consistency It's where resource consumption occurs — Locks, memory, log space, and CPU cycles are allocated It determines performance characteristics — How transactions behave while Active directly impacts system throughput

What You Will Learn

By the end of this page, you will understand the formal definition of the Active state, comprehend what occurs internally when a transaction is Active, recognize the operations permitted in this state, and appreciate the resource management and isolation mechanisms the DBMS employs to support Active transactions.

Formal Definition of the Active State

Let's establish a precise, formal understanding of what it means for a transaction to be in the Active state. Formal Definition: A transaction T is in the Active state if and only if: T has been initiated (either explicitly via BEGIN TRANSACTION or implicitly via the first statement) T has not yet executed its final operation (the commit or abort request) T has not encountered a failure that would transition it to the Failed state More formally, using state machine notation: T \in Active ⟺ (initiated(T) = true) \land (final_op_executed(T) = false) \land (failure_detected(T) = false) Key Characteristics of Active Transactions:

Defining Properties of Active State

•Currently Executing — The transaction is in the midst of performing its operations. It may be reading data, computing results, or writing modifications.
•Holding Resources — Active transactions typically hold locks, consume buffer pool space, and occupy entries in transaction tables.
•Generating Log Records — Every modification creates log records that are essential for recovery and rollback.
•Subject to Isolation Protocols — The transaction's reads and writes are governed by the configured isolation level.
•Interruptible by Failure — At any moment, a failure (system crash, constraint violation, deadlock detection) can transition the transaction out of Active state.

The Active State in Transaction Diagrams: In the classic transaction state diagram (which we'll explore fully in this module), the Active state is the entry point—the initial state that every transaction occupies immediately after initialization. From Active, a transaction can only transition to: Partially Committed — When all operations complete successfully and the commit is requested Failed — When any error or failure condition is detected There is no direct path from Active to Committed or Aborted—the transaction must pass through intermediate states first. This structure ensures that the DBMS has checkpoint opportunities to manage resources and verify correctness.

Transaction Initiation: Entering the Active State

How does a transaction enter the Active state? This seemingly simple question has nuanced answers depending on the database system and configuration. Explicit Transaction Start: The most straightforward way to initiate a transaction is through an explicit command: -- SQL Standard BEGIN TRANSACTION; -- or in some systems START TRANSACTION; When this command executes, the DBMS: Allocates a Transaction ID (TID or XID) — A unique identifier for tracking Creates a transaction descriptor — An in-memory structure holding transaction metadata Records the transaction in the active transaction table — A system structure tracking all live transactions Sets the transaction state to Active Notes the start timestamp — Used for MVCC and timeout detection

transaction-initiation.sql

-- Explicit transaction with isolation level specification
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
 
-- Check our transaction ID (PostgreSQL specific)
SELECT txid_current();
 
-- Our transaction is now Active
-- Any operations we perform here are part of this transaction
 
-- Reading data (the transaction holds shared locks or uses MVCC)
SELECT * FROM accounts WHERE account_id = 1001;
 
-- Modifying data (the transaction acquires exclusive locks)
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1001;
 
-- More operations can follow...
-- Transaction remains Active until we COMMIT or encounter an error

Implicit Transaction Start: Many database systems support autocommit mode where each individual statement is automatically wrapped in its own transaction: -- With autocommit enabled (default in many systems) UPDATE accounts SET balance = balance - 100; -- This IS a complete transaction In autocommit mode, the statement above: Implicitly begins a transaction (entering Active state) Executes the UPDATE Implicitly commits (transitioning through Partially Committed to Committed) All of this happens atomically from the user's perspective, but internally the state transitions still occur. Session-Level Transaction Settings: Some systems allow session-level control over implicit transaction behavior:

Implicit Transaction Modes by Database System
Database	Default Mode	Command to Change	Effect
PostgreSQL	Autocommit ON	BEGIN (starts explicit)	Each statement is its own transaction unless explicitly grouped
MySQL	Autocommit ON	SET autocommit = 0	Statements accumulate until explicit COMMIT/ROLLBACK
SQL Server	Autocommit ON	SET IMPLICIT_TRANSACTIONS ON	Statements begin transactions automatically; COMMIT required
Oracle	Autocommit OFF	SET AUTOCOMMIT ON	Each statement becomes its own transaction

Best Practice: Explicit Transactions

For any operation involving multiple statements that must succeed or fail together, always use explicit transaction boundaries. This makes your code's transactional intent clear, prevents accidental partial updates, and gives you control over isolation levels and other transaction properties.

Internal Structures for Active Transactions

When a transaction enters the Active state, the DBMS creates and maintains several internal data structures. Understanding these structures provides insight into the overhead of transactions and the sophistication of modern database systems. 1. Transaction Descriptor (Transaction Control Block): This is the primary data structure representing the transaction. It typically contains:

Transaction Descriptor Fields
Field	Description	Example Value
Transaction ID	Unique identifier for this transaction	TID-2847291
State	Current state in the transaction lifecycle	ACTIVE
Start Timestamp	When the transaction began (for MVCC, timeouts)	2024-01-15 14:32:01.234
Isolation Level	The configured isolation semantics	SERIALIZABLE
Read/Write Mode	Whether transaction can modify data	READ_WRITE
Lock List Pointer	Reference to locks held by this transaction	0x7F4A2100
Undo Log Pointer	Reference to undo log entries	0x8F3B1200
Session/Connection ID	The client connection owning this transaction	ConnID-4521
Savepoint Stack	Stack of declared savepoints	[SP1, SP2]
Deadlock Priority	Used for victim selection in deadlock resolution	NORMAL

2. Active Transaction Table (Transaction Table): The DBMS maintains a system-wide table of all active transactions. This table is crucial for: Concurrency control — Determining what transactions are running and their timestamps Recovery — Identifying incomplete transactions after a crash Deadlock detection — Building wait-for graphs Monitoring — Providing visibility into system activity The table is typically kept in shared memory for fast access and is protected by latches (lightweight locks). 3. Lock Table Entry: For each lock acquired, an entry is created (or updated) in the lock table:

lock-table-structure.pseudo
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
// Pseudocode representation of lock table structures
 
struct LockTableEntry {
    ResourceID      resource;       // What is locked (table, row, page, etc.)
    LockMode        mode;           // SHARED, EXCLUSIVE, UPDATE, etc.
    TransactionID   holder;         // Transaction holding the lock
    WaitQueue       waiters;        // Transactions waiting for this lock
    GrantedCount    count;          // For counting acquired lock grants
}
 
struct TransactionLockList {
    TransactionID   txn;            // Owner transaction
    LockEntry[]     held_locks;     // All locks held by this transaction
                                    // Used for lock release at commit/abort
}
 
// When transaction T acquires a lock on row R:
lock_entry = find_or_create_lock_entry(R);
if (compatible(lock_entry.mode, requested_mode)) {
    grant_lock(lock_entry, T, requested_mode);
    add_to_transaction_lock_list(T, lock_entry);
} else {
    add_to_wait_queue(lock_entry.waiters, T);
    // T may now be blocked, still in Active state but suspended
}

4. Log Buffer Entries: As an Active transaction makes modifications, log records are generated. These records reside in the log buffer before being flushed to stable storage: Undo Log Records — Information needed to reverse the operation (old values) Redo Log Records — Information needed to replay the operation (new values) Transaction Begin Record — Marks the start of the transaction Checkpoint References — Links to system checkpoints 5. Buffer Pool Pages: Active transactions may be working with data pages in the buffer pool. The buffer manager tracks: Pin counts — How many transactions are using each page Dirty flags — Whether pages have been modified LSN (Log Sequence Number) — Which log record last modified each page

Resource Overhead of Active Transactions

Every Active transaction consumes system resources: memory for descriptors and locks, log space for undo/redo records, and potentially buffer pool pages. This is why long-running transactions can stress system resources and why transaction timeouts exist. A well-designed application keeps transactions short and focused.

Operations Permitted While Active

During the Active state, a transaction can perform a wide variety of operations. Understanding what's possible—and what constraints apply—is essential for effective transaction programming. Read Operations:

Reading Data While Active

•SELECT queries — Retrieve data from tables, views, and the results of subqueries. The data seen depends on isolation level.
•Function calls that read data — Stored functions, aggregate computations, and procedural code that accesses tables.
•Index lookups — Using indexes to efficiently locate data (transparent to application code).
•Read-only cursors — Iterating over result sets returned by queries.
•System catalog queries — Reading database metadata (schemas, permissions, object definitions).

Write Operations:

Modifying Data While Active

•INSERT statements — Add new rows to tables. Generates redo log records and potentially updates indexes.
•UPDATE statements — Modify existing rows. Generates both undo (old values) and redo (new values) log records.
•DELETE statements — Remove rows (usually marks them as deleted rather than physically removing immediately).
•DDL operations — In some systems, CREATE/ALTER/DROP can be transactional and performed while Active.
•Sequence/identity operations — Generating new sequence values (note: these may not rollback).

Control Operations:

Transaction Control Operations in Active State
Operation	Purpose	Effect on State
SAVEPOINT name	Create a savepoint for partial rollback	Remains Active; savepoint recorded
ROLLBACK TO name	Undo work back to a savepoint	Remains Active; partial undo performed
RELEASE SAVEPOINT	Discard a savepoint (merge into main transaction)	Remains Active; savepoint removed
SET CONSTRAINTS	Change constraint checking mode	Remains Active; affects constraint timing
LOCK TABLE	Explicitly acquire table-level locks	Remains Active; lock granted or wait
SET LOCAL variable	Set session variable for transaction duration	Remains Active; variable set

savepoint-example.sql
SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
BEGIN TRANSACTION;
 
-- First, insert a new customer
INSERT INTO customers (customer_id, name, email) 
VALUES (1001, 'Alice Smith', 'alice@example.com');
 
-- Create a savepoint after customer creation
SAVEPOINT after_customer;
 
-- Attempt to insert an order
INSERT INTO orders (order_id, customer_id, total) 
VALUES (5001, 1001, 250.00);
 
-- Oops, we realize the order should be for a different amount
-- Roll back just the order, keeping the customer
ROLLBACK TO after_customer;
 
-- Insert the correct order
INSERT INTO orders (order_id, customer_id, total) 
VALUES (5001, 1001, 275.00);
 
-- Transaction is still Active at this point
-- We can continue with more operations or commit
 
COMMIT;  -- This would transition us to Partially Committed

Operations That Exit Active State

Two categories of operations cause a transaction to leave the Active state: (1) A COMMIT request, which transitions to Partially Committed, and (2) Any failure, error, or ROLLBACK request, which transitions to Failed. While Active, errors like constraint violations, deadlock detection, or system failures immediately move the transaction to Failed state.

Concurrency Control for Active Transactions

While in the Active state, transactions interact with other concurrent transactions. The DBMS employs sophisticated concurrency control mechanisms to ensure isolation while maximizing throughput. Lock-Based Concurrency Control: In traditional lock-based systems, Active transactions acquire locks as they access data: Shared Locks (S) — Acquired for read operations; multiple transactions can hold S locks simultaneously Exclusive Locks (X) — Acquired for write operations; only one transaction can hold an X lock Update Locks (U) — A hybrid used to prevent certain deadlock scenarios Intent Locks (IS, IX, SIX) — Hierarchical locks indicating intent at finer granularity When a lock cannot be immediately granted (due to conflict), the requesting transaction waits —it remains in the Active state but is not currently executing.

Converting Mermaid diagram...

MVCC (Multi-Version Concurrency Control): Modern systems like PostgreSQL, Oracle, and MySQL InnoDB use MVCC to reduce lock contention: Writers don't block readers — Read operations access an appropriate version of the data based on the transaction's snapshot Multiple versions coexist — Each modification creates a new version rather than overwriting in place Snapshot isolation — Active transactions see a consistent snapshot from their start time With MVCC, Active transactions spend less time waiting, but they may need to make visibility decisions (is this version visible to my snapshot?).

mvcc-example.sql

PostgreSQL

-- Session 1: Start a transaction
BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
-- This transaction gets snapshot at this moment
 
SELECT balance FROM accounts WHERE id = 100;
-- Returns: 1000 (let's say)
 
-- While Session 1 is Active, Session 2 runs:
 
-- Session 2:
BEGIN;
UPDATE accounts SET balance = 1500 WHERE id = 100;
COMMIT;
-- Session 2 completes
 
-- Back in Session 1 (still Active):
SELECT balance FROM accounts WHERE id = 100;
-- Still returns: 1000
-- Because Session 1 sees its original snapshot
 
-- Session 1 cannot see Session 2's change until it commits and
-- starts a new transaction (depending on isolation level)
 
COMMIT;

Deadlock Handling While Active: When multiple Active transactions wait for each other's locks in a circular manner, a deadlock occurs. The DBMS handles this by: Deadlock Detection — Periodically scanning wait-for graphs to find cycles Victim Selection — Choosing one transaction to abort (based on various criteria like age, work done, priority) State Transition — The victim transaction moves from Active to Failed The selected victim receives a deadlock error, and its current operation fails. The application must handle this error, typically by retrying the entire transaction.

Minimizing Contention

To reduce the time transactions spend waiting while Active: (1) Access resources in a consistent order to prevent deadlocks, (2) Keep transactions short to minimize lock hold times, (3) Use appropriate isolation levels—don't use SERIALIZABLE when READ COMMITTED suffices, (4) Consider optimistic concurrency control for low-conflict workloads.

Monitoring Active Transactions

Database administrators and developers need visibility into Active transactions for troubleshooting, performance tuning, and capacity planning. Modern database systems provide rich monitoring capabilities. PostgreSQL: Viewing Active Transactions:

monitoring-postgresql.sql

PostgreSQL

-- View all active transactions (Active state)
SELECT 
    pid,
    usename,
    state,
    backend_xid AS transaction_id,
    xact_start AS transaction_start,
    query_start,
    now() - xact_start AS transaction_duration,
    query AS current_query,
    wait_event_type,
    wait_event
FROM pg_stat_activity
WHERE state != 'idle' 
  AND backend_xid IS NOT NULL;
 
-- Find long-running transactions (potential problems)
SELECT 
    pid,
    usename,
    now() - xact_start AS duration,
    query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
  AND now() - xact_start > interval '5 minutes';
 
-- View locks held by active transactions
SELECT 
    l.pid,
    l.locktype,
    l.mode,
    l.granted,
    l.relation::regclass AS table_name,
    a.query
FROM pg_locks l
JOIN pg_stat_activity a ON l.pid = a.pid
WHERE NOT l.granted OR l.mode LIKE '%Exclusive%';

MySQL: Monitoring Active Transactions:

monitoring-mysql.sql

MySQL

-- View current InnoDB transactions
SELECT 
    trx_id,
    trx_state,
    trx_started,
    TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS duration_seconds,
    trx_rows_modified,
    trx_rows_locked,
    trx_isolation_level,
    trx_query
FROM information_schema.INNODB_TRX
WHERE trx_state = 'RUNNING';  -- Active transactions
 
-- View processes with current query
SHOW FULL PROCESSLIST;
 
-- Performance Schema for detailed transaction monitoring
SELECT 
    THREAD_ID,
    EVENT_NAME,
    STATE,
    TRX_ID,
    GTID,
    XID
FROM performance_schema.events_transactions_current
WHERE STATE = 'ACTIVE';

SQL Server: Monitoring Transactions:

monitoring-sqlserver.sql

SQL Server

-- View open transactions
DBCC OPENTRAN;
 
-- Detailed view using DMVs
SELECT 
    t.session_id,
    t.transaction_id,
    t.transaction_begin_time,
    DATEDIFF(SECOND, t.transaction_begin_time, GETDATE()) AS duration_seconds,
    t.transaction_type,  -- 1 = Read/Write, 2 = Read-Only
    t.transaction_state, -- 0 = Initializing, 1 = Initialized/Active
    s.login_name,
    s.host_name,
    r.command,
    r.wait_type
FROM sys.dm_tran_active_transactions t
LEFT JOIN sys.dm_exec_sessions s ON t.session_id = s.session_id
LEFT JOIN sys.dm_exec_requests r ON t.session_id = r.session_id
WHERE t.transaction_state = 1;  -- Active transactions
 
-- Find blocking chains
SELECT 
    blocking.session_id AS blocking_session,
    blocked.session_id AS blocked_session,
    blocked.wait_type,
    blocked.wait_time
FROM sys.dm_exec_requests blocked
JOIN sys.dm_exec_sessions blocking ON blocked.blocking_session_id = blocking.session_id;

Long-Running Active Transactions

Transactions that remain in the Active state for long periods can cause serious problems: lock contention (blocking other transactions), MVCC bloat (preventing vacuum/old version cleanup), log accumulation (filling transaction logs), and resource exhaustion (memory, connections). Set appropriate timeouts and monitor for runaway transactions.

Best Practices for Managing Active Transactions

Effective management of transactions in the Active state is crucial for system performance and reliability. Here are best practices developed from decades of database engineering experience:

Transaction Design Best Practices

•Keep transactions short — Minimize the time between BEGIN and COMMIT. Long Active transactions hold locks, block others, and consume resources. Aim for transactions measured in milliseconds, not seconds.
•Do expensive computation outside transactions — Perform complex calculations, API calls, or user interactions before starting the transaction. Only include actual database operations inside transaction boundaries.
•Access objects in consistent order — When transactions touch multiple tables, always access them in the same order. This prevents deadlock cycles where T1 waits for T2 waits for T1.
•Use appropriate isolation level — Don't use SERIALIZABLE by default. Choose the least restrictive level that meets your consistency requirements. READ COMMITTED is sufficient for many applications.
•Handle errors gracefully — Always wrap transactional code in error handling that properly commits or rolls back. A failed BEGIN...COMMIT block should never leave a transaction hanging.
•Use savepoints strategically — For complex transactions with recoverable sections, savepoints allow partial rollback without abandoning all work. But too many savepoints add overhead.
•Set statement and transaction timeouts — Prevent runaway transactions by configuring maximum duration. When a transaction exceeds the timeout, it automatically transitions to Failed.

transaction-best-practices.py
Python
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
import psycopg2
from contextlib import contextmanager
 
@contextmanager
def transaction(connection, isolation_level=None):
    """
    Context manager for properly handling transactions.
    Ensures commit on success, rollback on failure.
    """
    cursor = connection.cursor()
    
    try:
        # Optionally set isolation level before transaction
        if isolation_level:
            connection.set_isolation_level(isolation_level)
        
        # Start explicit transaction (entering Active state)
        cursor.execute("BEGIN")
        
        yield cursor
        
        # If we reach here, operations succeeded
        # Request commit (transition to Partially Committed)
        connection.commit()
        
    except Exception as e:
        # Any error triggers rollback (transition to Failed → Aborted)
        connection.rollback()
        raise  # Re-raise so caller knows what happened
    
    finally:
        cursor.close()
 
 
# Usage example: Transfer funds between accounts
def transfer_funds(connection, from_account, to_account, amount):
    """
    Atomic funds transfer using proper transaction management.
    """
    # Do validation OUTSIDE the transaction
    if amount <= 0:
        raise ValueError("Transfer amount must be positive")
    
    # Keep the actual transaction as short as possible
    with transaction(connection) as cursor:
        # Check balance (may acquire lock depending on isolation)
        cursor.execute(
            "SELECT balance FROM accounts WHERE id = %s FOR UPDATE",
            (from_account,)
        )
        balance = cursor.fetchone()[0]
        
        if balance < amount:
            # This will trigger rollback via exception
            raise ValueError("Insufficient funds")
        
        # Perform the transfer
        cursor.execute(
            "UPDATE accounts SET balance = balance - %s WHERE id = %s",
            (amount, from_account)
        )
        cursor.execute(
            "UPDATE accounts SET balance = balance + %s WHERE id = %s",
            (amount, to_account)
        )
        
        # Transaction commits automatically when context exits successfully
        # The time spent in Active state is minimal

The Art of Transaction Boundaries

Deciding what to include in a transaction is both a science and an art. The science: include exactly those operations that must be atomic. The art: balance consistency requirements against performance impact. Experienced engineers develop intuition for where to draw transaction boundaries based on application semantics and system characteristics.

Active State

The Living Transaction

Formal Definition of the Active State

Transaction Initiation: Entering the Active State

Internal Structures for Active Transactions

Operations Permitted While Active

Concurrency Control for Active Transactions

Monitoring Active Transactions

Best Practices for Managing Active Transactions

There’s more to understand.

Active State

The Living Transaction

Formal Definition of the Active State

Transaction Initiation: Entering the Active State

Internal Structures for Active Transactions

Operations Permitted While Active

Concurrency Control for Active Transactions

Monitoring Active Transactions

Best Practices for Managing Active Transactions

There’s more to understand.