Transactions

witsqltransactionsisolationsavepointsreference

This page is the syntax. For what the isolation levels mean and how conflicts behave, see transactions and concurrency.

Beginning and ending

sql
BEGIN TRANSACTION;
    INSERT INTO Users (Name) VALUES ('alice');
    INSERT INTO Audit (Action) VALUES ('user created');
COMMIT;

The word TRANSACTION is optional, so a bare BEGIN opens one. SAVEPOINT is likewise optional in ROLLBACK TO and in RELEASE.

sql
ROLLBACK;

A statement outside a transaction commits on its own. Wrap anything that writes a large number of rows, even a single statement: inside a transaction nothing reaches the media until the commit, so the statement becomes all or nothing against a process that dies, and one commit flushes once instead of once per statement.

Savepoints

A savepoint is a place inside a transaction you can return to without losing what came before it.

sql
BEGIN TRANSACTION;

INSERT INTO Orders (CustomerId, Total) VALUES (@customerId, @total);

SAVEPOINT before_items;

INSERT INTO OrderItems (OrderId, ProductId) VALUES (@orderId, @productId);
-- something is wrong with the items
ROLLBACK TO SAVEPOINT before_items;

-- the order is still here
COMMIT;
sql
SAVEPOINT name;
ROLLBACK TO [SAVEPOINT] name;
RELEASE [SAVEPOINT] name;

RELEASE discards a savepoint you no longer need without undoing anything.

Isolation level

sql
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
BEGIN TRANSACTION;
    -- everything here sees the database as of the moment it began
COMMIT;

The five levels are READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SERIALIZABLE and SNAPSHOT. The default is READ COMMITTED, and it can be changed for a whole connection through the connection string.

Locking rows you read

sql
SELECT * FROM Accounts WHERE Id = @id FOR UPDATE;
SELECT * FROM Accounts WHERE Id = @id FOR SHARE;

The lock is held until the transaction ends. FOR UPDATE is exclusive, FOR SHARE allows other readers to hold one at the same time but no writer.

Two modifiers decide what happens when a row is already locked:

sql
SELECT * FROM Jobs WHERE Status = 'pending' LIMIT 1 FOR UPDATE NOWAIT;
SELECT * FROM Jobs WHERE Status = 'pending' LIMIT 1 FOR UPDATE SKIP LOCKED;

NOWAIT fails immediately rather than waiting. SKIP LOCKED passes over the locked rows and returns what is left, which is how several workers pull from one queue without queueing behind each other:

sql
BEGIN TRANSACTION;

SELECT Id, Payload FROM Jobs
WHERE Status = 'pending'
ORDER BY CreatedAt
LIMIT 1
FOR UPDATE SKIP LOCKED;

UPDATE Jobs SET Status = 'running' WHERE Id = @id;

COMMIT;

Raising an error

SIGNAL fails the current statement with a SQLSTATE and a message, which is how a trigger refuses a write:

sql
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Order total cannot be negative';
sql
CREATE TRIGGER TR_Orders_CheckTotal
    BEFORE INSERT ON Orders
    FOR EACH ROW
BEGIN
    SELECT CASE WHEN NEW.Total < 0
        THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Total cannot be negative'
    END;
END;

A CHECK constraint is the better tool when the rule fits in one, since it is declared once and the engine enforces it everywhere. SIGNAL is for rules that need more context than a CHECK can see.

Two things to know before designing around this

A conflict surfaces at commit, not at the write. Every level above READ COMMITTED is optimistic: nothing blocks, and a transaction whose read set moved fails at commit and has to be retried. Application code written for a database that locks will have no retry in it.

SERIALIZABLE permits write skew. Two transactions reading overlapping rows and writing disjoint ones both commit. Where a rule spans rows, take FOR UPDATE on what you read rather than relying on the level.

Statements nest at most 32 deep. A trigger that fires a statement that fires a trigger counts, and hitting the limit is a catchable error rather than a crash.

Where to go next