Home

Transactions
 

Transactions Fundamentals
 

Introduction

A transaction is an operation or a series of operations that must be performed as a group with the idea of accountability, which is to find out whether the operation(s) was(were) carried. If so, or if not so, what to do. Because of this involvement of accountability, there are rules that must be respected, and suggestions that should be followed to effectively implement the idea of a transaction.

The rules to follow are grouped under the acronym ACID:

  • Atomicity: If the transaction includes many operations, all of them must be carried. If at least one operation in the group fails, the whole series is dismissed
  • Consistency: The series of operations must be consistent
  • Isolation: When the operations are being performed, they must be isolated from any other operation on the same server or on the same database so it (they) would not be confused or mixed with other operations going on in the same machine or the same database
  • Durability: While the operations are going on, the computer keeps track of the evolution. If something makes the series of operations stop and if the computer restarts, it would remember where the operations stopped and resume. When/if the operations have been performed, they must be saved and judged complete so they can be used with no concern as to whether they completed the previous time

Practical LearningPractical Learning: Introducing Transactions
  1. Start Microsoft SQL Server and connect to the server
  2. To open a Query window, on the Standard toolbar, click the New Query button New Query

Beginning a Transaction

Before creating a transaction, you must define the operations that will be performed. To specify the beginning of the transaction, before the first operation, type BEGIN TRAN or BEGIN TRANSACTION with the following formula:

BEGIN { TRAN | TRANSACTION } 
    [ { transaction_name | @tran_name_variable }
      [ WITH MARK [ 'description' ] ]
    ]
[ ; ]

Start with either BEGIN TRAN or BEGIN TRANSACTION. After this, a transaction_name is optional. If you had previously declared a text-based variable (char, nchar, varchar, or nvarchar), and assigned the transaction name to it, you can omit the transaction_name and use the name of that variable instead. If you want the transaction to be described in a log file, type WITH MARK and provide a description that will be written to the file.

The code between the BEGIN TRAN or BEGIN TRANSACTION line is part of the transaction.

Ending a Transaction
 

Committing a Transaction

After defining the operations that are part of the transaction, the database engine would execute them in the sequence they are written. You must indicate where this series of transactions ends. To do this, type the COMMIT TRAN or COMMIT TRANSACTION expression:

BEGIN TRAN Name or BEGIN TRANSACTION Name
    Operations
COMMIT TRAN Name or COMMIT TRANSACTION Name

Consider the following example:

USE Exercise;
Go

CREATE TABLE Employees
(
    EmployeeNumber nchar(10),
    EmployeeName nvarchar(50),
    DateHired date,
    HourlySalary money
);
GO

INSERT INTO Employees
VALUES(N'593705', N'Frank Somah', N'20061004', 26.15),
      (N'720947', N'Paul Handsome', N'20000802', 36.05);
GO

INSERT INTO Employees(EmployeeName, EmployeeNumber, DateHired)
VALUES(N'Clarice Simms', N'971403', N'20011112');
GO

BEGIN TRANSACTION AddEmployees

INSERT INTO Employees
VALUES(N'595002', N'John Meah', N'20000212', 32.25),
      (N'928375', N'Chuck Stansil', N'20080628'),
      (N'792764', N'Orlando Perez', N'20000616', 12.95);

COMMIT TRANSACTION AddEmployees;
GO

INSERT INTO Employees(EmployeeName, EmployeeNumber,
            HourlySalary, DateHired)
VALUES(N'Gina Palau', N'247903', 18.85, N'20080612');
GO

This code asks the database engine to create a table named Employees in the Exercise database. After creating the table, it must first one, followed by two records. Then it must process a transaction that consists of creating three records. After that transaction, data entry continues with the addition of a record. For illustration purposes, we included an error in the code for the transaction. The above code would produce:

Transaction

The resulting table is:

Transaction

Notice that the code where the transaction was held did not complete and its records were not created.

Practical LearningPractical Learning: Creating Transactions
  1. Copy and paste the following code in the Query window: 
    -- =============================================
-- Database: DepartmentStore1
-- Author:   FunctionX
-- Date:     Wednesday 16 September 2009 - 15:12
-- =============================================
USE master
GO

-- Drop the database if it already exists
IF  EXISTS (
	SELECT name 
		FROM sys.databases 
		WHERE name = N'DepartmentStore1'
)
DROP DATABASE DepartmentStore1
GO

