-3

enter image description here

Let's say the picture above the transaction 4 for user A.

A updated a record from Account table, change the balance from 100 to 0, then a checkpoint occurred, so all dirty pages got written to disk.

At time t, user B checked the Account table and add records whose balance is zero to Audit table and commit immediately before the system failure. Then a system failure occurred, so the transaction 4 was rolled-back, and for account id 1234, then balance was 100 again, so this account id shouldn't be in the Audit table, but it was because user B added it. so how to tackle this inconsistency?

| improve this question | |
4

The question is invalid - either Transaction 4 committed before the system failure or it did not, but you've stated that it did commit, but then rolled back and your diagram suggests the transaction was still uncommitted at the point of system failure.

If Tran 4 committed before the failure, then it was written to the transaction log and would be rolled forward into the database during recovery. Subsequent queries to the audit table would show a balance of 0.

If Tran 4 had not committed before the failure, recovery would have rolled the transaction back and subsequent queries to the Audit table would show a balance of 100.

If your audit table has to be historically and transactionally consistent, you need to update your audit table in the same transaction that you update your source table to maintain a perfect historical record that is transactionally consistent across both tables.

If the audit only needs to have the latest value and not interim values, then you should write a 'true-up' script that will update the Audit table with the latest values from the source table and run this as a post-recovery step, or even as a regular agent job.

| improve this answer | |
  • I didn't say transaction 4 was committed. I mean another user B start a new transaction(let's say it is transaction 5) at time t and insert records into Audit table based on the balance(account id 1234 was in Audit table at this point) and transaction 5 was committed before system failure. But transaction 4 will be rolled back, the balance of account 1234 in Account table will be rolled back from 0 to 100. So after the recovery, account 1234 shouldn't be in Audit table, because Audit table should only contain account id whose balance is 0. Can locking solve this problem? – slowjams Jul 3 '19 at 6:43
  • 1
    If Tran 4 hadn't committed but Tran 5 read the uncommitted value in Audit table from Tran 4, then you're using an isolation level like READ UNCOMMITTED, which can result in dirty reads, the scenario you're describing. See here. – HandyD Jul 3 '19 at 6:47
  • Oh I See, I need to set the isolation level to Read Committed at least? – slowjams Jul 3 '19 at 23:33
  • 1
    Yes, read committed (which is the default) is probably your best bet. – HandyD Jul 4 '19 at 0:49
  • could you also take a look at this question? thanks – slowjams Jul 4 '19 at 1:00

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.