SQL TRANSACTION

Complete SQL TRANSACTION Technical Guide

1. Fundamental Syntax

START TRANSACTION;
-- or BEGIN (in some databases)
-- SQL statements
COMMIT;
-- or ROLLBACK;

ACID

Atomicity: All operations succeed or none do
Consistency: Database moves between valid states
Isolation: Concurrent transactions don't interfere
Durability: Committed changes survive crashes

2. Isolation Levels

Standard Levels (ANSI SQL)

-- Setting isolation level
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- PostgreSQL
BEGIN ISOLATION LEVEL REPEATABLE READ;

-- SQL Server
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;

Level	Dirty Reads	Non-Repeatable Reads	Phantoms
READ UNCOMMITTED	Possible	Possible	Possible
READ COMMITTED	No	Possible	Possible
REPEATABLE READ	No	No	Possible
SERIALIZABLE	No	No	No

3. Savepoints & Nested Transactions

BEGIN;
INSERT INTO accounts(id, balance) VALUES (1, 100);
SAVEPOINT first_insert;
INSERT INTO accounts(id, balance) VALUES (2, 200);
-- Oops, need to undo just the second insert
ROLLBACK TO SAVEPOINT first_insert;
-- First insert remains, second is rolled back
COMMIT;

Partial rollback: Undo specific operations while keeping others
Nested transactions: Simulated using savepoints
Error recovery: Handle specific failures without full rollback
Release savepoints: Explicit cleanup with RELEASE SAVEPOINT

4. Locking & Concurrency

Lock Types

-- Explicit locking (PostgreSQL example)
BEGIN;
LOCK TABLE accounts IN SHARE MODE;
SELECT * FROM accounts WHERE id = 1;
COMMIT;

-- Row-level locking
SELECT * FROM orders 
WHERE customer_id = 42 
FOR UPDATE;  -- Exclusive lock

Shared vs Exclusive: Read locks vs write locks
Row-level vs Table-level: Granularity of locking
Deadlocks: Detection and resolution strategies
Lock timeouts: SET lock_timeout = '5s'

5. Database-Specific Implementations

PostgreSQL

BEGIN;
SET LOCAL statement_timeout = '2s';
-- Transaction operations
COMMIT;

-- Two-Phase Commit
PREPARE TRANSACTION 'transaction_id';
COMMIT PREPARED 'transaction_id';

SQL Server

BEGIN TRANSACTION WITH MARK 'OrderUpdate';
-- Operations
COMMIT TRANSACTION;

-- Snapshot Isolation
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
BEGIN TRAN;
-- Read-consistent operations
COMMIT;

Oracle

SET TRANSACTION READ ONLY;
-- Read-only operations
COMMIT;

-- Flashback Query
SELECT * FROM accounts AS OF TIMESTAMP 
SYSTIMESTAMP - INTERVAL '10' MINUTE;

6. Performance & Best Practices

Transaction size: Keep transactions short (milliseconds ideally)
Connection pooling: Reuse connections to reduce overhead
Batch operations: Group related statements in single transactions
Monitoring: Track long-running transactions and locks

Optimization Example

-- Bad: Many small transactions
BEGIN; INSERT INTO log VALUES (NOW(), 'event1'); COMMIT;
BEGIN; INSERT INTO log VALUES (NOW(), 'event2'); COMMIT;

-- Good: Single transaction
BEGIN;
INSERT INTO log VALUES (NOW(), 'event1');
INSERT INTO log VALUES (NOW(), 'event2');
COMMIT;

Advanced TRANSACTION Topics

1. Distributed Transactions

-- XA Transaction Protocol Example
XA START 'transaction_id';
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE other_db.accounts SET balance = balance + 100 WHERE id = 2;
XA END 'transaction_id';
XA PREPARE 'transaction_id';
XA COMMIT 'transaction_id';

Two-Phase Commit (2PC): Prepare phase followed by commit/rollback
Coordinator role: Transaction manager coordinates participants
Failure handling: Heuristic decisions for blocked transactions
Performance impact: Network latency and blocking concerns

2. MVCC Implementation Details

-- PostgreSQL visibility check
SELECT xmin, xmax, * FROM accounts WHERE id = 1;

-- Oracle flashback query
SELECT versions_starttime, versions_endtime, amount
FROM accounts VERSIONS BETWEEN TIMESTAMP 
SYSTIMESTAMP - INTERVAL '1' HOUR AND SYSTIMESTAMP
WHERE id = 1;

Version chains: How databases store multiple row versions
Transaction IDs: xmin/xmax in PostgreSQL, SCN in Oracle
Visibility rules: Determining which versions are visible to transactions
Vacuum processes: Cleaning up obsolete row versions

3. Write-Ahead Logging (WAL)

# PostgreSQL WAL configuration
wal_level = replica             # minimal, replica, or logical
archive_mode = on               # enables WAL archiving
archive_command = 'gzip %p -c > /archive/%f.gz'

# SQL Server transaction log
ALTER DATABASE mydb SET RECOVERY FULL;
BACKUP LOG mydb TO DISK = '/logs/mydb.trn';

Crash recovery: Replaying logs after system failure
Checkpoints: Periodic flushing of dirty buffers
Log shipping: Using WAL for replication
Performance tuning: WAL segment size and sync methods

4. Application Transaction Patterns

// Retry logic example (pseudocode)
int retries = 3;
while(retries-- > 0) {
  try {
    executeTransaction();
    break;
  } catch(DeadlockException e) {
    if (retries == 0) throw e;
    Thread.sleep(100 * (3 - retries));
  }
}

// Optimistic concurrency control
UPDATE products 
SET stock = stock - 1, version = version + 1
WHERE id = 123 AND version = 5;

Retry mechanisms: Handling deadlocks and transient errors
Optimistic vs Pessimistic: Version checking vs locking
Compensating transactions: Undo operations for eventual consistency
Idempotency: Designing retry-safe operations

5. Nested Transactions

-- SQL Server nested transaction example
BEGIN TRAN outer_tran;
    -- Operations...
    SAVE TRAN savepoint1;
    BEGIN TRAN inner_tran;
        -- More operations...
        IF @@ERROR <> 0 ROLLBACK TRAN savepoint1;
    COMMIT TRAN inner_tran;
COMMIT TRAN outer_tran;

-- PostgreSQL savepoint approach
BEGIN;
    -- Outer operations...
    SAVEPOINT nested;
    BEGIN;
        -- Inner operations...
        ROLLBACK TO nested;  -- Partial rollback
    COMMIT;
COMMIT;

True nesting: SQL Server's named transaction levels
Savepoint emulation: How most databases simulate nesting
Commit semantics: Outer commit behavior with nested transactions
Error handling: Partial rollback strategies

6. Transactional DDL

-- PostgreSQL transactional DDL
BEGIN;
CREATE TABLE new_orders (LIKE orders INCLUDING INDEXES);
ALTER TABLE orders ADD CONSTRAINT new_check CHECK (amount > 0);
-- If anything fails, all changes are rolled back
COMMIT;

-- SQL Server (non-transactional by default)
BEGIN TRAN;
EXEC sp_rename 'orders.amount', 'total_amount', 'COLUMN';
-- If this fails, the transaction is still active
COMMIT;

Database support: PostgreSQL vs SQL Server vs Oracle behaviors
Implicit commits: How DDL statements affect transactions
Schema locks: Preventing concurrent DDL during transactions
Migration implications: Safe schema changes in deployment scripts