CREATE DATABASE DepartmentStore1
GO

BEGIN TRANSACTION DeptStore;
GO

USE DepartmentStore1;
GO
-- =======================================================
-- Database: DepartmentStore1
-- Table:	 StoreItems
-- Description:	This table holds an inventory of all the
--				merchandise sold in the store
-- =======================================================
IF OBJECT_ID('StoreItems', 'U') IS NOT NULL
  DROP TABLE StoreItems
GO
CREATE TABLE StoreItems
(
	ItemNumber nchar(10) not null,
	Manufacturer nvarchar(50) null,
	Category nvarchar(40),
	SubCategory nvarchar(40),
	ItemName nvarchar(50) not null,
	Size nvarchar(32),
	UnitPrice money default 0,
	CONSTRAINT PK_StoreItems Primary Key(ItemNumber)
);
GO
INSERT INTO StoreItems
VALUES(582604, N'Trina', N'Women', N'Clothing', N'Turk Printed 3/4-Sleeved Wrap Jersey Dress', N'0', 265.50),
	  (749374, N'Kenneth Cole', N'Men', N'Clothing', N'Simplicity Leather Dress Oxfords', N'8',65.85),
	  (379374, N'Shiseido', N'Women', N'Beauty', N'Zen Perfumed Shower Gel', N'3.3 Oz',35.00);
GO
INSERT INTO StoreItems(ItemNumber, Category, SubCategory,	ItemName, Size,	UnitPrice)
VALUES(248592, N'Women', N'Clothing', N'Petite Tech Stretch Seamed Pencil Skirt', N'4', 100.00);
GO
INSERT INTO StoreItems
VALUES(757947, N'Kenneth Cole', N'Men', N'Clothing', N'Simplicity Leather Dress Oxfords', N'9', 65.85);
GO
INSERT INTO StoreItems(ItemNumber, Category, SubCategory, ItemName, UnitPrice)
VALUES(294725, N'Women', N'Handbag', N'Perfect Tote', 295.00);
GO
INSERT INTO StoreItems(ItemNumber, Manufacturer, Category, SubCategory,	ItemName, UnitPrice)
VALUES(264826, N'Cole Haan', N'Women', N'Clothing', N'Saddle East/West Leather Hobo Bag', 345.50);
GO
INSERT INTO StoreItems
VALUES(684604, N'Ralph Lauren', N'Girls', N'Clothing', N'Girls 2-6X Short-Sleeved Mesh Polo Shirt', N'2T', 18.95),
	  (158824, N'Mulberribush', N'Boys', N'Clothing', N'2-7 Twill Flat-Front Pants', N'2T', 24.50);
GO
INSERT INTO StoreItems(ItemNumber, Manufacturer, Category, SubCategory, ItemName, UnitPrice)
VALUES(495007, N'Giorgio Armani', N'Men', N'Beauty', N'Acqua Di Gio After Shave Balm', 49.50),
	  (729741, N'Symphony Designs', N'Women', N'Accessories', N'Wool Pashmina-style Wrap', 16.95);
GO
INSERT INTO StoreItems(ItemNumber, Category, SubCategory,	ItemName, Size,	UnitPrice)
VALUES(927940, N'Women', N'Clothing', N'Petite New Femme Jacket', N'6', 210.00);
GO
INSERT INTO StoreItems
VALUES(790279, N'Trina', N'Women', N'Clothing', N'Turk Printed 3/4-Sleeved Wrap Jersey Dress', N'2', 265.50),
	  (957492, N'Kenneth Cole', N'Men', N'Clothing', N'Simplicity Leather Dress Oxfords', N'10', 65.85),
	  (557504, N'Trina', N'Women', N'Clothing', N'Trina Turk Printed 3/4-Sleeved Wrap Jersey Dress', N'10', 265.50),
	  (779204, N'Wes & Willy', N'Boys', N'Clothing', N'2-7 Cotton Short-Sleeved Polo Shirt', N'6 Long', 4.95),
	  (864400, N'Trina', N'Women', N'Clothing', N'Trina Turk Printed 3/4-Sleeved Wrap Jersey Dress', N'8', 265.50),
	  (925749, N'Kenneth Cole', N'Men', N'Clothing', N'Simplicity Leather Dress Oxfords', N'10.5', 65.85),
	  (628346, N'Fiona', N'Women', N'Clothing', N'Fiona High Heel Boot', N'6.50',295.00);
