3

I am working on pymssql, a python MSSQL driver. I have encountered an interesting situation that I can't seem to find documentation for. It seems that when a CREATE TABLE statement fails, the transaction it was run in is implicitly rolled back:

-- shows 0
select @@TRANCOUNT
BEGIN TRAN

-- will cause an error
INSERT INTO foobar values ('baz')

-- shows 1 as expected
select @@TRANCOUNT

-- will cause an error
CREATE TABLE badschema.t1 (
    test1 CHAR(5) NOT NULL
)

-- shows 0, this is not expected
select @@TRANCOUNT

I would like to understand why this is happening and know if there are docs that describe the situation. I am going to code around this behavior in the driver, but I want to make sure that I do so for any other error types that implicitly rollback a transaction.

NOTE

I am not concerned here with typical transactional behavior. I specifically want to know why an implicit rollback is given in the case of the failed CREATE statement but not with the INSERT statement.

Randy Syring
  • 1,971
  • 1
  • 15
  • 19

3 Answers3

3

Here is the definitive guide to error handling in Sql Server:
http://www.sommarskog.se/error-handling-I.html

It's long, but in a good way, and it was written for Sql Server 2000 but most of it is still accurate. The part you're looking for is here:
http://www.sommarskog.se/error-handling-I.html#whathappens

In your case, the article says that Sql Server is performing a Batch Abortion, and that it will take this measure in the following situations:

  • Most conversion errors, for instance conversion of non-numeric string to a numeric value.
  • Superfluous parameter to a parameterless stored procedure.
  • Exceeding the maximum nesting-level of stored procedures, triggers and functions.
  • Being selected as a deadlock victim.
  • Mismatch in number of columns in INSERT-EXEC.
  • Running out of space for data file or transaction log.

There's a bit more to it than this, so make sure to read the entire section.

Joel Coehoorn
  • 399,467
  • 113
  • 570
  • 794
  • _Why_ it peforms a batch abortion in those circumstances is something only someone at Microsoft can answer ;) – Joel Coehoorn May 04 '11 at 01:30
  • Thanks, there is a lot of info there and the batch-abort concept does seem to match what I am seeing. Interestingly, though, my CREATE statement failure doesn't seem to fit any of those conditions. – Randy Syring May 04 '11 at 01:57
2

It is often, but not always, the point of a transaction to rollback the entire thing if any part of it fails: http://www.firstsql.com/tutor5.htm

One of the most common reasons to use transactions is when you need the action to be atomic:

An atomic operation in computer science refers to a set of operations that can be combined so that they appear to the rest of the system to be a single operation with only two possible outcomes: success or failure. en.wikipedia.org/wiki/Atomic_(computer_science)

It's probably not documented, because, if I understand your example correctly, it is assumed you intended that functionality by beginning a transaction with BEGIN TRAN

jefflunt
  • 33,527
  • 7
  • 88
  • 126
  • There is no assumption that using a transaction automatically means you want to rollback the entire thing. Also, did you even run the SQL? Did you notice the difference between how the two errors were handled? The INSERT error does not rollback but the CREATE error does. Its that inconsistency that I am trying to figure out. If they both rolled back, it wouldn't be such a big deal. – Randy Syring May 04 '11 at 00:34
  • 1
    ?? First paragraph of the "Transcations" section: http://www.codeproject.com/KB/database/sqlservertransactions.aspx – jefflunt May 04 '11 at 00:40
  • Thats just a typical usage. There are other use cases. For example, it would be perfectly acceptable to start a transaction in a web application, make an INSERT, make a second INSERT, and if the second INSERT fails, adjust something, and attempt another INSERT. In this case a transaction is used, but a ROLLBACK is not automatically issued just because an error was encountered. Besides, the point of my question was not directed towards typical transaction behavior. It is specifically questioning why one case results in an implicit ROLLBACK and the other does not. – Randy Syring May 04 '11 at 00:54
  • Ah, I see. Allow me to rephrase my answer then. That would imply error-handling code, yes? Maybe doesn't apply to the sample code in this question, but definitely I see your point. – jefflunt May 04 '11 at 01:25
2

If you run as one batch (which I did first time), the transaction stays open because the INSERT aborts the batch and CREATE TABLE is not run. Only if you run line-by-line does the transaction get rolled back

You can also generate an implicit rollback for the INSERT by setting SET XACT_ABORT ON.

My guess (just had a light bulb moment as I typed the sentence above) is that CREATE TABLE uses SET XACT_ABORT ON internalls = implicit rollback in practice

Some more stuff from me on SO about SET XACT_ABORT (we use it in all our code because it releases locks and rolls back TXNs on client CommandTimeout)

Community
  • 1
  • 1
gbn
  • 422,506
  • 82
  • 585
  • 676