SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by bitbucket / Transactions 3 / Latest Posts

RE: Transactions 3

Neha05 — Thu, 09 Aug 2012 08:50:16 GMT

Good back-to-basics question!

RE: Transactions 3

Britt Cluff — Mon, 04 Jun 2012 19:50:03 GMT

Seems like dejavu to me.

RE: Transactions 3

Paul White — Thu, 31 May 2012 04:41:22 GMT

[quote][b]Hugo Kornelis (5/31/2012)[/b][hr]And here is yet another variation, (using an option that I would not mind being removed from the product)[/quote]Ah, IGNORE_DUP_KEY, a rich vein for QotD questions :-)[code="sql"]CREATE TABLE #T( col1 integer NOT NULL);INSERT #T (col1) VALUES (1), (1), (2), (3);ALTER TABLE #TADD PRIMARY KEY (col1)WITH (IGNORE_DUP_KEY = ON);[/code]

RE: Transactions 3

Paul White — Thu, 31 May 2012 04:38:04 GMT

[quote][b]John Mitchell-245523 (5/31/2012)[/b][hr]Wow! Took a while to get my head round that, but when I did, it made sense. An INSERT statement has to succeed or fail as a whole, even when it attempts to insert more than one row. But I imagine the point you're making is that it's a potential banana skin for developers to beware of.[/quote]Yep, making the point that I understand why these things seem confusing and/or counter-intuitive. I have heard people say that the reason a statement has to succeed or fail as a whole is that every statement runs inside an implicit transaction...:-D

RE: Transactions 3

Hugo Kornelis — Thu, 31 May 2012 04:37:33 GMT

[quote][b]SQL Kiwi (5/31/2012)[/b][hr]I understand this behaviour, really I do, but it will never be fully intuitive; especially since SQL Server has such bizarre rules concerning which errors do what (terminate the statement, abort the batch, abort the transaction, abort the scope, doom the transaction...blah blah blah).To pursue the atomicity thing a bit more...[/quote]And here is yet another variation, (using an option that I would not mind being removed from the product)[code="sql"]DECLARE @T AS TABLE( col1 integer PRIMARY KEY WITH (IGNORE_DUP_KEY = ON));BEGIN TRANSACTION;INSERT @T (col1) VALUES (1), (3);INSERT @T (col1) VALUES (1), (2), (4);COMMIT TRANSACTION;SELECT * FROM @T AS t;DELETE @T;BEGIN TRANSACTION;INSERT @T (col1) VALUES (1);INSERT @T (col1) VALUES (3);INSERT @T (col1) VALUES (1);INSERT @T (col1) VALUES (2);INSERT @T (col1) VALUES (4);COMMIT TRANSACTION;SELECT * FROM @T AS t;[/code]

RE: Transactions 3

John Mitchell-245523 — Thu, 31 May 2012 04:30:03 GMT

Wow! Took a while to get my head round that, but when I did, it made sense. An INSERT statement has to succeed or fail as a whole, even when it attempts to insert more than one row. But I imagine the point you're making is that it's a potential banana skin for developers to beware of.John

RE: Transactions 3

Paul White — Thu, 31 May 2012 04:12:41 GMT

I understand this behaviour, really I do, but it will never be fully intuitive; especially since SQL Server has such bizarre rules concerning which errors do what (terminate the statement, abort the batch, abort the transaction, abort the scope, doom the transaction...blah blah blah).To pursue the atomicity thing a bit more...[code="sql"]DECLARE @T AS TABLE( col1 integer PRIMARY KEY)BEGIN TRANSACTION;INSERT @T (col1) VALUES (1), (3);INSERT @T (col1) VALUES (1), (2), (4);COMMIT TRANSACTION;SELECT * FROM @T AS t;DELETE @T;BEGIN TRANSACTION;INSERT @T (col1) VALUES (1);INSERT @T (col1) VALUES (3);INSERT @T (col1) VALUES (1);INSERT @T (col1) VALUES (2);INSERT @T (col1) VALUES (4);COMMIT TRANSACTION;SELECT * FROM @T AS t;[/code]

