Why a single update, delete and insert operations are atomic, even without using Begin Transaction ?
Posted on 2004-09-22
I have a doubt about the internal behavior of how SQL Server handle errors in update/delete/insert operations.
If say, I have an "Update" statement, that will update 10000 records and in the middle of the update, some error happens, like a check constraint violation, I review the table and non of the records where updated. That's very nice, right ? Even without adding a Begin Transaction / Update|Delete|Insert ... / Commit transaction, the operation is always atomic.
So the question is how does it do it ?
I have some guess. First, could it be starting an internal/hidden transaction ? If so, is that transaction the same kind of transaction we use in our code or do they have their own special internal transaction to handle this ?
Or, if this is not a transaction at all, then what is it ?
Thanks in advance,