GO
INSERT INTO StoreItems(ItemNumber, Category, SubCategory, ItemName, Size, UnitPrice)
VALUES(979120, N'Women', N'Clothing', N'Pleated Neck Dress', N'16',180.00);
GO
INSERT INTO StoreItems(ItemNumber, Manufacturer, Category, SubCategory, ItemName, UnitPrice)
VALUES(707394, N'Kenneth Cole', N'Women', N'Wallet', N'Bar Tab Leather Tab Wallet', 19.95),
	  (972934, N'Ted Baker', N'Men', N'Ties', N'Grid Silk Tie', 89.50);
GO
INSERT INTO StoreItems
VALUES(295300, N'Little Me', N'Baby', N'Babies', N'Baby Bear Footies With Hat', N'6 Months', 9.95);
GO
INSERT INTO StoreItems(ItemNumber, Manufacturer, Category, SubCategory, ItemName, UnitPrice)
VALUES(492957, N'Invicta', N'Women', N'Watches', N'Pro Diver Two-tone Watch', 58.95);
GO
INSERT INTO StoreItems
VALUES(297035, N'Ralph Lauren', N'Girls', N'Clothing', N'Girls 2-6X Short-Sleeved Mesh Polo Shirt', N'3T',18.95),
	  (286401, N'Trina', N'Women', N'Clothing', N'Turk Printed 3/4-Sleeved Wrap Jersey Dress', N'6',265.50),
	  (927351, N'Kenneth Cole', N'Men', N'Clothing', N'Simplicity Leather Dress Oxfords', N'9.5',65.85),
	  (649004, N'Trina', N'Women', N'Clothing', N'Turk Printed 3/4-Sleeved Wrap Jersey Dress', N'12',265.50),
	  (709274, N'Little Me', N'Baby', N'Babies', N'Baby Bear Footies With Hat', N'8 Months',9.95);
GO
INSERT INTO StoreItems(ItemNumber, Category, SubCategory, ItemName, Size, UnitPrice)
VALUES(790402, N'Women', N'Clothing', N'Petite New Femme Jacket', N'2', 210.00);
GO
INSERT INTO StoreItems
VALUES(770240, N'Trina', N'Women', N'Clothing', N'Turk Printed 3/4-Sleeved Wrap Jersey Dress', N'4', 265.50),
	  (952735, N'Black Brown 1826', N'Men', N'Clothing', N'3-Button Top Coat', N'42R', 495.00),
	  (974815, N'Trina', N'Women', N'Clothing', N'Trina Turk Printed 3/4-Sleeved Wrap Jersey Dress', N'0', 265.50),
	  (184081, N'Kenneth Cole', N'Men', N'Clothing', N'Simplicity Leather Dress Oxfords', N'8', 65.85),
	  (660284, N'Shiseido', N'Women', N'Beauty', N'Zen Perfumed Shower Gel', N'3.3 Oz', 35.00);
GO
INSERT INTO StoreItems(ItemNumber, Category, SubCategory, ItemName, Size, UnitPrice)
VALUES(208405, N'Women', N'Clothing', N'Petite Tech Stretch Seamed Pencil Skirt', N'4', 100.00);
GO
INSERT INTO StoreItems
VALUES(697224, N'Kenneth Cole', N'Men', N'Clothing', N'Simplicity Leather Dress Oxfords', N'9', 65.85);
GO
INSERT INTO StoreItems(ItemNumber, Manufacturer, Category, SubCategory, ItemName, UnitPrice)
VALUES(972947, N'Cole Haan', N'Women', N'Clothing', N'Saddle East/West Leather Hobo Bag', 345.50);
GO
INSERT INTO StoreItems
VALUES(820284, N'Ralph Lauren', N'Girls', N'Clothing', N'Girls 2-6X Short-Sleeved Mesh Polo Shirt', N'2T', 18.95);
GO
INSERT INTO StoreItems(ItemNumber, Category, SubCategory, ItemName, UnitPrice)
VALUES(862040, N'Women', N'Accessories', N'Perfect Luxe Scarf', 45.00);
GO
INSERT INTO StoreItems
VALUES(297204, N'Mulberribush', N'Boys', N'Clothing', N'2-7 Twill Flat-Front Pants', N'2T', 24.50);
GO
INSERT INTO StoreItems(ItemNumber, Category, SubCategory, ItemName, Size, UnitPrice)
VALUES(602947, N'Women', N'Clothing', N'Pleated Neck Dress', N'16', 180.00);
GO
INSERT INTO StoreItems(ItemNumber, Manufacturer, Category, SubCategory, ItemName, UnitPrice)
VALUES(792002, N'Giorgio Armani', N'Men', N'Beauty', N'Acqua Di Gio After Shave Balm', 49.50),
	  (797140, N'Kenneth Cole', N'Women', N'Wallet', N'Bar Tab Leather Tab Wallet', 19.95);
