4

I have this transaction:

BEGIN TRAN;
Update Job set JobTitle = 'wonderfullivin' where JobId = 1271;
Update Job set LastTransactionDate = null where JobId = 1271;
COMMIT;

In which the second statement must fail because LastTransactionDate is set to be NOT NULL. When I look at the transaction log though, I see the transaction gets committed. Shouldn't it be aborted because one of the queries failed?

Irwin
  • 12,551
  • 11
  • 67
  • 97

1 Answers1

6

No.

You would need to use SET XACT_ABORT ON to get the behaviour you apparently expect or add some error handling and only commit if no errors occurred or rollback otherwise.

Errors in SQL Server may abort the statement, scope, batch or connection.

In this case the error just aborts the statement then execution continues on with the next statement.

See Error Handling in SQL 2000 for more about this.

Martin Smith
  • 438,706
  • 87
  • 741
  • 845
  • 1
    Hmm, this seems "wrong". I mean, isn't the whole point of BEGIN TRANSACTION to say that everything here has to execute fine or nothing at all? – Irwin Apr 04 '13 at 09:17
  • 1
    @Irwin - Not necessarily there are some circumstances in which you might be able to handle the error and not want to doom the entire outer transaction. `XACT_ABORT` gives you that behaviour. – Martin Smith Apr 04 '13 at 09:21
  • I don't think I was looking for the XACT_ABORT answer. I think I was looking to understand why it wasn't default. I guess I asked the question incorrectly. – Irwin Apr 04 '13 at 09:42
  • 1
    @Irwin - Why `XACT_ABORT` doesn't default to `ON` would be a dupe of [this question](http://stackoverflow.com/q/7956856/73226) then. Not sure you will find a canonical explanation for that design decision. The accepted answer seems reasonable to me. – Martin Smith Apr 04 '13 at 09:47
  • OK, cool. I think what I'm seeing here is there's a notion of a thing called a Transaction which allows for multiple SQL statements to be treated as an atomic activity, but each vendor (because I'm reading up on Transactions in Oracle, too) has their own implementations that should be properly understood. – Irwin Apr 04 '13 at 09:56
  • @irwin A failed statement won't abort a transaction in Oracle either. You're right each vendor has its own implementation though, so you should definitely take time to learn about the specifics. – Vincent Malgrat Apr 04 '13 at 11:09