Re: SQL SERVER Rollback Problems

From: Andrew J. Kelly (sqlmvpnooospam_at_shadhawk.com)
Date: 10/09/04 

Date: Sat, 9 Oct 2004 10:48:07 -0400

When you issue a BEGIN TRAN everything from there on is wrapped in that
transaction. It will not be fully committed until you issue a COMMIT TRAN.
If your sp had an error or was manually stopped the transaction is still
open and will sit like that until you issue a COMMIT or a ROLLBACK. If you
issue the ROLLBACK ALL operations are rolled back up until that first BEGIN
TRAN. It is up to you to issue the COMMIT or ROLLBACK unless you have SET
XACT_ABORT ON (See BOL for details). But in any case ALL the transactions
in there are either fully committed when you issue a COMMIT or ALL of them
are rolled back in the case of a ROLLBACK. This is how the integrity the
database is upheld and is no different than in any other modern relational
db. If you lost data it was due to something you did and not the engine. 

-- 
Andrew J. Kelly  SQL MVP
"Sajan Rajagopal.M" <samy_mss@rediffmail.com> wrote in message
news:1d9001c4adf8$6931c2e0$a301280a@phx.gbl...
> I have included a query (procedure) inside a begin and
> rollback. The Query got Hanged and i stopped the
> execution. Upon Stopping the query didnt get rollbacked
> instead it got executed and i had lost few data as there
> was a delete scripts inside my query.After that i executed
> the ROLLBACK alone and it got executed.
> Kindly tell me if this is a limitation in SQL server or is
> there any patch to be run in order to prevent this. This
> is a show stopper in my organisation as all the Back End
> Operations are totally dependant on SQL server and if this
> case keeps continuing i may have to take precautionary
> measures before executing even inside a BEGIN and ROLLBACK
> transaction.
> Thanking You
>
> Saminathan.S
> (Cognizant Technologies Solutions)

Relevant Pages

  • Re: Classic Nest SP with Transaction Question
    ... PMFJI, but if your child proc is using an explicit tran, then it can be coded as follows: create proc dbo.ChildProc as set nocount on declare @trancount int set @trancount = @@TRANCOUNT if @trancount> 0 begin tran ChildProcTran else save tran ChildProcTran /* ... Do some stuff */ if @@ERROR> 0 begin raiserror rollback ChildProcTran return end ... commit tran go ... I believe I'm having the same issue as Chad with nested stored procedures inside a transaction. ...
    (microsoft.public.sqlserver.programming)
  • Re: Is statement.executeBacth() a transaction?
    ... point and did not make my question clearer enough. ... rollback() in the catch since the driver already does that, ... The reason I am confused about it is that the "transaction" nature ... A commit is a commit, ...
    (comp.lang.java.programmer)
  • Re: how to roll back
    ... You can only use rollback within a transaction block. ... If you commit a ... COMMIT TRAN, then ROLLBACK TRAN to ...
    (microsoft.public.sqlserver.server)
  • Re: help - transaction control
    ... transaction to successfully commit before any of the inner ones are ... If a Rollback is issued anywhere along the way ALL transactions ... ROLLBACK TRAN ... > My understanding of the way that transactions nest> is that I should be able to have each SP> begin and commit a transaction within itself,> and if the SP happens to be called from some context> that has begun a transaction, then the SP would participate> in that transaction context. ...
    (microsoft.public.sqlserver.programming)
  • Re: [RFC] JBD ordered mode rewrite
    ... data buffers that need syncing on transaction commit but a list of inodes ... that need writeout during commit. ... the file then that inode would be added to the journal list and the pages ...
    (Linux-Kernel)