GO
INSERT INTO StoreItems
VALUES(119723, N'Ralph Lauren', N'Girls', N'Clothing', N'Girls 2-6X Short-Sleeved Mesh Polo Shirt', N'2T', 18.95);
GO
INSERT INTO StoreItems(ItemNumber, Category, SubCategory, ItemName, Size, UnitPrice)
VALUES(740597, N'Women', N'Clothing', N'Petite New Femme Jacket', N'6', 210.00);
GO
INSERT INTO StoreItems
VALUES(628460, N'Mulberribush', N'Boys', N'Clothing', N'2-7 Twill Flat-Front Pants', N'3T',24.50),
	  (864402, N'Trina', N'Women', N'Clothing', N'Turk Printed 3/4-Sleeved Wrap Jersey Dress', N'8', 265.50);
GO
INSERT INTO StoreItems(ItemNumber, Manufacturer, Category, SubCategory, ItemName, UnitPrice)
VALUES(186824, N'Invicta', N'Women', N'Watches', N'Pro Diver Two-tone Watch', 58.95);
GO
INSERT INTO StoreItems
VALUES(628316, N'Kenneth Cole', N'Men', N'Clothing', N'Simplicity Leather Dress Oxfords', N'10.5', 65.85);
GO
INSERT INTO StoreItems(ItemNumber, Category, SubCategory, ItemName, Size, UnitPrice)
VALUES(794750, N'Women', N'Clothing', N'Textured Jacket', N'6', 325.00);
GO
INSERT INTO StoreItems(ItemNumber, Manufacturer, Category, SubCategory, ItemName, UnitPrice)
VALUES(826114, N'Ted Baker', N'Men', N'Ties', N'Grid Silk Tie', 89.50);
GO
INSERT INTO StoreItems(ItemNumber, Category, SubCategory, ItemName, Size, UnitPrice)
VALUES(592733, N'Women', N'Clothing', N'Petite New Femme Jacket', N'2', 210.00);
GO
INSERT INTO StoreItems
VALUES(295001, N'Little Me', N'Baby', N'Babies', N'Baby Bear Footies With Hat', N'6 Months', 9.95);
GO
INSERT INTO StoreItems
VALUES(641104, N'Trina', N'Women', N'Clothing', N'Turk Printed 3/4-Sleeved Wrap Jersey Dress', N'12',265.50);
GO
INSERT INTO StoreItems(ItemNumber, Manufacturer, Category, SubCategory, ItemName, UnitPrice)
VALUES(797040, N'Kenneth Cole', N'Women', N'Wallet', N'Bar Tab Leather Tab Wallet', 19.95);
GO
INSERT INTO StoreItems
VALUES(860402, N'Trina', N'Women', N'Clothing', N'Turk Printed 3/4-Sleeved Wrap Jersey Dress', N'8', 265.50);
GO
INSERT INTO StoreItems(ItemNumber, Manufacturer, Category, SubCategory, ItemName, UnitPrice)
VALUES(790002, N'Giorgio Armani', N'Men', N'Beauty', N'Acqua Di Gio After Shave Balm', 49.50);
GO
INSERT INTO StoreItems
VALUES(709174, N'Little Me', N'Baby', N'Babies', N'Baby Bear Footies With Hat', N'8 Months', 9.95);
GO
INSERT INTO StoreItems(ItemNumber, Category, SubCategory, ItemName, Size, UnitPrice)
VALUES(592735, N'Women', N'Clothing', N'Petite New Femme Jacket', N'2', 210.00);
GO
INSERT INTO StoreItems
VALUES(119720, N'Ralph Lauren', N'Girls', N'Clothing', N'Girls 2-6X Short-Sleeved Mesh Polo Shirt', N'2T',18.95),
	  (779240, N'Trina', N'Women', N'Clothing', N'Turk Printed 3/4-Sleeved Wrap Jersey Dress', N'4',265.50),
	  (602917, N'Wes & Willy', N'Boys', N'Clothing', N'2-7 Cotton Short-Sleeved Polo Shirt', N'6 Long',4.95),
	  (297030, N'Sweatheart Rose', N'Girls', N'Clothing', N'2-6X Two-For Polo & Pleated Plaid Dress', N'2',40.00),
	  (770241, N'Trina', N'Women', N'Clothing', N'Turk Printed 3/4-Sleeved Wrap Jersey Dress', N'4',265.50);
