Data
manipulation language(DML) may be includes a construct for specifying the set of
actions that comprise a transaction.In
SQL, a transaction begins implicitly ends.A
transaction in SQL ends by:
Commit
work
commits current transaction and begins a new one.
Rollback
work
causes current transaction to abort.
Levels
of consistency specified by SQL-92:
Serializable — default
Repeatable
read
Read
committed
Read
uncommitted
Serializable
— default
Repeatable
read — only
committed records to be read, repeated reads of same record must return same
value. However, a transaction may not be
serializable
– it may find some records inserted by a transaction but not find others.
Read
committed — only
committed records can be read, but successive reads of record may return
different (but committed) values.
Read
uncommitted — even
uncommitted records may be read.
Consider
some schedule of a set of transactions T1, T2, ..., Tn
Precedence
graph — a direct graph where the vertices
are the transactions.
We can draw an arc from Ti to
Tj if
the two transaction conflict, and Ti accessed the data item on which the
conflict arose earlier.We
may label the arc by the item that was accessed.
A
schedule is also conflict serializable
if and only if its precedence graph is acyclic.
Cycle-detection
algorithms exist which take order n2 time, where n is the number of vertices in the
graph. (Better algorithms take order n + e
where e is the number of edges.)
If
precedence graph is acyclic, the serializability
order can be obtained by a topological
sorting of the graph. This is a linear order consistent with the
partial order of the graph.
For example, a serializability order for Schedule A would be
T5 ® T1 ® T3 ® T2 ® T4 .
For example, a serializability order for Schedule A would be
T5 ® T1 ® T3 ® T2 ® T4 .