RE: Transactions 3

John Mitchell-245523 — Thu, 31 May 2012 02:55:45 GMT

OK, thanks for all the replies. Plenty to think about here.[quote][b]Hugo Kornelis (5/30/2012)[/b][hr]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.[/quote]You're right - that's exactly how I was interpreting it. Not being a developer, I hadn't considered the other options, so thank you for clarifying that.[quote](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.)[/quote]Yes, it is easy to feel flamed when you find yourself proffering views that aren't agreed with by the wider community. I'm reassured by your comment.[quote][b]L' Eomot Inversé (5/30/2012)[/b][hr]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).[/quote]I understand and accept that. The reason I broadened the debate to include isolation was that I took your earlier comment (rightly or wrongly) as meaning "SQL Server doesn't honour the Isolation principle by default, and therefore we shouldn't necessarily expect it to honour the Atomicity principle". I agree that READ COMMITTED is, for many or most purposes, a good compromise between isolation and concurrency.[quote][b]sknox (5/30/2012)[/b][hr]Because you want them to commit or rollback as a unit. In other words, you want people viewing the data to either see all or none of the modifications.[/quote]Yes indeed. It all turns on whether you consider the success of all statements as being a necessary condition for a transaction to be committed. Like Hugo said, that's only one option.John

RE: Transactions 3

davoscollective — Wed, 30 May 2012 18:31:02 GMT

[quote] Transactions are not an [i]error-handling[/i] mechanism; they're a [i]data consistency[/i] mechanism. [/quote]I think that pretty much summarises this interesting discussion.

RE: Transactions 3

sknox — Wed, 30 May 2012 13:03:15 GMT

[quote][b]John Mitchell-245523 (5/30/2012)[/b][hr][quote][b]Hugo Kornelis (5/30/2012)[/b][hr][quote][b]John Mitchell-245523 (5/30/2012)[/b][hr]I must admit I'm surprised that having SET XACT_ABORT OFF is the default.[/quote]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.[/quote]But then why wrap statements in a transaction if you don't want them to succeed as a unit or fail as a unit?[/quote]Because you want them to commit or rollback as a unit. In other words, you want people viewing the data to either see all or none of the modifications. Transactions are not an [i]error-handling[/i] mechanism; they're a [i]data consistency[/i] mechanism. [quote]My point is that the default setting means that Atomicity ([i]A transaction must be an atomic unit of work; either all of its data modifications are performed, or none of them is performed[/i]) is not honoured. [/quote]I disagree. Atomicity [b]is[/b] honored in this case -- because there are only two valid data modifications in the transaction. The invalid INSERT in this case does not make it to the level of a data modification because it errors out before data is modified.

RE: Transactions 3

Hugo Kornelis — Wed, 30 May 2012 12:33:50 GMT

[quote][b]John Mitchell-245523 (5/30/2012)[/b][hr][quote][b]Hugo Kornelis (5/30/2012)[/b][hr]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.[/quote]But then why wrap statements in a transaction if you don't want them to succeed as a unit or fail as a unit?[/quote]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.[quote]That's interesting. Books Online says this in the Isolation Levels in the Database Engine topic:[/quote]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).[quote]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.[/quote]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.[quote]And I never said that XACT_ABORT OFF causes constraint violations. My point is that the default setting means that Atomicity ([i]A transaction must be an atomic unit of work; either all of its data modifications are performed, or none of them is performed[/i]) 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.[/quote]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.[quote]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.[/quote]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.)

RE: Transactions 3

IgorMi — Wed, 30 May 2012 11:45:25 GMT

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 anywayIgorMi

RE: Transactions 3

SQLRNNR — Wed, 30 May 2012 11:17:34 GMT

Keep it up Ron - doing good with these questions.

RE: Transactions 3

L' Eomot Inversé — Wed, 30 May 2012 10:19:16 GMT

[quote][b]John Mitchell-245523 (5/30/2012)[/b][hr]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.[/quote]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 [b]would[/b] 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).

