Every database rookie knows about the ACID principles.
ACID is what makes Oracle snobs look down at MySQL and scoff that it is not a real database.
I am neither a database snob nor an absolute rookie, but a certain behavior in SQL Server 2000 did baffle me. I would create transactions and they would fail in the middle without rolling back (and atomicity was just like, not there).
Rummaging through the beloved server’s documentation (RTFM, RTFM) discovered this flag and the somewhat hair-raising behavior:
If a run-time statement error (such as a constraint violation) occurs in a batch, the default behavior in SQL Server is to roll back only the statement that generated the error. You can change this behavior using the SET XACT_ABORT statement. After SET XACT_ABORT ON is executed, any run-time statement error causes an automatic rollback of the current transaction. Compile errors, such as syntax errors, are not affected by SET XACT_ABORT.
That is, you need to set your SET XACT_ABORT ON before you execute your transaction.
XC3113n7
