Re: computational model of transactions

Brian Selzer wrote:

> Consider a checking account from a bank that has an overdraft limit of $100.
> Customers will be charged $30 for insufficient funds, regardless of whether
> the check clears or bounces.
>
> Let's say that you're account starts out with a balance of $550.
>
> At time T1 a transaction is started to post a check for $500.
> At time T2 a transaction is started to post a check for $100.
> At time T3 a transaction is started to post a check for $75.
> At time T4 the $100 transaction completes, recording a balance of $450.
> At time T5 the $75 transaction completes, recording a balance of $375.
> At time T6 the $500 transaction completes, recording a balance of $-125.
>
> This is clearly an error, since no insufficient funds fees were charged.
> The problem is that when the $500 transaction started, the balance was read
> out at $550 and $550 - $500 is positive, and since all reads within the same
> transaction appear to come from the same consistent database state--that is,
> the one that existed at T1, the balance does not appear to have changed. In
> fact, none of the transactions appear to have insufficient funds at the
> start of their respective transactions, yet the end result is $125 in the
> hole. Furthermore, the $500 check should have bounced, because the
> overdraft limit was exceeded, so even adding the insufficient funds charge
> in a separate transaction wouldn't work in this case. Instead, an
> additional transaction would need to be issued to reverse the committed $500
> transaction.

Your description is completely incorect. Any MVCC worth its name would not admit the sequence you've described. For example in Oracle (assuming SERIALIZABLE):

"At time T4 the $100 transaction completes, recording a balance of $450." allright,
but
at time T5 the $75 transaction aborts with a 'cannot serilize message' likewise, at time T6 the $500 transaction also aborts with the same message.

One would sincerely hope that MS SQL developers have done a good job in their attempt to emulate Oracle's SERIALIZABLE. Received on Sat Aug 05 2006 - 05:17:28 CEST