locked
Where is the data stored of a uncomitted transaction? RRS feed

  • Question

  • Hi,

          Suppose i started a transaction,updated something ,now when i select i  will get the latest ,where are the old values stored as a backup,which is needed when rollback.

     

     

    Regards

    Chaithu

     

       

    Friday, April 29, 2011 7:16 AM

Answers

  • What you need to know is how transaction log works. When a transaction whether implicit or explicit is executed the old values and new values are written to the log first and when successful, flushed to disk and the LSN marked in the data file. 

    In case of Rollback or some error, the concerned VLF is marked and transaction closed.

    That is why the log is said to be WRITE AHEAD log.

    • Marked as answer by Stephanie Lv Tuesday, May 10, 2011 4:51 AM
    Friday, April 29, 2011 7:46 AM
  • Suppose i started a transaction,updated something ,now when i select i  will get the latest ,where are the old values stored as a backup,which is needed when rollback.   


    As the others have mentioned, records of data modifications (before and after data images) are stored in the transaction log.  If the UPDATE statement is uncommitted, you can simply ROLLBACK the transaction to undo the changes.

    After the UPDATE transaction is committed, the changes are permanent.  If you need to revert data to before the update was made in the FULL or BULK_LOGGED recovery model, you will need to backup the transaction log, restore the entire database (with NORECOVERY) and apply transaction log backups up to a point before the UPDATE.  In the case where there are other changes after the UPDATE was done that you need to retain, you can restore to a separate database and extract the needed data from there.

     

     

     


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/
    • Marked as answer by Stephanie Lv Tuesday, May 10, 2011 4:51 AM
    Friday, April 29, 2011 12:06 PM

All replies

  • That's the purpose of the transaction log, if I don't missunderstand you...
    Friday, April 29, 2011 7:20 AM
  • The old values havent been changed in the datafiles, wont till next checkpoint after your commit i think
    Friday, April 29, 2011 7:22 AM
  • Hi Carlos,

     

                            You mean the old  values are taken from the transaction log when rollback?

     

    Regards

    Chaithu

    Friday, April 29, 2011 7:28 AM
  •  

    Here is good explanation od WAL protocol (SQL Server 2000) http://technet.microsoft.com/en-us/library/cc966500.aspx


    Slaven Sola MCITP DBA,MCT
    • Proposed as answer by Abhay_78 Friday, April 29, 2011 10:14 AM
    Friday, April 29, 2011 7:29 AM
  • Hi,

           soon after we update,if select statement is executed,the new values are populated in the resulted,Now the old values  should been stored somewhere right?how and where they get stored?when rollback is executed ,we need old values which should be replaced right?Hope u got  my question..

     

    Regards

    Chaithu

     

    Friday, April 29, 2011 7:31 AM
  •  

    If you perform something like this

    Begin Tran

    Update a

    set col1  = 1

    From t1 as

    where col1 =2

    -- note that transaction is not commited

    if you run

    Select col1

    from t1

    where col1 = 2

    -- your process will be blocked in default SQL Server isolation level and higher

    Please read article which I sent you.  


    Slaven Sola MCITP DBA,MCT
    Friday, April 29, 2011 7:37 AM
  • What you need to know is how transaction log works. When a transaction whether implicit or explicit is executed the old values and new values are written to the log first and when successful, flushed to disk and the LSN marked in the data file. 

    In case of Rollback or some error, the concerned VLF is marked and transaction closed.

    That is why the log is said to be WRITE AHEAD log.

    • Marked as answer by Stephanie Lv Tuesday, May 10, 2011 4:51 AM
    Friday, April 29, 2011 7:46 AM
  • Every DML is executed as an implicit transaction. So in case you update a column and the operation completes with success then the only value in effect is the current value and the old value is now not preserved anywhere.

     

    If the DML takes place within an explicit transaction (meaning after a BEGIN TRAN; ) then the operation only takes place at the t-log at first. Once the tran is explicity commited by a COMMIT; command then the DML is commited/ flushed into the data file and at that point  there is no remainder to the old value. However, during the time period that the tran is open (in between the BEGIN TRAN; and COMMIT TRAN;) the old value still exists and so if the tran is rolled back for any reason the value remains. This is done so in order to maintain the atomic property of a transaction making it an all or nothing operation if properly written.

    If you select data from a modified row during the tran in any isolation level other tan the READ UNCOMMITED  you will get blocked until the tran is over and this is because sql server holds locks on that data so no other connection can modify that data so in case the tran is rolled back the data is protected/ guaranteed to be the same.


    -- Yaniv www.sqlserverutilities.com http://blogs.microsoft.co.il/blogs/yaniv_etrogi
    Friday, April 29, 2011 8:13 AM
  • The answer is "in your backups".

    Essentially you need to perform regular Transaction Log Backups in order to provide a reliable rollback position.  Remember that Transactions can still be committed by other processes even though a script may not have implicitly applied a COMMIT (one such example is a CHECKPOINT, another is a restart of SQL Server).

    Therefore ensure that you have a Full backup and regular Transaction Log Backups; it also a good idea to prove the backups by performing test restores (to a point in time).


    Please click "Mark As Answer" if my post helped. Tony C.
    Friday, April 29, 2011 11:04 AM
  • Suppose i started a transaction,updated something ,now when i select i  will get the latest ,where are the old values stored as a backup,which is needed when rollback.   


    As the others have mentioned, records of data modifications (before and after data images) are stored in the transaction log.  If the UPDATE statement is uncommitted, you can simply ROLLBACK the transaction to undo the changes.

    After the UPDATE transaction is committed, the changes are permanent.  If you need to revert data to before the update was made in the FULL or BULK_LOGGED recovery model, you will need to backup the transaction log, restore the entire database (with NORECOVERY) and apply transaction log backups up to a point before the UPDATE.  In the case where there are other changes after the UPDATE was done that you need to retain, you can restore to a separate database and extract the needed data from there.

     

     

     


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/
    • Marked as answer by Stephanie Lv Tuesday, May 10, 2011 4:51 AM
    Friday, April 29, 2011 12:06 PM