SQL - Transactions

Mastering SQL Transactions: Ensuring Data Integrity

A transaction is a sequence of operations performed as a single, logical unit of work. The classic example is a bank transfer: you debit money from one account and credit it to another. Both actions must succeed together. If either fails, the entire operation must be undone. This all-or-nothing principle is the core of transactions.

The ACID Properties of Transactions

To guarantee reliability, transactions adhere to four properties, known by the acronym ACID:

• Atomicity: Ensures that all operations within the transaction are completed successfully as a single, indivisible unit. If any part of the transaction fails, the entire transaction is rolled back, and the database is left unchanged.
• Consistency: Guarantees that a transaction brings the database from one valid state to another. Any data written to the database must be valid according to all defined rules, including constraints, cascades, and triggers.
• Isolation: Ensures that concurrent transactions produce the same result as if they were executed sequentially. This prevents issues like one transaction reading incomplete data from another.
• Durability: Ensures that once a transaction has been committed, it will remain so, even in the event of a power loss, crash, or error. The changes are permanently saved.

Transactional Control Commands

These commands are used with DML (Data Manipulation Language) statements like INSERT, UPDATE, and DELETE to manage transactions. DDL (Data Definition Language) commands like CREATE TABLE are often auto-committed.

• BEGIN TRANSACTION (or START TRANSACTION): Marks the beginning of a transaction.
• COMMIT: Saves all the changes made in the transaction to the database, making them permanent.
• ROLLBACK: Discards all changes made in the transaction, restoring the database to the state it was in at the start of the transaction.
• SAVEPOINT: Sets a named marker within a transaction to which you can later roll back.

Example: COMMIT and ROLLBACK

Let’s use a products table for an e-commerce site. When a user places an order, we need to decrease the stock.

-- First, let's create and populate our table.
CREATE TABLE products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(100) NOT NULL,
    stock_quantity INT NOT NULL CHECK (stock_quantity >= 0)
);

INSERT INTO products (product_id, product_name, stock_quantity) VALUES
(101, 'Laptop', 50),
(102, 'Mouse', 200);

A Successful Transaction (COMMIT)

A customer buys 2 laptops. This is a successful operation.

START TRANSACTION;

-- Decrease the stock for the product
UPDATE products
SET stock_quantity = stock_quantity - 2
WHERE product_id = 101;

-- The change is now permanent
COMMIT;

After this, querying the table shows the stock_quantity for ‘Laptop’ is now 48.

An Unsuccessful Transaction (ROLLBACK)

A customer tries to buy 250 mice, but we only have 200. Our CHECK constraint will cause an error, and we should roll back.

START TRANSACTION;

-- This UPDATE will fail because stock_quantity would become -50, violating the CHECK constraint.
-- In a real application, your code would catch this error.
UPDATE products
SET stock_quantity = stock_quantity - 250
WHERE product_id = 102;

-- Since an error occurred, we discard the changes.
ROLLBACK;

After the ROLLBACK, the stock_quantity for ‘Mouse’ remains at 200. No change was made.

Using SAVEPOINT

A SAVEPOINT allows for partial rollbacks within a larger transaction. This is useful for complex multi-step operations.

START TRANSACTION;

-- Step 1: Update laptop stock
UPDATE products SET stock_quantity = stock_quantity - 1 WHERE product_id = 101;

-- Create a savepoint before the next step
SAVEPOINT before_mouse_update;

-- Step 2: Update mouse stock. Let's say this was a mistake.
UPDATE products SET stock_quantity = stock_quantity - 10 WHERE product_id = 102;

-- We realize the mouse update was wrong, so we roll back to the savepoint.
ROLLBACK TO SAVEPOINT before_mouse_update;

-- Finally, we commit the valid part of the transaction (the laptop update).
COMMIT;

In the final state, the laptop stock is 49, but the mouse stock is back to its original value before the SAVEPOINT.

Common Pitfalls & Best Practices

• Keep Transactions Short: Long-running transactions can lock database resources, blocking other users and degrading performance. Do the work and COMMIT or ROLLBACK as quickly as possible.
• Don’t Wait for User Input: Never start a transaction and then wait for a user to click a button. The transaction should be contained within a single, fast server-side operation.
• Implicit vs. Explicit Transactions: Be aware of your database’s autocommit settings. It’s best practice to use explicit START TRANSACTION and COMMIT/ROLLBACK to make your logic clear and predictable.
• Error Handling is Key: Your application code must be able to catch database errors and issue a ROLLBACK command appropriately.