0

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: enter image description here 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?

5

Regarding visibility, SQL Server uses locking and/or row-versioning to implement isolation levels. Changes are always visible to the current session. Uncommitted changes are visible to other sessions only they are using the UNCOMMITTED ("daring they could get a dirty read"). Normally, other sessions will be blocked (due to locking) or will see the original value before changes (due to row versioning) until the transaction is committed.

Transaction isolation levels, and their implementation, are not related to transaction durability. It doesn't matter when log and data file writes occur for isolations levels to behave properly.

To guarantee durability and atomicity, log records are always written to the log before the corresponding data files. Importantly, this includes changes for both uncommitted and committed transactions. This write-ahead logging allows SQL Server to roll forward all transactions and then rollback uncommitted ones during recovery as well as when a ROLLBACK is performed.

  • @secondimage, I also added more info answering your question in this chat. – Dan Guzman Jun 30 at 12:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.