My textbook says:
Starting when you begin a transaction, your changes are isolated from other users. What you’re doing is visible to you only,and isn’t really done until you COMMIT — although it looks real to you, only you can see that result. Anyone else attempting to look, they could see the old value, or if they’re daring they could get a dirty read.
I am confused, so I will use a picture below to raise some questions:
and the t-sql code is
BEGIN TRAN
UPDATE checking
SET Balance = Balance - 1000
WHERE Account = 'Sally' // original balance is 2000
--------------a checkpoint occurs ---------
UPDATE savings
SET Balance = Balance + 1000
WHERE Account = 'Sally'
COMMIT TRAN
and we know that a new log record describing the COMMIT will only be created in the log buffer after 'COMMIT TRAN' has been executed.
Q1- Why the result is only visible for me before COMMIT? let's say a checkpoint happens after checking for sally has been minus -1000, and let's the the updated record is in page 4(in memory), since it hasn't been commit yet, so there won't be a log record in the log buffer, therefore there won't be a corresponding log record in disk(.ldf), but the cache page 4 will be written to disk (.mdf), so sin't that any user can see the latest change for the record?
Q2-How transaction control maintains atomicity in this case?
As I have discussed, when the checkpoint happens, page 4(in cache, and the containing record's latest balance value: 1000) will be written to disk, and if there is a system failure immediately. After SQL server restarts, how does it know how to restore the record's balance(currently 1000) back to its original value(balance is 2000), since there is no log record in .ldf file?