GO
INSERT INTO StoreItems(ItemNumber, Manufacturer, SubCategory, ItemName, UnitPrice)
VALUES(286402, N'Andiamo', N'Bedding', N'Solid 500 Thread Count Egyptian Cotton Sheet', 44.95);
GO
INSERT INTO StoreItems(ItemNumber, Manufacturer, Category, SubCategory, ItemName, UnitPrice)
VALUES(862046, N'Symphony Designs', N'Women', N'Accessories', N'Wool Pashmina-style Wrap', 16.95);
GO
INSERT INTO StoreItems
VALUES(777240, N'Trina', N'Women', N'Clothing', N'Turk Printed 3/4-Sleeved Wrap Jersey Dress', N'4', 265.50);
GO
INSERT INTO StoreItems(ItemNumber, Manufacturer, Category, SubCategory, ItemName, UnitPrice)
VALUES(797440, N'Kenneth Cole', N'Women', N'Wallet', N'Bar Tab Leather Tab Wallet', 19.95),
	  (790202, N'Giorgio Armani', N'Men', N'Beauty', N'Acqua Di Gio After Shave Balm', 49.50);
GO
INSERT INTO StoreItems
VALUES(952935, N'Black Brown 1826', N'Men', N'Clothing', N'3-Button Top Coat', N'42R', 495.00),
	  (779242, N'Trina', N'Women', N'Clothing', N'Turk Printed 3/4-Sleeved Wrap Jersey Dress', N'4', 265.50);
GO
INSERT INTO StoreItems(ItemNumber, Category, SubCategory, ItemName, Size, UnitPrice)
VALUES(829411, N'Women', N'Clothing', N'Petite Tropical Wool Perfect Pencil Skirt', N'6', 110.00);
GO
INSERT INTO StoreItems(ItemNumber, Manufacturer, Category, SubCategory, ItemName, UnitPrice)
VALUES(200095, N'Citizen', N'Men', N'Watches', N'Eco-Drive Men''s Titanium Bracelet Watch', 115.00);
GO
INSERT INTO StoreItems
VALUES(872047, N'Trina', N'Women', N'Clothing', N'Turk Printed 3/4-Sleeved Wrap Jersey Dress', N'6', 265.50),
	  (827114, N'Joseph Abbound', N'Boys', N'Clothing', N'Guys 8-20 Blue & Tan Striped Dress Shirt', N'4', 45.00);
GO
INSERT INTO StoreItems(ItemNumber, Category, SubCategory, ItemName, Size, UnitPrice)
VALUES(860613, N'Women', N'Clothing', N'Narrow V-Neck Top', N'2', 55.50);
GO
INSERT INTO StoreItems(ItemNumber, Category, SubCategory, ItemName, UnitPrice)
VALUES(790064, N'Women', N'Jewelry', N'Pearl and Ribbon Four-Strand Necklace', 65.00),
	  (227994, N'Women', N'Clothing', N'Perfect Skinny Belt', 30.00);
GO
INSERT INTO StoreItems
VALUES(797064, N'Trina', N'Women', N'Clothing', N'Turk Printed 3/4-Sleeved Wrap Jersey Dress', N'2', 265.50),
	  (909120, N'Mulberribush', N'Boys', N'Clothing', N'2-7 Twill Flat-Front Pants', N'3T', 24.50),
	  (600947, N'Wes & Willy', N'Boys', N'Clothing', N'2-7 Cotton Short-Sleeved Polo Shirt', N'6 Long',4.95);
