Transaction Rollbacks
Learn how to roll back the changes in case of errors within transactions.
We'll cover the following...
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 ...