RE: Transactions 3

SQLDCH — Wed, 30 May 2012 09:49:21 GMT

Thanks for the question, good reinforcement.

RE: Transactions 3

Revenant — Wed, 30 May 2012 09:26:25 GMT

A good one, Ron - thanks!

RE: Transactions 3

OzYbOi d(-_-)b — Wed, 30 May 2012 08:59:28 GMT

thanks for the question - cheers

RE: Transactions 3

John Mitchell-245523 — Wed, 30 May 2012 08:49:06 GMT

[quote][b]Hugo Kornelis (5/30/2012)[/b][hr][quote][b]John Mitchell-245523 (5/30/2012)[/b][hr]I must admit I'm surprised that having SET XACT_ABORT OFF is the default.[/quote]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.[/quote]But then why wrap statements in a transaction if you don't want them to succeed as a unit or fail as a unit?[quote][quote]I take the point that READ COMMITTED breaks the rules of isolation anyway[/quote]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.[/quote]That's interesting. Books Online says this in the Isolation Levels in the Database Engine topic:[i]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, [b]where transactions are completely isolated from one another[/b])[/i][quote][quote]but I find this one even more alarming. This from the Transactions topic in Books Online:[i]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.[/i]What it should really point out is that the facilities described in the first and third points aren't turned on by default.[/quote]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?[/quote]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 ([i]A transaction must be an atomic unit of work; either all of its data modifications are performed, or none of them is performed[/i]) 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.JohnEdit - corrected typo.Second edit - responded to the question about disabling the first and third points

RE: Transactions 3

Hugo Kornelis — Wed, 30 May 2012 08:17:02 GMT

[quote][b]roger.plowman (5/30/2012)[/b][hr]You know, if you took out the transaction the answer would be exactly the same? How then is this a transaction? :)[/quote]Replace the COMMIT with ROLLBACK - sufficient proof that this is a transaction?[quote]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.[/quote]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.[quote][b]John Mitchell-245523 (5/30/2012)[/b][hr]I must admit I'm surprised that having SET XACT_ABORT OFF is the default.[/quote]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.[quote]I take the point that READ COMMITTED breaks the rules of isolation anyway[/quote]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.[quote]but I find this one even more alarming. This from the Transactions topic in Books Online:[i]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.[/i]What it should really point out is that the facilities described in the first and third points aren't turned on by default.[/quote]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?

RE: Transactions 3

Dalton Moore — Wed, 30 May 2012 08:00:03 GMT

