Re: computational model of transactions

"vc" <boston103_at_hotmail.com> wrote in message news:1154640950.073211.183800_at_s13g2000cwa.googlegroups.com...
> Brian Selzer wrote:
> [...]
>> SQL Server 2005 provides this capability, it's called READ COMMITTED
>> SNAPSHOT isolation. It accomplishes it by accessing the previous states
>> of
>> the rows that have been changed in the transaction log.
>
> That is not entirely corect. SQL Server 2005 actually has two
> varieties of the SNAPSHOT isolation that Microsoft faithfully, at least
> at the conceptual level, copied from Oracle: read commtted snapshot
> and transactional snapshot which correspond to Oracle's READ COMMITTED
> and SERIALIZABLE respectively. The only true innovation MS can boast
> about would be the correct name for the transactional snapshot (rather
> than the Oracle 'serializable'). It's worth to remind that Oracle has
> had the snapshot isolation aka row versioning aka MVCC since at least
> 1984 (version 4).
>

Thank you for pointing that out.

>> The mechanism is
>> similar to that used by the Volume Shadow Copy service in Windows 2003
>> and
>> many of the open file backup options that have been available for years.
>
> The above statement does not make any obvious sense in the context of
> database concurrency control (except perhaps some superficial
> similarities).
>

Both of those mechanisms cache old versions of disk blocks so that what is copied to disk or tape is exactly the same as it was at the time that the copy began. Intervening updates are not included in the copy. This has significance in this context because that's exactly what happens with snapshot isolation. Intervening updates to any row read within a transaction are hidden, ensuring that everything read out originated from the same consistent database state.

>>
>> The benefit of this mechanism is that it provides the same level of
>> protection for the issuing transaction as READ COMMITTED without applying
>> any shared locks. Fewer locks means less blocking and fewer deadlocks.
>> Fewer locks means better overall performance. But it's not a silver
>> bullet.
>> There are times when it is definitely contraindicated. The danger is
>> that
>> DBAs and developers will use it without fully understanding the
>> ramifications of using it.
>
> Such DBA and developers have only themselves to blame for the
> hypothetical ignorance since various MVCC systems have been readily
> available for use and study for many years, e.g. Interbase, Rdb,
> Postgres (in addition to Oracle).
>

Agreed. But SQL Server is and has been very popular, and not every practitioner has had experience with those other databases.

>>
>> This should never be used in a posting routine because the information
>> read
>> out is already stale even before the read is completed, so a subsequent
>> update based on that information would introduce garbage into the
>> database.
>> It is inevitable that overzealous DBAs and developers will use this to
>> boost
>> performance and end up with corrupt databases.
>
> Unfortunately, without further elaboration, the above paragraph does
> not make much sense.
>

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.

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.   Received on Fri Aug 04 2006 - 08:31:07 CEST