Querying Databases Using Transact-SQL/

...

/

Transaction Rollbacks

Transaction Rollbacks

Learn how to roll back the changes in case of errors within transactions.

We'll cover the following...

Not all errors cause a transaction to roll back and cancel all the commands it contains. For example, if a timeout occurs on the client’s side, there will be an error, but changes made prior to the timeout will be saved. Also, errors related to constraint violation do not make the transaction roll back, by default.

Example: Constraint violation

Let’s try setting NULL to a column that does not allow NULL values:

CREATE DATABASE BankingSystem;
USE BankingSystem;

CREATE TABLE Accounts
(
    Id INT PRIMARY KEY IDENTITY(1, 1),
    Balance DECIMAL(18, 2) NOT NULL
);

-- Create 4 accounts with initial balance
INSERT INTO dbo.Accounts (Balance)
VALUES (250), (1310), (760), (890);

PRINT 'Table state in the beginning:';
SELECT * FROM dbo.Accounts;

BEGIN TRANSACTION
    -- Transfer 200 from Account 2 (Id = 2) to Account 1 (Id = 1)
    -- 1. Deduct the amount from Account 2
    UPDATE dbo.Accounts
    SET Balance = Balance - 200
    WHERE Id = 2
    -- 2. Debit the amount to Account 1 (we will make this statement fail)
    UPDATE dbo.Accounts
    SET Balance = NULL -- Constraint violation here
    WHERE Id = 1
COMMIT TRANSACTION;

-- Check the status of the accounts
PRINT 'Table state in the end:';
SELECT * FROM dbo.Accounts;
Constraint violation does not roll back a transaction

As we can see, funds were deducted from ...