May 30, 2012 at 6:51 am
You know, if you took out the transaction the answer would be exactly the same? How then is this a transaction? 🙂
Still say having SET XACT_ABORT OFF as the default is broken, buggy behavior. Because it means *it is not a transaction*.
Growl.
And please don't quibble that "it acts like a transaction sometimes and sometimes it doesn't". That's not a transaction, folks.
May 30, 2012 at 6:59 am
roger.plowman (5/30/2012)
You know, if you took out the transaction the answer would be exactly the same? How then is this a transaction? 🙂Still say having SET XACT_ABORT OFF as the default is broken, buggy behavior. Because it means *it is not a transaction*.
Growl.
And please don't quibble that "it acts like a transaction sometimes and sometimes it doesn't". That's not a transaction, folks.
Well, I'll growl "it acts like a transaction at all times - at least to the extent that the isolation level you've chosen supports transactions" (which, for the default isolation level READ COMMITTED, is pretty close to not at all).
Something is a transaction if it has the ACID properties, which are nothing to do with handling errors internal to the transaction; it isn't a transaction if it doesn't have those properties. End of story.
Tom
May 30, 2012 at 7:29 am
Ron, thank you for the easy question.
roger.plowman (5/30/2012)
You know, if you took out the transaction the answer would be exactly the same? How then is this a transaction? 🙂
Still say having SET XACT_ABORT OFF as the default is broken, buggy behavior. Because it means *it is not a transaction*.
Growl.
And please don't quibble that "it acts like a transaction sometimes and sometimes it doesn't". That's not a transaction, folks.
This happens in this specific example, it doesn't mean it always happens with any transaction.
L' Eomot Inversé (5/30/2012)
Well, I'll growl "it acts like a transaction at all times - at least to the extent that the isolation level you've chosen supports transactions" (which, for the default isolation level READ COMMITTED, is pretty close to not at all).
Something is a transaction if it has the ACID properties, which are nothing to do with handling errors internal to the transaction; it isn't a transaction if it doesn't have those properties. End of story.
+1
"El" Jerry.
May 30, 2012 at 7:40 am
I am so accustomed to seeing XACT_ABORT set ON that I almost missed the fact that it was being set OFF in this case. Could explain some of the wrong answers.
May 30, 2012 at 7:41 am
I must admit I'm surprised that having SET XACT_ABORT OFF is the default. I take the point that READ COMMITTED breaks the rules of isolation anyway, but I find this one even more alarming. This from the Transactions topic in Books Online:
It is the responsibility of an enterprise database system, such as an instance of the Database Engine, to provide mechanisms ensuring the physical integrity of each transaction. The Database Engine provides:
- Locking facilities that preserve transaction isolation.
- Logging facilities that ensure transaction durability. Even if the server hardware, operating system, or the instance of the Database Engine itself fails, the instance uses the transaction logs upon restart to automatically roll back any uncompleted transactions to the point of the system failure.
- Transaction management features that enforce transaction atomicity and consistency. After a transaction has started, it must be successfully completed, or the instance of the Database Engine undoes all of the data modifications made since the transaction started.
What it should really point out is that the facilities described in the first and third points aren't turned on by default.
John
May 30, 2012 at 8:00 am
There goes my streak 🙁
May 30, 2012 at 8:17 am
roger.plowman (5/30/2012)
You know, if you took out the transaction the answer would be exactly the same? How then is this a transaction? 🙂
Replace the COMMIT with ROLLBACK - sufficient proof that this is a transaction?
Still say having SET XACT_ABORT OFF as the default is broken, buggy behavior. Because it means *it is not a transaction*.
Growl.
And please don't quibble that "it acts like a transaction sometimes and sometimes it doesn't". That's not a transaction, folks.
But I am allowed to quibble that it always acts like a transaction, right?
Enclosing stuff in a transaction means that all modifications made within that transaction are all committed or all rolled back. It does not mean that errors are now magically handled exactly the way you want to. You want to ignore errors and simply move on? Fine! That's even default behaviour for most errors. You want to rollback the transaction, log the event in a logging table and bail out? Fine too, use TRY CATCH for that. You want to handle the error, correct something and continue the transaction? Also fine, again easy to implement with TRY CATCH (but beware that some errors make the transaction doomes, meaning you cannot commit anymore). Whatever you decide - the error is handled the way you specified it. And if that involved rolling back or committing, that will apply to the entire batch.
John Mitchell-245523 (5/30/2012)
I must admit I'm surprised that having SET XACT_ABORT OFF is the default.
I am not. I prefer to be able to handle my errors myself; SET XACT_ABORT ON does not allow that; it thrusts one standard error handling upon all code.
I take the point that READ COMMITTED breaks the rules of isolation anyway
All transaction isolation levels break the rules of isolation to some extent. That's why they are called transaction isolation level - you set the level of isolation you want. READ UNCOMMITTED breaks the rules of isolation even more.
but I find this one even more alarming. This from the Transactions topic in Books Online:
It is the responsibility of an enterprise database system, such as an instance of the Database Engine, to provide mechanisms ensuring the physical integrity of each transaction. The Database Engine provides:
- Locking facilities that preserve transaction isolation.
- Logging facilities that ensure transaction durability. Even if the server hardware, operating system, or the instance of the Database Engine itself fails, the instance uses the transaction logs upon restart to automatically roll back any uncompleted transactions to the point of the system failure.
- Transaction management features that enforce transaction atomicity and consistency. After a transaction has started, it must be successfully completed, or the instance of the Database Engine undoes all of the data modifications made since the transaction started.
What it should really point out is that the facilities described in the first and third points aren't turned on by default.
I don't see why this is alarming. Why do you expect SQL Server to magically handle all your errors for you? Do you expect that in C# or VB.Net as well? Most developers routinely use try catch blocks in those languages. Why not in T-SQL?
I also fail to see how this behaviour would disable the first and third point. Can you give me a repro to demonstrate how XACT_ABORT OFF manages to get constraint violations in the database?
May 30, 2012 at 8:49 am
Hugo Kornelis (5/30/2012)
John Mitchell-245523 (5/30/2012)
I must admit I'm surprised that having SET XACT_ABORT OFF is the default.I am not. I prefer to be able to handle my errors myself; SET XACT_ABORT ON does not allow that; it thrusts one standard error handling upon all code.
But then why wrap statements in a transaction if you don't want them to succeed as a unit or fail as a unit?
I take the point that READ COMMITTED breaks the rules of isolation anyway
All transaction isolation levels break the rules of isolation to some extent. That's why they are called transaction isolation level - you set the level of isolation you want. READ UNCOMMITTED breaks the rules of isolation even more.
That's interesting. Books Online says this in the Isolation Levels in the Database Engine topic:
The ISO standard defines the following isolation levels, all of which are supported by the SQL Server Database Engine:
- Read uncommitted (the lowest level where transactions are isolated only enough to ensure that physically corrupt data is not read)
- Read committed (Database Engine default level)
- Repeatable read
-Serializable (the highest level, where transactions are completely isolated from one another)
but I find this one even more alarming. This from the Transactions topic in Books Online:
It is the responsibility of an enterprise database system, such as an instance of the Database Engine, to provide mechanisms ensuring the physical integrity of each transaction. The Database Engine provides:
- Locking facilities that preserve transaction isolation.
- Logging facilities that ensure transaction durability. Even if the server hardware, operating system, or the instance of the Database Engine itself fails, the instance uses the transaction logs upon restart to automatically roll back any uncompleted transactions to the point of the system failure.
- Transaction management features that enforce transaction atomicity and consistency. After a transaction has started, it must be successfully completed, or the instance of the Database Engine undoes all of the data modifications made since the transaction started.
What it should really point out is that the facilities described in the first and third points aren't turned on by default.
I don't see why this is alarming. Why do you expect SQL Server to magically handle all your errors for you? Do you expect that in C# or VB.Net as well? Most developers routinely use try catch blocks in those languages. Why not in T-SQL?
I also fail to see how this behaviour would disable the first and third point. Can you give me a repro to demonstrate how XACT_ABORT OFF manages to get constraint violations in the database?
I'm sorry, I didn't express myself clearly here. I didn't mean that I found the BOL excerpt alarming, but that I thought that the XACT_ABORT default was more alarming than the isolation level default. But I shall attempt to answer your questions. I don't expect SQL Server to do anything magically. TRY...CATCH blocks are fine - I use them myself. I just can't think of a reason to declare an explicit transaction and then not expect it to be rolled back if any of the operations within it fail. And I never said that XACT_ABORT OFF causes constraint violations. My point is that the default setting means that Atomicity (A transaction must be an atomic unit of work; either all of its data modifications are performed, or none of them is performed) is not honoured. Maybe it's a good thing, maybe it isn't - I'm just saying I'm surprised that OFF is the default.
The first bullet from the BOL snippet is disabled by the default isolation level of READ COMMITTED, which allows nonrepeatable reads and phantoms. The third is disabled by the default of SET XACT_ABORT OFF, which allows partial success of a set of statements within an explicit transaction, as I described above.
John
Edit - corrected typo.
Second edit - responded to the question about disabling the first and third points
May 30, 2012 at 8:59 am
thanks for the question - cheers
May 30, 2012 at 9:26 am
A good one, Ron - thanks!
May 30, 2012 at 9:49 am
Thanks for the question, good reinforcement.
----------------------------------------------------------------------------
Sacramento SQL Server users group - http://sac.sqlpass.org
Follow me on Twitter - @SQLDCH
----------------------------------------------------------------------------
May 30, 2012 at 10:19 am
John Mitchell-245523 (5/30/2012)
The first bullet from the BOL snippet is disabled by the default isolation level of READ COMMITTED, which allows nonrepeatable reads and phantoms. The third is disabled by the default of SET XACT_ABORT OFF, which allows partial success of a set of statements within an explicit transaction, as I described above.
I'm afraid old handslike me won't accept your argument. We know that the atomicity requirement (the database is never observably in a state where some but not all of the updates caused by a transaction have taken place) isn't violated by having XACT_ABORT off; the transaction in this question is just fine: it makes two updates; there is no observable state where only one of them is made because to observe such a state you would have to get inside the transaction which you can't do if you choose a serious isolation level. You can't argue that the transaction must not be allowed to continue after that error because if you are doing so you are saying that only one type of error management is acceptable so every error has to result in roll-back: Any error of any kind, however detected, has to result in rollback or it's a bug in the SQL Server system. To pretend that there's something magic about a constraint violation you have to pretend the same about arithmetic overflow, arithmetic underflow, loss of significance (when/if SQL gets proper support for the current IEE floating point standard), zero divides, and so on - anything that the system detects, in fact, and anything that is detected by SQL code or other application code within the transaction. That's still all silly, because the system can't know about an error it doesn't detect if the application doesn't tell it about the error, so it can't force a rollback. It seems pretty clear that there's no good reason to think that an error detected by the system should be any more decisive in requiring rollback than an error detected by the programme (in SQL or whatever language) unless either the programmer has specifically told the system to treat it so or the system is unable to continue after the error, whether because it can't translate the program or because the system's error management isn't good enough to allow the programme to make a choice in this instance.
Of course you can look inside transactions and violate the atomicity principle when your isolation level is neither snapshot nor serializable, but that's nothing to do with XACT_ABORT or error handling, it's to do with deliberately violating atomicity by using non-isolating isolation levels, playing with fire to gain some performance, just the sort of thing you could expect when (some decades ago) everyone building a relational database was determined to show vast throughput on benchmarks which they didn't know how to achieve if they had to provide proper isolation; ANSI could produce a standard that every RDBMS manufacturer would ignore (an utterly pointless excercise) or it could put the compromise into the standard (which is what it did). So yes, SQL does violate the transaction principles - but with its isolation levels and not with its error handling (which, with all its faults, is completely consistent with the transaction principles).
Incidentally, I know some people who, when snapshot isolation was first proposed (several decades ago) claimed it violated atomicity because it can not guarantee serializability of the commited transactions. I think that's gibberish, because proper snapshot isolation makes no uncommitted transactions updates visible outside the transaction that owns the updates, but I would like to see either an optimistic locking system that guarantees serializability (perhaps called "serializable snapshot", or something like that) or a decent proof that the cases where snapshot isolation delivers non-serializable results are detectable (ie given a workload definition you can compute whether snapshot isolation guarantees serializability or not) and that this is extremely rare (sufficiently to make "serializable snapshot" not worth implementing). But I suspect nothing will happen, the question has been out there for a long time now and very few people have looked at it (and I haven't had time since the early 90s).
Tom
May 30, 2012 at 11:17 am
Keep it up Ron - doing good with these questions.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
May 30, 2012 at 11:45 am
Hi,
If you go five questions back, you'll see a question named "Transactions 2". As I can see nothing different in this one, and the result is the same.
What is the difference?
I can only see the correct-answered percentage is improved.
Thanks anyway
IgorMi
Igor Micev,My blog: www.igormicev.com
May 30, 2012 at 12:33 pm
John Mitchell-245523 (5/30/2012)
Hugo Kornelis (5/30/2012)
I am not. I prefer to be able to handle my errors myself; SET XACT_ABORT ON does not allow that; it thrusts one standard error handling upon all code.But then why wrap statements in a transaction if you don't want them to succeed as a unit or fail as a unit?
Because I still want the ability to fail the transaction as a whole. I just don't want it to be the only option on errors.
In an explicit transaction, I can issue an explicit ROLLBACK and everything will be rolled back.
That's interesting. Books Online says this in the Isolation Levels in the Database Engine topic:
True, my "all isolation levels" was overgeneralizing. Serializable does offer full isolation (at the price of reduced concurrency; this is why you can control the isolation level).
I don't expect SQL Server to do anything magically. TRY...CATCH blocks are fine - I use them myself. I just can't think of a reason to declare an explicit transaction and then not expect it to be rolled back if any of the operations within it fail.
I already mentioned two reasons in a previous reply: logging of errors that have occurred for later analysis, or specific code to correct "expected" error conditions.
And I never said that XACT_ABORT OFF causes constraint violations. My point is that the default setting means that Atomicity (A transaction must be an atomic unit of work; either all of its data modifications are performed, or none of them is performed) is not honoured. Maybe it's a good thing, maybe it isn't - I'm just saying I'm surprised that OFF is the default.
The keywords in this statement are "all of its data modifications". I interpret this as "everything that is specified, after catering for the effect of possible errors and the specified error handling", and in that case the code snippet in the QotD conforms. But you seem to interpret this as "everything that is specified, period - any error invalidates the transaction". In my opinion, the latter is just one of several ways to handle errors, and the developer should explicitly specify if that is the required handling; your mileage (obviosuly) varies.
The first bullet from the BOL snippet is disabled by the default isolation level of READ COMMITTED, which allows nonrepeatable reads and phantoms. The third is disabled by the default of SET XACT_ABORT OFF, which allows partial success of a set of statements within an explicit transaction, as I described above.
I'd say that for the first bullet point, the default setting is a good compromise between isolation and concurrency. The third has nothing to do with this issue. The database can only guard consistency if you tell it how inconsistency looks. You do so by constraints and triggers. Other than that, the database does as instructed. So if you instruct it not to handle errors and commit the transaction regardless of them, it will so - except it will still not violate constraints, so statements that would do so will be refused.
(BTW, please don't feel I am picking on you. You are representing a notion that many people have, as I saw from previous discussions on this subject, and I am adressing everyone through you.)
Viewing 15 posts - 16 through 30 (of 39 total)
You must be logged in to reply to this topic. Login to reply