There goes my streak :(

RE: Transactions 3

John Mitchell-245523 — Wed, 30 May 2012 07:41:11 GMT

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:[i]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.[/i]What it should really point out is that the facilities described in the first and third points aren't turned on by default.John

RE: Transactions 3

Cliff Jones — Wed, 30 May 2012 07:40:36 GMT

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.

RE: Transactions 3

EL Jerry — Wed, 30 May 2012 07:29:37 GMT

Ron, thank you for the easy question.[quote][b]roger.plowman (5/30/2012)[/b]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.[/quote]This happens in this specific example, it doesn't mean it always happens with any transaction.[quote][b]L' Eomot Inversé (5/30/2012)[/b]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.[/quote]+1"El" Jerry.

RE: Transactions 3

L' Eomot Inversé — Wed, 30 May 2012 06:59:13 GMT

[quote][b]roger.plowman (5/30/2012)[/b][hr]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.[/quote]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.

RE: Transactions 3

roger.plowman — Wed, 30 May 2012 06:51:08 GMT

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.

RE: Transactions 3

Sherwin Anderson — Wed, 30 May 2012 06:02:59 GMT

Thanks Ron, easy one but worth remembering.

RE: Transactions 3

Koen Verbeeck — Wed, 30 May 2012 05:55:45 GMT

Thomas, have fun on your vacation!

RE: Transactions 3

Thomas Abraham — Wed, 30 May 2012 05:52:27 GMT

Just from the first 2 lines of code, I knew this was a bitbucket question. :-)Thanks for the easy (now that I've researched the topic) point.I doubt I will be missed, not being a major contributor here. But, I want to wish you all well, as I'll be on vacation for a few weeks. (My wife thinks the purpose of our trip is to revive the Greek economy. She'll try.)[b]Αντίο, και καλή τύχη με το ζήτημα της ημέρας.[/b](Goodbye, and good luck with the question of the day. Either that, or I just ordered 2 ouzos and a squid.)Edited to correct spelling of "question" - "quest" made it by spell check. Maybe I'll try German instead.

RE: Transactions 3

G Bryant McClellan — Wed, 30 May 2012 05:35:03 GMT

The repetition is a good idea but really depends on the audience. In high school Honors Calculus we has one basic proof show up on EVERY weekly quiz until everyone got it right. The rationale was that the proof was so basic to calculus in general that if you didn't get it, you were doomed. Eventually everyone got it right. I managed to get it from the first attempt onward although, for the life of me, I cannot remember that proof now.Point being that transaction control is so basic to developing robust transactional systems that a little repetition early can save some serious data headaches later.

RE: Transactions 3

bitbucket-25253 — Wed, 30 May 2012 05:21:51 GMT

[quote][b]L' Eomot Inversé (5/30/2012)[/b][hr]Until I saw the counts of answers so far I thought that maybe this repetition with tiny variations (this is the third version of this question in a couple of weeks) was a bit much, but then I saw that 29% (77 out of 270) so far have got this wrong, so now I reckon the repetition is useful after all.[/quote]Agreed it is somewhat of a repeat (with a slight, ever so slight variation), but going back to those prior questions on May 16th and 23rd) my last look at those answers:a. 16th 53% correctb. 23rd 47% correctSeems like there is still a great deal of learning that needs to be driven home.Now fore warned is fore armed, be prepared, the last in the series is scheduled for June 8th. If that is not answered 100% correctly, I guess I'll give up, no sense flogging a dead horse.

RE: Transactions 3

Koen Verbeeck — Wed, 30 May 2012 05:09:42 GMT

Another great question Ron. It certainly useful to refreshen the transaction theory from time to time.

RE: Transactions 3

call.copse — Wed, 30 May 2012 04:10:23 GMT

Well, I think I'll remember this one now anyhow! I did have to check the ON / OFF status and which meant what, as Microsoft do not always seem to agree with my mental logic on binary switches and how they are named. They did this time!

RE: Transactions 3

L' Eomot Inversé — Wed, 30 May 2012 02:56:30 GMT

Until I saw the counts of answers so far I thought that maybe this repetition with tiny variations (this is the third version of this question in a couple of weeks) was a bit much, but then I saw that 29% (77 out of 270) so far have got this wrong, so now I reckon the repetition is useful after all.

RE: Transactions 3

Toreador — Wed, 30 May 2012 02:05:35 GMT

Presumably the 21% who answered "0" didn't read the discussions following the previous question :)

RE: Transactions 3

Stewart "Arturius" Campbell — Wed, 30 May 2012 01:56:24 GMT

Good back-to-basic question, thanks Ron

RE: Transactions 3

davoscollective — Tue, 29 May 2012 23:00:54 GMT

Is this not the same question that we've had three times now?Maybe the addition of set XACT_ABORT OFFBut it's off by default, right? I guess it reinforces how transactions work in MSSQL so it's a good thing.

RE: Transactions 3

mohammed moinudheen — Tue, 29 May 2012 22:51:13 GMT

Good one. Got this right this time :-)

RE: Transactions 3

Nakul Vachhrajani — Tue, 29 May 2012 22:50:34 GMT

Nice & easy question. Brings back memories from a couple of days ago :-)

RE: Transactions 3

Hardy21 — Tue, 29 May 2012 22:33:30 GMT

Nice & easy question...

Transactions 3

bitbucket-25253 — Tue, 29 May 2012 20:23:55 GMT

Comments posted to this topic are about the item [B]Transactions 3[/B]