StudyMaterials

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 .