February 3, 2009 at 10:00 am
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 !
February 3, 2009 at 10:03 am
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
February 3, 2009 at 10:36 am
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?
February 3, 2009 at 11:07 am
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.
February 3, 2009 at 11:57 am
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
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply