Where does the uncommitted updated data gets stored ??

  • I update the salary column of a table and do not commit the operation. let's say the existing value is 500 and I update it with 1000(not committed).

    From another session (read uncommitted isolation level) I access the salary column and find that the value is 1000.

    Now where does this value get stored temporarily ??? Does it store this value in tempdb or does it use log files for this purpose ???

    Thanx in advance !

    Sanz
  • santhosh (2/3/2009)


    Now where does this value get stored temporarily ??? Does it store this value in tempdb or does it use log files for this purpose ??

    Neither. That 1000 is the value in the table. The record will be locked to prevent anyone else reading it until the transaction is committed or rolled back. If it's committed, the value remains there. If it's rolled back, the transaction log is used to undo the operation.

    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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • If our database is in simple recovery mode. Is there any chance to overwrite the uncommitted data by any other user?

    If this happened how to rollback the data from transaction log? Could you please clarify?

    Sanz
  • santhosh (2/3/2009)


    If our database is in simple recovery mode. Is there any chance to overwrite the uncommitted data by any other user?

    If this happened how to rollback the data from transaction log? Could you please clarify?

    A database using the simple recovery model still uses the transaction log. If you rollback the transaction, it will use the information in the log to restore the original value. As long as the transaction is open (not committed or rolledback), no other user will be able to update the record.

  • santhosh (2/3/2009)


    If our database is in simple recovery mode. Is there any chance to overwrite the uncommitted data by any other user?

    Recovery model has no effect on how transactions are run or logged. It only affects how long log records are retained in the log after the transaction has been 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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply