June 27, 2012 at 1:02 am
hi
waiting for the reply
thanks
kumar
June 27, 2012 at 1:30 am
Once committed it is committed.
Unless of course you restore the database from a backup.
Can you descibe more what your concern is?
Mike
June 27, 2012 at 3:41 am
Mike John (6/27/2012)
Once committed it is committed....
Not for nested transactions, check this one:
begin transaction outerOne
update Table1 set Col1 = 'booo' where id =1
begin transaction innerOne
update Table2 set Col1 = 'opoo' where id =1
commit transaction innerOne
rollback transaction outerOne
Even so inner transaction "innerOne" was committed, due to rollback of outer transaction "outerOne", it was also rolled back.
June 27, 2012 at 4:33 am
If you're in a situation where you start a backup process and during that backup process you start a transaction. Then, the backup process completes, before the transaction. The transaction is committed. But if you run a restore, because that transaction wasn't committed during the backup process, it will be rolled back along with any other open transactions.
That's not exactly the same as simply restoring old data, but it's the only thing I can think of that's close to what you're talking about.
Although, I suppose, it might be possible for you to commit the transaction and just prior to the final write to disk the power goes out, during recovery of the database when you restart, you might not get that transaction committed. Although that's usually when you're facing corruption too. Otherwise because the transaction was in the log, it's getting to the disk one way or another.
Why are you asking?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 27, 2012 at 6:29 am
i think in sql server 2008 we can rollback the committed transaction.
but in server 2005 we can't.
can anyone suggest me what will be reason.
June 27, 2012 at 6:32 am
Reddy Ksr (6/27/2012)
i think in sql server 2008 we can rollback the committed transaction.but in server 2005 we can't.
can anyone suggest me what will be reason.
Where are you getting this idea from?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 27, 2012 at 6:47 am
Reddy Ksr (6/27/2012)
i think in sql server 2008 we can rollback the committed transaction.but in server 2005 we can't.
can anyone suggest me what will be reason.
no. a commited transaction is committed, and cannot be rolled back.
no version of any SQL changes that behavior.
you can use things like log recovery tools to discover what has been done previously, and create new transactions, which, in effect, reverse the work that was done previously.
That behavior is not a rollback, it'st the same as manually updating the name column from 'Jon' to 'Jonathan', and then running a new statement to put the value back to 'Jon'
is that what you are after? trying to undo something someone else did?
Lowell
June 27, 2012 at 7:06 am
Reddy Ksr (6/27/2012)
i think in sql server 2008 we can rollback the committed transaction.
You cannot.
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
June 27, 2012 at 3:54 pm
Eugene Elutin (6/27/2012)
Mike John (6/27/2012)
Once committed it is committed....
Not for nested transactions, check this one:
begin transaction outerOne
update Table1 set Col1 = 'booo' where id =1
begin transaction innerOne
update Table2 set Col1 = 'opoo' where id =1
commit transaction innerOne
rollback transaction outerOne
Even so inner transaction "innerOne" was committed, due to rollback of outer transaction "outerOne", it was also rolled back.
The inner "transaction" wasn't actually committed because it's not really a true transaction. SQL Server does not support nested transactions. It allows the sytnax shown above to be used, but only the outermost transaction is a true transaction, and only that transaction controls whether the statements in the transaction are committed or rolled back.
MS states this very clearly in their documentation for SQL Server, which is my source for this info.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
June 27, 2012 at 4:44 pm
ScottPletcher (6/27/2012)
Eugene Elutin (6/27/2012)
Mike John (6/27/2012)
Once committed it is committed....
Not for nested transactions, check this one:
begin transaction outerOne
update Table1 set Col1 = 'booo' where id =1
begin transaction innerOne
update Table2 set Col1 = 'opoo' where id =1
commit transaction innerOne
rollback transaction outerOne
Even so inner transaction "innerOne" was committed, due to rollback of outer transaction "outerOne", it was also rolled back.
The inner "transaction" wasn't actually committed because it's not really a true transaction. SQL Server does not support nested transactions. It allows the sytnax shown above to be used, but only the outermost transaction is a true transaction, and only that transaction controls whether the statements in the transaction are committed or rolled back.
MS states this very clearly in their documentation for SQL Server, which is my source for this info.
To make sure the outer transaction is committed (or rolled back), you can use this statement:
-- To commit outer transaction
while @@trancount > 0 begin commit end
-- To rollback outer transaction
while @@trancount > 0 begin rollback end
June 28, 2012 at 5:23 am
create table (id int,subid int)
begin transaction outerOne
insert into bitwise values(100,25)
begin transaction innerOne
insert into bitwise values(150,25)
commit transaction outerOne
rollback transaction outerOne
Not only for innerone transaction as mentioned above reply, even i committed the outerone transaction, i am able to rollback it again by immediately executing the rollback transaction in server 2008.
can anyone try this as it. execute it in sequence as it appear above.
June 28, 2012 at 5:43 am
It is correct, if there is any open transaction while executing the Rollback transaction command.
Check if any transactions are open after commiting transaction by using this command
SELECT @@TRANCOUNT.
If you get value more than 0 then there is an open transaction and now if you exec the rollback transaction it will rollback all the transactions and will make the value of @@TRANCOUNT = 0.
June 28, 2012 at 6:08 am
Reddy Ksr (6/28/2012)
Not only for innerone transaction as mentioned above reply, even i committed the outerone transaction
You didn't commit the outermost transaction. Naming of transactions is purely for documentation, it has no effect on the behaviour of commit or rollback. What you did there was decrement the open tran count by one, then roll back the entire transaction. Nothing was ever committed
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 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply