Multi-Version Concurrency Control (MVCC) is a complex technique for
enhancing database performance in a multi-user environment. In
contrast to most other database systems which use locks for
concurrency control, Daffodil DB preserves uniformity of data by using
a Multi-Version model.

MVCC ensures that each transaction obtains a snapshot of data as per
the last consistent state of the database. This shields transactions
from viewing inconsistent data caused by concurrent updates on the
same data by other transactions.


The main difference between Multi-Version and lock models is that in
MVCC locks attained for reading data do not conflict with locks
acquired for writing data. This ensures that reading never blocks
writing and vice

Transaction Isolation Levels

The ANSI/ISO SQL standard characterizes four level of transaction
isolation based upon three incidences that must be prevented between
concurrent transactions. These undesirable incidences are:


1. Dirty / Inconsistent reads

In this, the result of a query includes data written by a concurrent
uncommitted transaction.
2. Non-repeatable reads

A transaction re-reads data it has previously read and finds that data
has been modified by another transaction (that committed since the
initial read).

3. Phantom read

A transaction re-executes a query, returning a set of rows that
satisfy a search condition and finds that the set of rows satisfying
the condition has changed due to another recently committed
transaction.

The transaction isolation levels have been categorized in following
four forms,

1. Read uncommitted

2. Read committed

3. Repeatable read

4. Serializable

Daffodil DB supports all isolation levels. Let us have a detailed look
at all of them.


1. Read Uncommitted Isolation Level


The Read Uncommitted Isolation Level allows a transaction to access
uncommitted changes that have been made by other transactions. A
transaction using the Read Uncommitted Isolation Level cannot prevent
other transactions from modifying the row of data being read.
Therefore, transactions are not isolated from each other. If the Read
Uncommitted Isolation Level is being used by a transaction that is
working with a data source (which supports other transaction isolation
levels), the data source ignores the mechanism used to implement those
levels.

(Note: Read Uncommitted Isolation Level is typically used by read-only
transactions.)


2. Read Committed Isolation Level


Read Committed is the default isolation level in Daffodil DB. When a
transaction runs on this isolation level, a SELECT query is able to
extract the data committed before the query began and does not get a
view of changes (uncommitted or committed) during query execution by
concurrent transactions.
(Note: Two successive SELECT can see different data, even though they
are within a single transaction, when other transactions commit
changes during execution of the first SELECT.)

The partial transaction isolation provided by Read Committed Level is
adequate for many applications, and this level is fast and simple to
use. However, for applications that do complex queries and updates, it
may be necessary to guarantee a more rigorous and consistent view of
the database.


3. Repeatable Read Isolation Level

The Repeatable Read Isolation Level permits a transaction to obtain
read locks on all rows of data it returns to an application, and write
locks on all rows of data it inserts, updates, or deletes.

By using the Repeatable Read isolation level, SELECT statements issued
multiple times within the same transaction will always yield the same
result. A transaction using the Repeatable Read isolation level can
retrieve and manipulate the same rows of data as many times as needed
until it completes its task.

However, other transactions can insert, update, or delete a row of
data that would affect the result table being accessed until the
isolating transaction releases its locks.


4. Serializable Isolation Level


Serializable provides the highest transaction isolation. This level
follows serial transaction execution, as if transactions had been
executed one after another, serially, rather than concurrently.
However, applications using this level must be prepared to retry
transactions due to serialization failures.


When a transaction is on the serializable level, a SELECT query sees
the data committed before the transaction began and does not get a
view of either uncommitted data or changes committed during
transaction execution by concurrent transactions. (Note: This is
different from Read Committed in the sense that SELECT sees a snapshot
as of the start of the transaction, not as of the start of the current
query within the transaction.)

Users should keep in view that serializable transactions cannot modify
rows changed by other transactions after the serializable transaction
began.


Transaction isolation levels do not affect a transaction's ability to
see its own changes; transactions can always see any changes they
make. For example, a transaction might consist of two UPDATE
statements, but this succeeds as a single transaction only because the
second UPDATE statement can see the results of the first.

In case you need to know more about Transaction Isolation levels,
please feel free to contact us at support (AT) daffodildb (DOT) com.

Join other professional developers at
http://www.daffodildb.com:8080/forum/index.jspa .

Visit us at www.daffodildb.com .

On Thu, 15 Jul 2004 22:36:43 -0700, DaffodilDB Sales Team wrote (subj.
"Re: MVCC and Daffodil DB: Improved Performance in Multi-User Environment"):


Product promotion aside:
Isn't MVCC actually a rather common feature nowadays? PostgreSQL, Oracle,
Interbase, (MaxDB?, ) and InnoDB in MySQL use MVCC, as far as I know.
Does anyone know if the different MVCC implementations differ in
interesting ways?

--
Greetings from Troels Arvin, Copenhagen, Denmark

I would like to know if MVCC is the same thing as what DEC Rdb/VMS called
"snapshot transactions" (not to be confused with Oracle snapshots). If it
is the same thing, then I don't need to learn it. If it's profoundly
different, then I do.

Anybody out there who remembers DEC Rdb/VMS? (Since 1994, DEC RDb is known
as "Oracle Rdb")

Troels Arvin <troels (AT) arvin (DOT) dk> wrote:

ISTR a similar comment about another Daffodil announcement. What
will the next one announce, that Daffy Dill stores DATA?

Wirchenko brand water is wet. Buy now.

Sincerely,

Gene Wirchenko

Computerese Irregular Verb Conjugation:
I have preferences.
You have biases.
He/She has prejudices.

<<

I'd love to see an article on that topic. Jim Grey was one of the
early people to work with it and InterBase was the first really
sucessful commercial RDBMS. The part I liked was that new data coming
into the system did not slow down queries on the older data -- very
handy for some real-time or near reatime apps like the stock market.

"--CELKO--" <jcelko212 (AT) earthlink (DOT) net> wrote

I think that Interbase was influenced heavily by Starkey's work on DEC Rdb
before Starkey left DEC.

If you look at The Interbase system tables and the RdbVMS system tables in
the mid to late 1980s, you see an enormous overlap, not only in functions,
but in actual names of system tables and columns.

If I got the history right, Starkey had worked on Rdb/ELN before leaving
DEC. ELN was an operating system for the VAX that was an alternative to
VMS. It was based on RSX, but it was lean and mean. Internal versions of
Rdb/ELN, jiggered to work on VMS, were available inside DEC before early
versions of Rdb/VMS, at least in the parts of DEC I know about. Rdb/ELN
was intended to be lean and mean (good for real time data capture) and
compatible with Rdb/VMS at the interface level.

Rdb/ELN and Rdb/VMS both conformed to an internal standard called DSRI
(something like DEC Standard Relational Interface). But internally, they
were quite different, according to the people who know more about that than
I do.

If you are going to call InterBase the first commercially successful RDBMS,
I think you have to qualify that with the trajectory of DEC Rdb. DEC Rdb
was doing fairly well, in a minor way, until DEC itself started going down.
Oracle swallowed DEC Rdb in 1994. I don't know in what year Interbase
became more of a success than DEC Rdb.

I'm curious about your take on all this.