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.

Improve this question

3 Answers 3

Reset to default
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.

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Why it peforms a batch abortion in those circumstances is something only someone at Microsoft can answer ;)
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.
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

Improve this answer

4 Comments

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.
?? First paragraph of the "Transcations" section: codeproject.com/KB/database/sqlservertransactions.aspx
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.
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.
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)

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.