Database Reference
In-Depth Information
--All ProductIDs are added into t1 as a logical unit of work
INSERT INTO dbo.ProductTest
SELECT p.ProductID
FROM Production.Product AS p;
GO
SELECT *
FROM dbo.ProductTest; --Returns 0 rows
SQL Server treats the preceding
INSERT
statement as a logical unit of work. The
CHECK
constraint on column
ProductID
of the
dbo.ProductTest
table allows only the value of
1
. Although the
ProductID
column in the
Production.Product
table starts with the value of
1
, it also contains other values. For this reason, the
INSERT
statement won't add any records at all to the
dbo.ProductTest
table, and an error is raised because of the
CHECK
constraint. This atomicity is automatically ensured by SQL Server.
So far, so good. But in the case of a bigger logical unit of work, you should be aware of an interesting behavior of
SQL Server. Imagine that the previous insert task consists of multiple
INSERT
statements. These can be combined to
form a bigger logical unit of work, as follows:
BEGIN TRAN
--Start: Logical unit of work
--First:
INSERT INTO dbo.ProductTest
SELECT p.ProductID
FROM Production.Product AS p;
--Second:
INSERT INTO dbo.ProductTest
VALUES (1);
COMMIT --End: Logical unit of work
GO
With the
dbo.ProductTest
table already created in the preceding script, the
BEGIN TRAN
and
COMMIT
pair of
statements defines a logical unit of work, suggesting that all the statements within the transaction should be atomic
in nature. However, the default behavior of SQL Server doesn't ensure that the failure of one of the statements within
a user-defined transaction scope will undo the effect of the prior statement(s). In the preceding transaction, the first
INSERT
statement will fail as explained earlier, whereas the second
INSERT
is perfectly fine. The default behavior of
SQL Server allows the second
INSERT
statement to execute, even though the first
INSERT
statement fails. A
SELECT
statement, as shown in the following code, will return the row inserted by the second
INSERT
statement:
SELECT *
FROM dbo.ProductTest; --Returns a row with t1.c1 = 1
The atomicity of a user-defined transaction can be ensured in the following two ways:
•
SET XACT_ABORT ON
Explicit rollback
•
Let's look at these quickly.
