15. Transactions & Concurrency

The ACID Properties

Transactions are guaranteed to be reliable through four properties collectively known by the acronym ACID. Every major database system in the world is built around these four guarantees.

Atomicity means the entire transaction is treated as a single atom — indivisible. Either all statements complete successfully, or none of them take effect. The bank transfer either moves money from both accounts or from neither. There is no partial state.

Consistency means a transaction always takes the database from one valid state to another valid state. All constraints, rules, and relationships defined in the schema are satisfied both before and after the transaction. A transaction that would violate a constraint is rejected entirely.

Isolation means concurrent transactions do not interfere with each other. While Transaction A is executing, its intermediate, uncommitted changes are invisible to Transaction B. Transaction B sees only committed data — the state the database was in before Transaction A began, or the state after it completes — never the in-between.

Durability means once a transaction is committed, its changes are permanent. Even if the power fails one second after the commit, the data is preserved. SQL Server writes to a transaction log before writing to the main data files, enabling recovery of all committed transactions after any failure.

Beginning and Committing a Transaction

By default, SQL Server runs in autocommit mode, where each individual statement is automatically its own transaction — it is committed immediately upon successful execution. To group multiple statements into one transaction, you must explicitly start one.

Open a New Query window in SSMS, connected to VoidX_Academy. First create a Wallet table to use in this example:

CREATE TABLE Wallet (
  StudentID INT PRIMARY KEY,
  Balance DECIMAL(10,2) DEFAULT 0,
  FOREIGN KEY (StudentID) REFERENCES Student(StudentID)
);

INSERT INTO Wallet
VALUES (1, 1000.00), (2, 500.00);

Now write a transaction that transfers 200 from Student 1 to Student 2:

BEGIN TRANSACTION;
UPDATE Wallet SET Balance = Balance - 200 WHERE StudentID = 1;
UPDATE Wallet SET Balance = Balance + 200 WHERE StudentID = 2;
COMMIT;

BEGIN TRANSACTION opens the transaction and creates a savepoint in the transaction log. The two UPDATE statements execute but their changes are not visible to other connections yet — they exist only in an uncommitted state. COMMIT finalises both changes permanently and simultaneously. After the COMMIT, both changes become visible to every other connection.

Verify the result:

SELECT StudentID, Balance FROM Wallet;

Student 1 should show 800.00 and Student 2 should show 700.00.

ROLLBACK — Undoing a Transaction

If something goes wrong inside a transaction, use ROLLBACK to undo every statement back to the point where BEGIN TRANSACTION was called. The database is left exactly as it was before the transaction started.

Simulate an error scenario. Attempt a transfer that should fail because of insufficient funds — and use ROLLBACK to protect the data:

BEGIN TRANSACTION;
UPDATE Wallet SET Balance = Balance - 5000 WHERE StudentID = 1;
IF (SELECT Balance FROM Wallet WHERE StudentID = 1) < 0
BEGIN
  ROLLBACK;
  PRINT 'Transfer cancelled: insufficient funds. State restored.';
END
ELSE
BEGIN
  UPDATE Wallet SET Balance = Balance + 5000 WHERE StudentID = 2;
  COMMIT;
  PRINT 'Transfer complete.';
END

Student 1 only has 800. The deduction would produce a negative balance. The IF check catches this, issues a ROLLBACK, and the wallet balances return to exactly what they were before the transaction began. No harm done.

SELECT StudentID, Balance FROM Wallet;

Both balances are unchanged. The ROLLBACK restored the pre-transaction state completely.

Savepoints — Partial Rollbacks

Sometimes a transaction consists of several stages, and you want to be able to roll back to a specific point within the transaction without abandoning all previous work. SQL Server supports this through SAVEPOINT.

BEGIN TRANSACTION;
UPDATE Wallet SET Balance = Balance - 100 WHERE StudentID = 1;
SAVE TRANSACTION AfterFirstDeduction;
UPDATE Wallet SET Balance = Balance - 200 WHERE StudentID = 1;
ROLLBACK TRANSACTION AfterFirstDeduction;
COMMIT;

This rolls back only the second deduction of 200, while keeping the first deduction of 100 intact. After the COMMIT, only the 100 deduction is permanent. Savepoints are useful in long, complex transactions where a failure in a later stage should not discard successful earlier stages.

Concurrency and Locking

When two transactions run simultaneously, SQL Server uses locks to prevent them from interfering with each other. Understanding lock types helps you design systems that are both safe and performant.

A Shared Lock is acquired during a read operation. Multiple transactions can hold shared locks on the same data simultaneously — many readers do not conflict with each other.

An Exclusive Lock is acquired during a write operation (INSERT, UPDATE, DELETE). Only one transaction can hold an exclusive lock on a piece of data at a time, and no other transaction can read or write that data until the exclusive lock is released.

This locking behaviour is what makes Isolation work. A transaction updating a student's GPA holds an exclusive lock on that row until it commits or rolls back. Any other transaction trying to read that same row will wait until the lock is released — ensuring it sees only fully committed data.

How to Use the Transaction Rollback Lab

The lab presents a split-screen interface. On the left, a Live Memory Buffer visualiser shows two states: the permanent committed data on disk, and the temporary uncommitted buffer that exists only inside an open transaction. The right side is your SQL editor.

The scenario involves an operator trying to transfer 5,000 Voidcoins when they only have 1,000. A system crash is simulated mid-transaction. Your task is to write code that starts the transaction, attempts the transfer, and uses ROLLBACK to restore the system when the crash occurs.

Here is the same pattern using the student wallet scenario:

BEGIN TRANSACTION;
UPDATE Wallet SET Balance = Balance - 5000 WHERE StudentID = 1;
UPDATE Wallet SET Balance = Balance + 5000 WHERE StudentID = 2;
ROLLBACK;

Apply this to the lab. The key requirement is that ROLLBACK must appear in your code — not COMMIT. The lab visually animates through four stages when you click Execute Sequence: the transaction opens, the uncommitted changes appear in the buffer, the crash alert fires, and then the ROLLBACK executes restoring the permanent disk state to its original values. The left panel's permanent state remains untouched throughout.