Transactions

What Is a Transaction?

A transaction is a group of database operations executed as a single unit. Either all operations succeed or all of them fail.

ACID Properties

Starting a Transaction

BEGIN TRANSACTION;
-- or
START TRANSACTION;

COMMIT - Saving Changes

BEGIN TRANSACTION;

UPDATE ACCOUNT SET balance = balance - 500000 WHERE account_id = 1;
UPDATE ACCOUNT SET balance = balance + 500000 WHERE account_id = 2;

COMMIT;

ROLLBACK - Reverting Changes

BEGIN TRANSACTION;

UPDATE ACCOUNT SET balance = balance - 500000 WHERE account_id = 1;
UPDATE ACCOUNT SET balance = balance + 500000 WHERE account_id = 2;

-- An error occurred, cancel everything
ROLLBACK;

SAVEPOINT - Save Points

BEGIN TRANSACTION;

INSERT INTO ACCOUNT_LOG VALUES (1, 'Deposit', 1000000);
SAVEPOINT sp1;

INSERT INTO ACCOUNT_LOG VALUES (2, 'Withdrawal', 2000000);
ROLLBACK TO sp1;

-- Only the first INSERT is kept
COMMIT;

Example: Transferring Between Accounts

BEGIN TRANSACTION;

-- Decrease the sender's balance
UPDATE ACCOUNT
SET balance = balance - 1000000
WHERE account_id = 'A001';

-- Check that the balance is sufficient
IF (SELECT balance FROM ACCOUNT WHERE account_id = 'A001') < 0
BEGIN
    ROLLBACK;
    RAISERROR('Insufficient balance', 16, 1);
    RETURN;
END

-- Increase the receiver's balance
UPDATE ACCOUNT
SET balance = balance + 1000000
WHERE account_id = 'B002';

-- Record the transaction
INSERT INTO TRANSACTION (sender, receiver, amount)
VALUES ('A001', 'B002', 1000000);

COMMIT;

Transaction Tips

• Do not let transactions run too long (they lock tables)
• Always handle errors with ROLLBACK
• Use transactions for interdependent operations

Transactions ensure data integrity. Next, we will learn about Stored Procedures in the next chapter.