I am trying to learn things about implicit and explicit transactions. I have a table with three columns and a check constraint on age (must be <10)
Now I have the following code where I expected to be rolled back because I have XACT_ABORT ON. But the first statement is imported to the table. How come?
SET IMPLICIT_TRANSACTIONS OFF;
SET XACT_ABORT ON;
INSERT INTO tblbegintran (firstname, lastname, age)
VALUES ('gijs', 'adsafads', 6)
INSERT INTO tblbegintran (firstname, lastname, age)
VALUES ('gijss', 'dafds', 17)
COMMIT TRAN;
Advertisement
Answer
Firstly, let’s start at SET IMPLICIT_TRANSACTIONS. From SET IMPLICIT_TRANSACTIONS (Transact-SQL):
When OFF, each of the preceding T-SQL statements is bounded by an unseen BEGIN TRANSACTION and an unseen COMMIT TRANSACTION statement. When OFF, we say the transaction mode is autocommit. If your T-SQL code visibly issues a BEGIN TRANSACTION, we say the transaction mode is explicit.
As you don’t have an explicit BEGIN TRANSACTION for the transaction above you are in autocommit; I have bolded the relevant sentences. From Auto-Commit Mode:
In auto-commit mode, every database operation is a transaction that is committed when performed. This mode is suitable for many real-world transactions that consist of a single SQL statement. It is unnecessary to delimit or specify completion of these transactions. In databases without transaction support, auto-commit mode is the only supported mode. In such databases, statements are committed when they are executed and there is no way to roll them back; they are therefore always in auto-commit mode.
If the underlying DBMS does not support auto-commit mode transactions, the driver can emulate them by manually committing each SQL statement as it is executed.
If a batch of SQL statements is executed in auto-commit mode, it is data source-specific when the statements in the batch are committed. They can be committed as they are executed or as a whole after the entire batch has been executed. Some data sources may support both of these behaviors and may provide a way of selecting one or the others. In particular, if an error occurs in the middle of the batch, it is data source-specific whether the already-executed statements are committed or rolled back. Thus, interoperable applications that use batches and require them to be committed or rolled back as a whole should execute batches only in manual-commit mode.
And finally, from SET XACT_ABORT (Transact-SQL):
When SET XACT_ABORT is ON, if a Transact-SQL statement raises a run-time error, the entire transaction is terminated and rolled back.
So here, each of your statements are being implicitly committed after they are completed, as you are in autocommit mode and so each is in it’s own transaction. Thus your first statement that meets the requirements of the CHECK CONSTRAINT is committed, and then the second statement fails, and that transaction is rolled back. This means that XACT_ABORT is only rolling back the transaction that failed in your attempt; where age has a value of 17.
To get the behaviour you want, you would either need to use SET IMPLICIT_TRANSACTIONS ON or wrap the whole thing in an explicit transaction. Example:
CREATE TABLE dbo.tblbegintran (age int CHECK (age < 10)); GO SET IMPLICIT_TRANSACTIONS OFF; SET XACT_ABORT ON; INSERT INTO tblbegintran (age) VALUES (6); INSERT INTO tblbegintran (age) VALUES (17); COMMIT TRAN; GO SET IMPLICIT_TRANSACTIONS OFF; BEGIN TRANSACTION; INSERT INTO tblbegintran (age) VALUES (7); INSERT INTO tblbegintran (age) VALUES (17); COMMIT TRANSACTION; GO SET IMPLICIT_TRANSACTIONS ON; INSERT INTO tblbegintran (age) VALUES (8); INSERT INTO tblbegintran (age) VALUES (17); COMMIT TRANSACTION; GO SELECT * --Only 6 appears FROM dbo.tblbegintran; GO DROP TABLE dbo.tblbegintran;