GO
INSERT INTO StoreItems(ItemNumber, Category, SubCategory, ItemName, UnitPrice)
VALUES(297249, N'Women', N'Handbags', N'Chain Handle Tote', 225.00);
GO
INSERT INTO StoreItems(ItemNumber, Manufacturer, Category, SubCategory, ItemName, UnitPrice)
VALUES(852040, N'Symphony Designs', N'Women', N'Accessories', N'Wool Pashmina-style Wrap', 16.95);
GO
INSERT INTO StoreItems(ItemNumber, Category, SubCategory, ItemName, UnitPrice)
VALUES(995135, N'Women', N'Jewelry', N'Pearl Stretch Bracelet', 40.00);
GO
INSERT INTO StoreItems
VALUES(290030, N'Ralph Lauren', N'Girls', N'Clothing', N'Girls 2-6X Short-Sleeved Mesh Polo Shirt', N'3T',18.95),
	  (202835, N'Bonnie Jean', N'Girls', N'Clothing', N'Girls 2-6x Two-For Dress & Coat', N'4T',50.00),
	  (296030, N'Sweatheart Rose', N'Girls', N'Clothing', N'2-6X Two-For Polo & Pleated Plaid Dress', N'4',40.00);
GO

-- ===============================================================================
-- Database:	DepartmentStore1
-- Table:		CustomersOrders
-- Description:	This table holds the items that a typical has bought in the store.
--				Each record contains a receipt number, followed by an item bought 
--				by a customer. Each item bought by a customer for a shopping
--				session shares the same receipt number, the same date, and the
--				same time
-- ===============================================================================
IF OBJECT_ID('CustomersOrders', 'U') IS NOT NULL
  DROP TABLE CustomersOrders
GO
CREATE TABLE CustomersOrders
(
	CustomerOrderID int identity(1, 1) not null,
	ReceiptNumber int not null,
	PurchaseDate date not null default getdate(),
	PurchaseTime time not null,
	ItemNumber nchar(10) not null,
	ItemName nvarchar(50) not null,
	Size nvarchar(32),
	UnitPrice money default 0,
	CONSTRAINT PK_CustomersOrders Primary Key(CustomerOrderID)
);
GO

/*	The following record is created as a first and default.
	This is used as a "dummy" record and will be used as a reference.
	This record should never be deleted.
*/
INSERT INTO CustomersOrders(
	ReceiptNumber, PurchaseDate, PurchaseTime,
	ItemNumber, ItemName, Size, UnitPrice)
