September 20, 2012 at 12:20 pm
stevro (9/20/2012)
The other possible query I can think of is SELECT INTO. This wont guarantee Atomicity and Consistency as the INTO table might still be created even if the transaction was rolled back. I think this is a SQL Server design fault
Did you try that?
BEGIN TRAN
SELECT * INTO Gone FROM sys.objects AS o
ROLLBACK TRAN
SELECT * FROM Gone
I have read in Paul NielNielsen Server bible 2008 that in the simple recovery model transactions wont guarantee the durability principle?
Nope, he's either wrong or is using a very extreme definition of 'durable'
The durability requirement states that once a transaction commits, the changes it made are permanent within the database. They are, once the commit has occurred, a change cannot be rolled back and the fact that the changes are hardened in the log ensures SQL will replay them if there's a crash immediately after the commit.
If we were to use Paul's definition, then no recovery model in SQL guarantees durability as if the server SAN catches fire the second after the commit, the changes that transaction made will disappear (along with the rest of the DB)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 20, 2012 at 12:20 pm
Sean Lange (9/20/2012)
The other possible query I can think of is SELECT INTO. This wont guarantee Atomicity and Consistency as the INTO table might still be created even if the transaction was rolled back
Where did you find this? I don't think that is true at all. The rollback would remove the table.
begin transaction
select 5 as Col1 into SomeSillyTable
rollback transaction
select * from SomeSillyTable
Yep. Here are the messages I get when I run the code:
(1 row(s) affected)
Msg 208, Level 16, State 1, Line 7
Invalid object name 'SomeSillyTable'.
September 20, 2012 at 1:39 pm
Where did you find this? I don't think that is true at all. The rollback would remove the table.
begin transaction
select 5 as Col1 into SomeSillyTable
rollback transaction
select * from SomeSillyTable
Yes, for explicit transactions it will roll back fine, but not for implicit ones:
select 5/0 as Col1 into SomeSillyTable
select * from SomeSillyTable
..Still creates that silly little table, even though it rolled back the transaction..
September 20, 2012 at 1:43 pm
Nope, he's either wrong or is using a very extreme definition of 'durable'
Yes, I think you are right on the "extreme" definition, but he is clearly wrong if he states that the Full recovery model will then be durable. As you said, in this case nothing is durable..
September 20, 2012 at 1:51 pm
That's not an implicit transaction, that's auto-commit mode. Implicit transactions is when you have SET IMPLICIT TRANSACTIONS ON.
The create table doesn't roll back for the same reason the create table doesn't roll back in this example
BEGIN TRAN
CREATE TABLE SillyTable (SomeVal int)
INSERT INTO SillyTable
SELECT 1/0
COMMIT TRANSACTION
A select into is not a single operation, it's 2 individual operations each in auto-commit mode, the first a DDL operation that creates the table, the second a DML that allocates the pages and inserts the rows. The error rolls back the second, not the first.
Errors don't automatically cause transaction rollbacks.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 20, 2012 at 2:33 pm
stevro (9/20/2012)
Where did you find this? I don't think that is true at all. The rollback would remove the table.
begin transaction
select 5 as Col1 into SomeSillyTable
rollback transaction
select * from SomeSillyTable
Yes, for explicit transactions it will roll back fine, but not for implicit ones:
select 5/0 as Col1 into SomeSillyTableselect * from SomeSillyTable
..Still creates that silly little table, even though it rolled back the transaction..
SELECT INTO is a proprietary carryover from Sybase Transact-SQL and it likely was maintained to behave this way for backward compatibility. I do not agree with the implementation. I think the table creation should be rolled back in case of the failed insert.
The creation of the table ahead of time appears to be a bit of syntax sugar but there are minimal logging optimizations with SELECT INTO that take it beyond my standard classification of sugar. The behavior is easily worked around using code setup as Sean showed and that would not affect any of the logging optimizations mentioned.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
September 21, 2012 at 4:25 am
GilaMonster (9/20/2012)
The create table doesn't roll back for the same reason the create table doesn't roll back in this example
BEGIN TRAN
CREATE TABLE SillyTable (SomeVal int)
INSERT INTO SillyTable
SELECT 1/0
COMMIT TRANSACTION
A select into is not a single operation, it's 2 individual operations each in auto-commit mode, the first a DDL operation that creates the table, the second a DML that allocates the pages and inserts the rows. The error rolls back the second, not the first.
Errors don't automatically cause transaction rollbacks.
This is not the same. The reason this is not rolled back is because XACT_ABORT is set to OFF on your client.(the deafult for SSMS, but OLEDB this is set on by default). We cant conrol this behaviour with SELECT INTO:
SET XACT_ABORT ON;
BEGIN TRAN
CREATE TABLE SillyTable1 (SomeVal int)
INSERT INTO SillyTable1
SELECT 1/0
COMMIT TRANSACTION;
SELECT 1/0 As SomeVal INTO SillyTable2;
The creation of SillyTable1 will be rolled back but not SillyTable2
I agree as you said that it is two individual operations, but going back to the original question, that is just the point I am making. It is still not and Atomic statement.
I agree with opc.three that is should not be implemented that way.
September 21, 2012 at 5:36 am
stevro (9/21/2012)
I agree as you said that it is two individual operations, but going back to the original question, that is just the point I am making. It is still not and Atomic statement.
Statements are not atomic. Transactions are. Now, most statements are wrapped into individual transactions, select into however is two transactions, not one. Hence why it rolls back in two pieces.
Atomic doesn't mean that if any part fails the entire thing will be rolled back (because that doesn't happen by default). It means if the transaction does not complete (the commit isn't reached), no part of the transaction will complete.
p.s. Ok, the example I gave was not equivalent. This is the equivalent of the select into
BEGIN TRANSACTION
CREATE TABLE SillyTable1 (SomeVal int)
COMMIT TRANSACTION
BEGIN TRANSACTION
INSERT INTO SillyTable1
SELECT 1/0
COMMIT TRANSACTION
And yes, I have xact abort off. I don't like automatic rollbacks in the case of errors, I like to handle my errors.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 21, 2012 at 9:54 am
GilaMonster (9/21/2012)
Statements are not atomic.
Atomicity is a database principle(all or nothing), it is not to say it can/cant belong to something like a statement. You probably meant that they dont ensure atomicity like transactions.
The only single statement I know of that is not atomic is SELECT INTO. It is not Atomic because as you said it consist of is two different transactions.
Again, going back to the original question, the only two queries/single statements that I can think of that does not conform to the ACID principles are Select INTO and NOLOCK queries. There might be others?
Obviously, if we are talking explicit transactions or batches with multiple statements, then it is not applicable and is a new discussion.
And yes, I have xact abort off. I don't like automatic rollbacks in the case of errors, I like to handle my errors.
I allways like to have XACT_ABORT set to ON. It does not stop me from handling my errors, but ensure that any uncatchable/unhandled client errors like timeouts will be rolled back.
September 21, 2012 at 10:04 am
stevro (9/21/2012)
Again, going back to the original question, the only two queries/single statements that I can think of that does not conform to the ACID principles are Select INTO and NOLOCK queries. There might be others?
Probably. I'd suspect some of the DDL statements are done as multiple transactions. Easy enough to check if you want.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 10 posts - 16 through 24 (of 24 total)
You must be logged in to reply to this topic. Login to reply