CSL 451 Introduction to Database Systems
partially
commi!ed
commi!ed
failed
aborted
active
Transactions
Department of Computer Science and Engineering
Indian Institute of Technology Ropar
Narayanan (CK) Chatapuram Krishnan!
Summary
•  Transaction
– 
– 
– 
– 
•  Testing for Serializability
Atomicity
Consistency
Isolation
Durability
–  Precedence graph
•  acyclic
–  topological sorting
•  Transaction states
–  active, partially committed, failed,
committed, aborted
•  Concurrent executions
–  why?
•  Schedules
–  serial
–  serializable
•  Serializability
–  conflict
•  conflict equivalent
•  conflict serializable
17/04/15!
Department of Computer Science and Engineering !
Indian Institute of Technology Ropar!
2!
Testing for Serializability
■  Consider some schedule of a set of transactions T1, T2, ..., Tn
■  Precedence graph — a directed graph where the vertices
are the transactions (names).
■  We 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.
■  Example 1
T1
Database System Concepts - 6th Edition
T2
14.3
©Silberschatz, Korth and Sudarshan
Test for Conflict Serializability
■  A schedule is conflict serializable if and only
Ti
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.
● 
Tm
(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.
Tk
Tj
(a)
Ti
Ti
● 
This is a linear order consistent with the
partial order of the graph.
Tj
Tk
● 
For example, a serializability order for
Schedule A would be T5 → T1 → T3 → T2 → T4
Tk
Tj
Tm
Tm
(b)
(c)
Database System Concepts - 6th Edition
14.4
©Silberschatz, Korth and Sudarshan
Summary
•  Testing for Serializability
•  Transaction
– 
– 
– 
– 
–  Precedence graph
Atomicity
Consistency
Isolation
Durability
•  acyclic
–  topological sorting
•  Transaction Isolation and
Atomicity
•  Transaction states
–  active, partially committed, failed,
committed, aborted
•  Concurrent executions
–  why?
•  Schedules
–  serial
–  serializable
•  Serializability
–  conflict
–  Partial schedule
–  Recoverable Schedules
–  Cascadeless Schedules
•  cascading rollback
•  Transaction Isolation levels
– 
– 
– 
– 
Serializable
Repeatable read
Read committed
Read uncommitted
•  Concurrency control policies
–  locking
–  snapshot isolation
•  conflict equivalent
•  conflict serializable
17/04/15!
Department of Computer Science and Engineering !
Indian Institute of Technology Ropar!
5!
14.1 Suppose that there is a database system that never fails. Is
a recovery manager required for this system?
17/04/15!
Department of Computer Science and Engineering !
Indian Institute of Technology Ropar!
6!
14.4 Justify the following statement: Concurrent execution of
transactions is more important when data must be fetched from
(slow) disk or when transactions are long, and is less important
when data are in memory and transactions are very short.
17/04/15!
Department of Computer Science and Engineering !
Indian Institute of Technology Ropar!
7!
14.14 Explain the distinction between the terms serial schedule
and serializable schedule.
17/04/15!
Department of Computer Science and Engineering !
Indian Institute of Technology Ropar!
8!
14.15 Consider the following two transactions:
T13: read(A);
read(B);
if A=0 then B:=B+1;
write(B);
T14: read(B);
read(A);
if B:=0 then A:=A+1;
write(A);
Let the consistency requirement be A=0 V B=0, with A=B=0 the
initial values
a. Show that every serial execution involving these two
transactions preserves the consistency of the database.
17/04/15!
Department of Computer Science and Engineering !
Indian Institute of Technology Ropar!
9!
14.15 Consider the following two transactions:
T13: read(A);
read(B);
if A=0 then B:=B+1;
write(B);
T14: read(B);
read(A);
if B:=0 then A:=A+1;
write(A);
Let the consistency requirement be A=0 V B=0, with A=B=0 the
initial values
b. Show a concurrent execution of the transactions that
produces a nonserializable schedule.
17/04/15!
Department of Computer Science and Engineering !
Indian Institute of Technology Ropar!
10!
14.15 Consider the following two transactions:
T13: read(A);
read(B);
if A=0 then B:=B+1;
write(B);
T14: read(B);
read(A);
if B:=0 then A:=A+1;
write(A);
Let the consistency requirement be A=0 V B=0, with A=B=0 the
initial values
c. Is there a concurrent execution of the transactions that
produces a serializable schedule?
17/04/15!
Department of Computer Science and Engineering !
Indian Institute of Technology Ropar!
11!
14.16 Give an example of a serializable schedule with two
transactions such that the order in which the transactions
commit is different from the serialization order.
17/04/15!
Department of Computer Science and Engineering !
Indian Institute of Technology Ropar!
12!
14.6 Consider the precedence graph. Is the corresponding
schedule conflict serializable?
17/04/15!
Department of Computer Science and Engineering !
Indian Institute of Technology Ropar!
13!
14.20 Give an example of a schedule that respects the specified
level of isolation, but is not serializable.
a. Read uncommitted
17/04/15!
Department of Computer Science and Engineering !
Indian Institute of Technology Ropar!
14!
14.17 What is a recoverable schedule? Why is recoverability of
schedules desirable? Are there any circumstances under which
it would be desirable to allow nonrecoverable schedules?
17/04/15!
Department of Computer Science and Engineering !
Indian Institute of Technology Ropar!
15!
14.8 The lost update anomaly is said to occur if a transaction Tj
reads a data item, then another transaction Tk writes the data
item. The update performed by Tk had been lost, since the
update done by Tj ignored the value written by Tk.
a. Give an example of a schedule showing the lost update
anomaly.
17/04/15!
Department of Computer Science and Engineering !
Indian Institute of Technology Ropar!
16!
14.8 The lost update anomaly is said to occur if a transaction Tj
reads a data item, then another transactions Tk writes the data
item. The update performed by Tk had been lost, since the
update done by Tj ignored the value written by Tk.
c. Explain why the lost update anomaly is not possible with the
repeatable read isolation level.
17/04/15!
Department of Computer Science and Engineering !
Indian Institute of Technology Ropar!
17!
14.9 Consider a database for a bank where the database
system uses snapshot isolation. Describe a particular scenario
in which a nonserializable execution occurs that would present a
problem for the bank.
17/04/15!
Department of Computer Science and Engineering !
Indian Institute of Technology Ropar!
18!