SQL Transactions

A Transaction is a set of SQL operations that either succeed as a group or fail as a group. A transaction is an all or nothing event -- there cannot be some operations that succeed and others that fail. Transactions are surrounded by BEGIN TRAN and COMMIT TRAN.

Example

#

ORDER
Id
OrderDate
OrderNumber
CustomerId
TotalAmount
ORDERITEM
Id
OrderId
ProductId
UnitPrice
Quantity
Problem: Create a new order. Ensure that both Order and OrderItems are saved or none at all.
BEGIN TRAN;

INSERT INTO [Order] (OrderNumber, CustomerId, TotalAmount)
VALUES (543210, 23, 183.35);

DECLARE @OrderId INT;
SET @OrderId = SCOPE_IDENTITY();

INSERT INTO OrderItem(OrderId, ProductId, UnitPrice, Quantity)
VALUES (@OrderId, 5, 21.35, 1);

INSERT INTO OrderItem(OrderId, ProductId, UnitPrice, Quantity)
VALUES (@OrderId, 20, 81.00, 2);

COMMIT TRAN;
Result:  3 Records affected

BEGIN TRAN starts the transaction and COMMIT TRAN commits the transaction, that is, it ensures that all records are written correctly to the hard drive. If any of the INSERT statements fail, than all INSERT statements are rolled back (undone).

Transactional commands

These are the fundamental transactional commands.

Statement Description
BEGIN TRAN Marks the beginning of a transaction.
COMMIT TRAN Marks the end of a transaction. All data changes are committed (written) to disk.
ROLLBACK TRAN Undo (removes) all data changes made since the beginning of the transaction.
SAVE TRAN Sets a savepoint, or marker, within a transaction. The savepoint defines a location to which a transaction can return if a failure occurs.

Note: Transactional commands only work with the following statements: INSERT, UPDATE, and DELETE. They cannot be used with DDL, such as creating or dropping tables.


Autocommit

#

Without explicit transactions, each INSERT, UPDATE, and DELETE statement is automatically committed (written to disk) following a successful completion. In case of an error, all column changes are rolled back (erased). This is referred to as autocommit mode.

There are situations where you need explicit transactions, such as banking solutions, with debit and credit transactions, or ecommerce solutions with shopping cart and order transactions. If not, there is a risk of partial transactions, incomplete orders, and financial loss.


More Examples

Explicit Transactions

SUPPLIER
Id
CompanyName
ContactName
City
Country
Phone
Fax
PRODUCT
Id
ProductName
SupplierId
UnitPrice
Package
IsDiscontinued
Problem: Add a new supplier and their products to the database.
BEGIN TRAN;

INSERT INTO [Supplier] (CompanyName, ContactName, 
                        City, Country, Phone)
VALUES ('Amsterdam Broodjeshuis', 'Jan van de Berge', 
        'Amsterdam', 'Netherlands','(31) 1 2612 7769');

DECLARE @SupplierId INT;
SET @SupplierId = SCOPE_IDENTITY();

INSERT INTO Product(SupplierId, ProductName, UnitPrice, Package)
VALUES (@SupplierId, 'Krentebollen', 4.35, '6 in a bag');

INSERT INTO Product(SupplierId, ProductName, UnitPrice, Package)
VALUES (@SupplierId, 'Volkoren brood', 6.35, '1 loaf');

INSERT INTO Product(SupplierId, ProductName, UnitPrice, Package)
VALUES (@SupplierId, 'Roggebrood', 2.99, '20 slices per pkgs');

COMMIT TRAN;

Again, either all INSERT statements are successful or they all fail.
For example, it is impossible that the first two INSERT succeed and the rest not.

Result:  4 records affected

You may also like



Guides


vsn 3.1