CODEWITHSUNDEEP
sql-servertransactionsconnection
Mark Roworth
Mark Roworth

Reputation: 566

Breaking a connection to SQL Server halfway through an execution

If I execute a script against SQL, which has multiple transactions in it, but break the connection half way through, what does SQL do? Does it run the script to completion, or just roll-back the transaction it was currently in (or finish it)?

Upvotes: 1

Views: 477

Answers (3)

BAndrei
BAndrei

Reputation: 249

Only committed transactions will be executed.

Other ones will do a roll-back.

Upvotes: 0

Alfin E. R.
Alfin E. R.

Reputation: 928

Well, depends, if there are multiple transactions in the script SQL, do you put commit into each transaction ? for example, if your script like this

BEGIN TRAN --Transaction 1
    INSERT TableA VALUES(value1,value2)
COMMIT TRAN

BEGIN TRAN --Transaction 2
    INSERT TableB VALUES(value1,value2)
COMMIT TRAN

if your sql server disconnected, half-way of the execution of the whole script, but while on the way of execution of the script and the sql server has completed transaction 1, and that's committed, then transaction 1 is completed, and you can see the result on tableA, but when the sql server running transaction 2 imidiately and few moment later the transaction 2 yet incomplete and the connection gets down, transaction 2 will be rollbacked, so you cant see the result on TableB

Upvotes: 0

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239714

When SQL Server detects that a connection is broken, it should rollback any current transaction and abort the current batch1.

Any preceding committed transactions will still be committed.

1Here I'm trying to draw the distinction between scripts and batches. Many client tools support scripts containing multiple batches (delimited by GOs) and its the batches that get submitted to SQL Server, sequentially.

Upvotes: 3

Related Questions