Re: computational model of transactions


"vc" <boston103@xxxxxxxxxxx> wrote in message
news:1154640950.073211.183800@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
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.
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.



.

Relevant Pages

  • Re: Calculated value dilemma
    ... the database accurate, easy to use, and able to expand (the current ... I am considering having a transaction ... and I should therefore simply calculate the balance ... aggregate and the values that combine to form the aggregate is not due to a ...
    (comp.databases.theory)
  • Re: Calculated value dilemma
    ... the database accurate, easy to use, and able to expand (the current ... and I should therefore simply calculate the balance ... Historical data should accurately reflect the history. ... calculated value of the balance would be dependent on the transaction ...
    (comp.databases.theory)
  • Re: Calculated value dilemma
    ... the database accurate, easy to use, and able to expand (the current ... I am considering having a transaction ... and I should therefore simply calculate the balance ... value of some attribute where no history is stored? ...
    (comp.databases.theory)
  • Re: Stateless clients and locking schemes (or rather isolation levels)
    ... > locking" has the same problem as replication: ... >>> stateless clients. ... >>> statement or a transaction level. ... >>> How does snapshot isolation help here? ...
    (comp.databases.informix)
  • Snapshot isolation with pooled connections
    ... Please let me know if it's possible to use snapshot isolation with the MS ... Only connections that were previously ... insert the row that has been modified or deleted by another transaction. ... public void run{ ...
    (microsoft.public.sqlserver.jdbcdriver)
Quantcast