Transactions in SQL

The Transactional Integrity Matrix In a production database environment, data modifications rarely happen in complete isolation. A single business action often requires running multiple distinct SQL statements in a specific sequence. For example, moving a service workload from one server cluster to another requires two steps: an UPDATE to deduct capacity from the source node, followed by a second UPDATE to add capacity to the destination node. If the database server crashes or loses network connectivity mid-way through that sequence—after the first step but before the second—your data falls into an inconsistent, broken state. A Transaction solves this problem by grouping multiple SQL statements together into a single, cohesive unit of work. It guarantees that either all the grouped statements execute successfully together, or none of them do, leaving the database completely clean and uncorrupted. 1. The Pillars of Data Integrity: ACID Properties To guarantee absolute reliability across any hardware configuration or runtime error, a relational database engine forces all transactions to strictly obey the four ACID properties: A - Atomicity (All-or-Nothing): Guarantees that a transaction evaluates as a single, indivisible unit of work. If any single SQL statement inside the transaction block fails for any reason, the entire transaction is aborted instantly, and all modifications made up to that point are completely undone. C - Consistency (State Preservation): Enforces that a transaction can only transition the database from one valid structural state to another. All written data must pass every defined schema validation rule, data type check, and table constraint (such as foreign keys or check constraints) perfectly before the changes can be locked in. I - Isolation (Independent Concurrency): Ensures that multiple transactions executing concurrently on the server cannot see or interfere with each other's uncommitted data modifications. The database makes it appear as though each transaction is running entirely on its own. D - Durability (Permanent Record): Guarantees that once a transaction successfully completes and commits, its data changes are safely flushed down to non-volatile physical disk storage. The records will not be lost, even if a total system power failure occurs immediately after the commit confirmation. 2. Transaction Control Language (TCL) Commands You manage the lifecycle of a transaction using explicit Transaction Control Language (TCL) boundaries. THE TRANSACTION LIFECYCLE PIPELINE BEGIN TRANSACTION; │ ▼ [SQL Write Op 1] ───► Passes Validation ───┐ │ ▼ ▼ SAVEPOINT alpha; [SQL Write Op 2] ───► Fails / Crashes ──────┼──────────────┐ │ │ │ ▼ ▼ ▼ Decision Endpoint ROLLBACK TO ROLLBACK; │ SAVEPOINT alpha; (Aborts Entire ▼ (Undo Op 2 Only) Transaction) COMMIT; (Persists to Disk Storage) A. Initializing and Locking Changes: BEGIN & COMMIT BEGIN TRANSACTION; (or START TRANSACTION; ): Tells the database engine to open a new isolated transaction sandbox thread for your session. Any subsequent data modifications ( INSERT , UPDATE , DELETE ) are kept temporary and are hidden from all other connected database users. COMMIT; : Takes all the temporary changes made inside your current transaction sandbox, writes them permanently to disk storage, and makes them visible to the rest of the world. SQL -- Initializing a secure, atomic capacity migration sequence BEGIN TRANSACTION; UPDATE cluster_nodes SET traffic_weight = traffic_weight - 100 WHERE node_id = 10; UPDATE cluster_nodes SET traffic_weight = traffic_weight + 100 WHERE node_id = 25; -- Lock both updates permanently to disk safely COMMIT; B. Undoing Operations: ROLLBACK & SAVEPOINT ROLLBACK; : Instantly aborts the active transaction. The engine discards all uncommitted modifications made during the session and restores the affected rows back to the exact data state they were in before the transaction began. SAVEPOINT name; : Sets an intermediate marker inside your transaction. This allows you to break a large transaction into smaller steps, giving you the ability to run a partial rollback to a specific checkpoint without needing to discard the entire transaction. SQL BEGIN TRANSACTION; -- Step 1: Modify primary node allocation UPDATE cluster_nodes SET system_weight = 400 WHERE node_id = 1; -- Establish a rollback milestone marker SAVEPOINT configuration_milestone; -- Step 2: Attempt a volatile secondary network connection insert INSERT INTO node_telemetry (target_node_id, latency_score) VALUES (999, 12); -- Assume this insert fails or throws an application error... -- Undo only the failed telemetry step, keeping the Step 1 update safe ROLLBACK TO SAVEPOINT configuration_milestone; -- Commit the valid updates from Step 1 permanently to disk COMMIT; 3. Concurrency Anomalies & Isolation Levels When hundreds of application threads read and write to the exact same database tables simultaneously, their operations can overlap in ways that create data inconsistencies. These overlaps cause four primary Concurrency Anomalies : Dirty Read: Transaction A modifies a row's value but hasn't committed yet. Transaction B reads that row and sees the modified value. If Transaction A then runs a ROLLBACK , the data Transaction B just read vanishes entirely, meaning Transaction B based its logic on fake, uncommitted data. Non-Repeatable Read: Transaction A reads a row. Transaction B immediately updates that same row and runs a COMMIT . If Transaction A reads the exact same row a second time within its original transaction window, it sees the new updated value, making the data inconsistent across the same transaction. Phantom Read: Transaction A executes a range query (e.g., counting all nodes with a weight over 500). Transaction B inserts a brand-new row that matches that criteria and runs a COMMIT . If Transaction A runs the exact same range query again, new "phantom" rows appear that weren't there before. Serialization Anomaly: A complex race condition where the concurrent results of a group of transactions differ from any possible sequence where those same transactions were run one after the other. The Four Standard Isolation Levels To protect your application from these anomalies, the SQL standard defines four distinct Transaction Isolation Levels . You can adjust these levels based on your performance needs, allowing you to choose the exact balance between strict data consistency and high concurrent performance. SQL -- Tuning an active connection's isolation boundary level SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; Isolation Level Dirty Reads Non-Repeatable Reads Phantom Reads Engine Concurrency Performance READ UNCOMMITTED Permitted Permitted Permitted Highest. Lowest locking overhead; highly prone to data errors. READ COMMITTED Blocked Permitted Permitted Balanced. This is the default isolation level for PostgreSQL and SQL Server. REPEATABLE READ Blocked Blocked Permitted Good. Shared read locks are held until the entire transaction finishes. SERIALIZABLE Blocked Blocked Blocked Lowest. Forces concurrent transactions to execute sequentially as if locked in a single-file line. Transaction Control Reference Matrix TCL Keyword Command Operational Scope Physical Memory / Disk Impact Critical Production Guardrail BEGIN Initiates an isolated transaction session tracking sandbox. Allocates local memory structures to track uncommitted changes. Always ensure your application code closes every open transaction block with a COMMIT or ROLLBACK . Leaving transactions open can lock rows permanently, freezing other queries. COMMIT Finalizes the transaction, making all modifications permanent. Flushes cached row changes out of memory and writes them straight to physical storage logs. A commit is permanent and irreversible . Once executed, you cannot undo the changes using a rollback. ROLLBACK Aborts the active transaction, discarding all uncommitted updates. Releases the session's temporary memory space and drops all pending changes instantly. Use ROLLBACK inside your application's error-handling catch blocks to clean up partial data writes if an API call fails mid-way. SAVEPOINT Creates an intermediate checkpoint marker inside a transaction. Sets a named pointer within the session's transaction log stack. Savepoints are automatically destroyed the moment you execute a top-level COMMIT or ROLLBACK command.