Breaking a connection to SQL Server halfway through an execution

Question

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)?

Damien_The_Unbeliever · Accepted Answer · 2018-05-04T06:40:27.443

3

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

Any preceding committed transactions will still be committed.

¹Here 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.

edited May 04 '18 at 06:40

answered May 04 '18 at 06:34

Damien_The_Unbeliever

234,701
27
340
448

Thanks Damien. Also the distinction between scripts and batches weren't so clear to me. Is it the client that breaks the script into separate batches and submits them separately then? – Mark Roworth May 05 '18 at 07:36
@MarkRoworth - yes, it is. That's why if you e.g. write code in another language and send it to the server, it doesn't recognise `GO`. That's the convention that the client tools use to know where to split the script into batches. – Damien_The_Unbeliever May 05 '18 at 16:39

BAndrei · Answer 2 · 2018-05-04T08:46:06.633

0

Only committed transactions will be executed.

Other ones will do a roll-back.

edited May 04 '18 at 08:46

answered May 04 '18 at 06:36

BAndrei

199
2
11

Can you provide a documentation link for this? – Tim Biegeleisen May 04 '18 at 06:46
http://www.sommarskog.se/error_handling/Part2.html Point 3.2 User-Defined Transactions. – BAndrei May 04 '18 at 07:14
And also, i think this question is a duplicate - https://stackoverflow.com/questions/1539564/what-happens-to-an-uncommitted-transaction-when-the-connection-is-closed?utm_medium=organic&utm_source=google_rich_qa&utm_campaign=google_rich_qa – BAndrei May 04 '18 at 07:16
`Only committed transactions will be executed on the SQL server` This statement does not sounds right. – Squirrel May 04 '18 at 08:26
Ok... Sorry for my bad English ) – BAndrei May 04 '18 at 08:45

score 0 · Answer 3 · answered May 04 '18 at 07:08

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

Breaking a connection to SQL Server halfway through an execution

3 Answers3