Transaction Safety

Secure transaction handling with timeouts, savepoint support, and isolation enforcement.

Configuration

# policy.yaml
transactions:
  # Allow transaction commands
  enabled: true

  # Maximum transaction duration (prevent long-running locks)
  max_duration: 30s

  # Maximum statements per transaction
  max_statements: 100

  # Allowed isolation levels
  allowed_isolation_levels:
    - "read committed"
    - "repeatable read"
    # serializable disabled for performance

  # Savepoints
  allow_savepoints: true

TXN-001PASS

Transaction Block Allowed

Valid Transaction

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = $1;
UPDATE accounts SET balance = balance + 100 WHERE id = $2;
COMMIT;

Expected Result

BEGIN
UPDATE 1
UPDATE 1
COMMIT

Transaction commands (BEGIN, COMMIT, ROLLBACK) are allowed. Each statement within the transaction is validated against the whitelist.

TXN-002BLOCKED

Long-Running Transaction Timeout

After 30 Seconds

ERROR: Transaction timeout
DETAIL: Transaction exceeded maximum duration of 30 seconds.
HINT: Transaction has been automatically rolled back. Break large operations into smaller batches.

Transactions exceeding the configured timeout are automatically rolled back to prevent resource exhaustion and lock contention.

TXN-003PASS

Savepoint Support

Using Savepoints for Partial Rollback

BEGIN;
INSERT INTO orders (user_id, total) VALUES ($1, $2);
SAVEPOINT before_items;
INSERT INTO order_items (order_id, product_id) VALUES ($3, $4);
-- If item insert fails:
ROLLBACK TO SAVEPOINT before_items;
COMMIT;

Savepoints allow partial rollback within a transaction. Useful for handling optional operations that may fail.

TXN-004BLOCKED

Disallowed Isolation Level

Attempt

BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;

Expected Response

ERROR: Isolation level not allowed
DETAIL: SERIALIZABLE is disabled in policy.
HINT: Use READ COMMITTED or REPEATABLE READ instead.

The ALG enforces allowed isolation levels to prevent performance issues from overly strict isolation.

TXN-005BLOCKED

Transaction Statement Limit

Transactions with more than max_statements are blocked to prevent runaway batch operations from consuming resources.