SQL Interview: 80 Automatic roll back of transactions

This is a post in the SQL Interview series. These aren’t trick or gotcha questions, they’re just questions designed to scope out a candidate’s knowledge around SQL Server and Azure SQL Database.

Section: Development Level: Medium

Question:

You are reviewing T-SQL transactional code that does not appear to be working as expected. The following code is being executed:

BEGIN TRAN;

UPDATE Table1 SET Column1 = 12 WHERE Column2 = 14;
UPDATE Table2 SET Column3 = 15 WHERE Column4 = 99;

COMMIT TRAN;

The second update (for Table2) is failing with a foreign key violation, but the update to Table1 is not being rolled back.

What would cause that?

Answer:

The outcome of the transaction depends upon the XACT_ABORT setting at the time the transaction was started.

If XACT_ABORT was ON, then the roll back of all statements in the transaction would occur.

If XACT_ABORT was OFF, then an error when updating Table2 in the code above, would not roll back the update to Table1.

2025-09-21