SQL Transaction self-committing

Question

I am running the following SQL Update Query in a transaction so that I can examine the results before committing. However, after executing and rolling back, I find that it has already committed and my data has changed despite never issuing a commit statement.

USE WS_Live
BEGIN TRAN Test

UPDATE AF
SET AF.AdditionalFieldsLookup11 = NULL

OUTPUT inserted.*

FROM dbo.Incident AS I

INNER JOIN dbo.AdditionalFields AS AF
ON AF.Oid = I.AdditionalFields

INNER JOIN dbo.AdditionalFieldsLookup11 AS AFL11
ON AFL11.Oid = AF.AdditionalFieldsLookup11

WHERE AF.AdditionalFieldsLookup11 IS NOT NULL

My statement was formatted properly and I did not lose any important data, but I would like to know why this is happening so that I don't run into problems in the future.

score 2 · Answer 1 · edited May 23 '17 at 10:26

2

SQL Server roles out with auto-commit mode set to true by default.

Here is how to turn it off and here is a little additional detail regarding this feature:

edited May 23 '17 at 10:26

Community

1
1

answered Sep 30 '14 at 17:00

I've done other transactions that I was able to rollback. Why would some work and not others? – Jonathon Anderson Sep 30 '14 at 18:00
From your first link it seems like my `BEGIN` should override the auto-commit. `A SQL Server connection operates in autocommit mode until a BEGIN TRANSACTION statement starts an explicit transaction, or implicit transaction mode is set on. ` – Jonathon Anderson Sep 30 '14 at 18:02
Oh wait... I know what I did. Instead of putting `ROLLBACK TRAN Test` at the end of my SQL Query, I replaced `BEGIN` with `ROLLBACK`, so it effectively rolled back my previous transaction as if I had appended the command, and then proceeded to execute the entire query after the `ROLLBACK` line. – Jonathon Anderson Sep 30 '14 at 18:04

score 0 · Answer 2 · answered Sep 30 '14 at 18:09

It turns out that SQL was doing exactly what I told it to when I tried to rollback my original transaction. Instead of appending ROLLBACK to the end of my transaction, I modified the BEGIN TRAN Test to ROLLBACK TRAN Test like this:

USE WS_Live
ROLLBACK TRAN Test

UPDATE AF
SET AF.AdditionalFieldsLookup11 = NULL

OUTPUT inserted.*

FROM dbo.Incident AS I

INNER JOIN dbo.AdditionalFields AS AF
ON AF.Oid = I.AdditionalFields

INNER JOIN dbo.AdditionalFieldsLookup11 AS AFL11
ON AFL11.Oid = AF.AdditionalFieldsLookup11

WHERE AF.AdditionalFieldsLookup11 IS NOT NULL

SQL acted as if I had appended ROLLBACK TRAN Test to the end of my previous query, and then appended an additional query on the end of that.

SQL Transaction self-committing

2 Answers2