VALUES(100000, GetDate(), N'09:00 AM', 0, N'Unknown', N'Fits All', 0);
GO
-- ===============================================================================
-- Database:	DepartmentStore1
-- Table:		ReceiptsSummaries
-- Description:	This table holds a type of monetary summary for each receipt.
--				A typical record contains a receipt number, the date and the time
--				the purchase took place, the total value of the purchase, and the
--				mode of payment the customer used.
--				We also include two columns that can be omitted: one that 
--				represents the amount the customer tended to the cashier 
--				(unless a mode other than cash was used
-- ===============================================================================
IF OBJECT_ID('ReceiptsSummaries', 'U') IS NOT NULL
  DROP TABLE ReceiptsSummaries
GO
CREATE TABLE ReceiptsSummaries
(
	ReceiptSummaryID int identity(1, 1) not null,
	ReceiptNumber int not null,
	PurchaseDate nvarchar(50) not null,
	OrderTotal money not null,
	TypeOfCurrency nvarchar, -- This will include: Cash, Check, Store Card, Debit Card, or Credit Card
	AmountTended money not null, -- This unnecessary column is used for claiming purposes
	Change money, -- This unnecessary column is used for claiming purposes
	CONSTRAINT PK_ReceiptsSummaries Primary Key(ReceiptSummaryID)
);
GO

COMMIT TRANSACTION DeptStore;
GO
  2. Press F5 to execute
 
 
 

Rolling Back a Transaction

Consider the following code:

USE Exercise;
Go

CREATE TABLE Employees
(
    EmployeeNumber nchar(10),
    EmployeeName nvarchar(50),
    DateHired date,
    HourlySalary money
);
GO

BEGIN TRANSACTION AddEmployees

INSERT INTO Employees
VALUES(N'593705', N'Frank Somah', N'20061004', 26.15),
      (N'720947', N'Paul Handsome', N'20000802', 36.05),
      (N'595002', N'John Meah', N'20000212', 32.25);
GO
INSERT INTO Employees(EmployeeName, EmployeeNumber, DateHired)
VALUES(N'Clarice Simms', N'971403', N'20011112');
GO
INSERT INTO Employees
VALUES(N'928375', N'Chuck Stansil', N'20080628');
GO
INSERT INTO Employees
VALUES(N'792764', N'Orlando Perez', N'20000616', 12.95);
GO
INSERT INTO Employees(EmployeeName, EmployeeNumber,
            HourlySalary, DateHired)
VALUES(N'Gina Palau', N'247903', 18.85, N'20080612');
GO

COMMIT TRANSACTION AddEmployees;
GO

When executed, the Query window would produce:

Notice that there is an error in the transaction code. The created records are:

Transaction

Notice that, despite the error, the transaction was performed and the section with error was ignored.

In some cases, to apply the rules or atomicity, you may want to dismiss the whole transaction if a section in it fails. In other words, you would want either the whole transaction to be successful or nothing. To ask the database engine to either validate the whole transaction or to dismiss it, you would ask it to roll back the (whole) transaction. To support this, instead of committing, you would use the ROLLBACK TRANSACTION expression. Its formula is:

ROLLBACK { TRAN | TRANSACTION } 
     [ transaction_name | @tran_name_variable
     | savepoint_name | @savepoint_variable ] 
[ ; ]

You start with a ROLLBACK TRAN or ROLLBACK TRANSACTION. If the transaction has a name, type it or the variable that holds its name. If you plan to save this operation, use the savepoint_name or the @savepoint_variable factor.

Here is an example of indicating that the transaction should be rolled back if it is not wholly successful:

USE Exercise;
Go

CREATE TABLE Employees
(
    EmployeeNumber nchar(10),
    EmployeeName nvarchar(50),
    DateHired date,
    HourlySalary money
);
GO

BEGIN TRANSACTION AddEmployees

INSERT INTO Employees
VALUES(N'593705', N'Frank Somah', N'20061004', 26.15),
      (N'720947', N'Paul Handsome', N'20000802', 36.05),
      (N'595002', N'John Meah', N'20000212', 32.25);
GO
INSERT INTO Employees(EmployeeName, EmployeeNumber, DateHired)
VALUES(N'Clarice Simms', N'971403', N'20011112');
GO
INSERT INTO Employees
VALUES(N'928375', N'Chuck Stansil', N'20080628');
GO
INSERT INTO Employees
VALUES(N'792764', N'Orlando Perez', N'20000616', 12.95);
GO
INSERT INTO Employees(EmployeeName, EmployeeNumber,
            HourlySalary, DateHired)
VALUES(N'Gina Palau', N'247903', 18.85, N'20080612');
GO

ROLLBACK TRANSACTION AddEmployees;
GO

This code starts by creating a table named Employees. After creating the table, the database engine is asked to add some records to it. The creation of records is included in a transaction with the roll back option.

Notice that there is an error inside the transaction. When the above code is executed, the table has been created because it is outside the transaction; but the resulting table is empty:

Notice that, because of an (one) error inside the transaction, the whole transaction was dismissed.

Controlling a Transaction's Isolation Level

In a database, a record is referred to as dirty if it has changed (modified) since the last time its table (or view) was opened. When creating a transaction, you can give instructions to the database engine about how to commit, or whether to dismiss, a transaction with regards to a dirty record. To support this, you start with the following formula: 

SET TRANSACTION ISOLATION LEVEL
    { READ UNCOMMITTED
    | READ COMMITTED
    | REPEATABLE READ
    | SNAPSHOT
    | SERIALIZABLE
    }
[ ; ]

You start with the SET TRANSACTION ISOLATION LEVEL expression and follow it with a value:

  • READ UNCOMMITTED: This value asks that the database engine read the records that are dirty but were not yet committed
  • READ COMMITTED: This value indicates that the dirty records should not be read
  • REPEATABLE READ: This value indicates that the current transaction should ignore dirty records from other transactions and the other transaction don't have access to the records of this transaction
  • SNAPSHOT
  • SERIALIZABLE
 
 
   
 
Previous Copyright © 2009 FunctionX, Inc. Next