Sql-server – System failure causes inconsistency between two transactions

checkpointisolation-levellockingsql servertransaction

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?

Best Answer

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.