Concurrency Control 18.1 – 18.2

Concurrency Control18.1 – 18.2 Chiu Luk CS257 Database Systems Principles Spring 2009

Concurrency Control • Concurrency control in database management systems (DBMS) ensures that database transactions are performed concurrently without the concurrency violating the data integrity of a database. • Executed transactions should follow the ACID rules. The DBMS must guarantee that only serializable (unless Serializability is intentionally relaxed), recoverable schedules are generated. • It also guarantees that no effect of committed transactions is lost, and no effect of aborted (rolled back) transactions remains in the related database.

Transaction ACID rules Atomicity - Either the effects of all or none of its operations remain when a transaction is completed - in other words, to the outside world the transaction appears to be indivisible, atomic. Consistency - Every transaction must leave the database in a consistent state. Isolation - Transactions cannot interfere with each other. Providing isolation is the main goal of concurrency control. Durability - Successful transactions must persist through crashes.

Serial and Serializable Schedules • In the field of databases, a schedule is a list of actions, (i.e. reading, writing, aborting, committing), from a set of transactions. • In this example, Schedule D is the set of 3 transactions T1, T2, T3. The schedule describes the actions of the transactions as seen by the DBMS. T1 Reads and writes to object X, and then T2 Reads and writes to object Y, and finally T3 Reads and writes to object Z. This is an example of a serial schedule, because the actions of the 3 transactions are not interleaved.

Serial and Serializable Schedules • A schedule that is equivalent to a serial schedule has the serializability property. • In schedule E, the order in which the actions of the transactions are executed is not the same as in D, but in the end, E gives the same result as D.

A B 25 25 125 125 250 250 250 250 Serial Schedule TI precedes T2 T1 T2 Read(A); A  A+100 Write(A); Read(B); B  B+100; Write(B); Read(A);A  A2; Write(A); Read(B);B  B2; Write(B);

A B 25 25 50 50 150 150 150 150 Serial Schedule T2 precedes Tl T1 T2 Read(A);A  A2; Write(A); Read(B);B  B2; Write(B); Read(A); A  A+100 Write(A); Read(B); B  B+100; Write(B);

A B 25 25 125 250 125 250 250 250 serializable, but not serial, schedule T1 T2 Read(A); A  A+100 Write(A); Read(A);A  A2; Write(A); Read(B); B  B+100; Write(B); Read(B);B  B2; Write(B); r1(A); w1 (A): r2(A); w2(A); r1 (B); w1 (B); r2(B); w2(B);

A B 25 25 125 250 50 150 250 150 nonserializable schedule T1 T2 Read(A); A  A+100 Write(A); Read(A);A  A2; Write(A); Read(B);B  B2; Write(B); Read(B); B  B+100; Write(B);

A B 25 25 125 125 25 125 125 125 schedule that is serializable only because of the detailed behavior of the transactions T1 T2’ Read(A); A  A+100 Write(A); Read(A);A  A1; Write(A); Read(B);B  B1; Write(B); Read(B); B  B+100; Write(B); • regardless of the consistent initial state: the final state will be consistent.

Non-Conflicting Actions Two actions are non-conflicting if whenever theyoccur consecutively in a schedule, swapping themdoes not affect the final state produced by theschedule. Otherwise, they are conflicting.

Conflicting Actions: General Rules • Two actions of the same transaction conflict: • r1(A) w1(B) • Two actions over the same database element conflict, if one of them is a write • r1(A) w2(A) • w1(A) w2(A)

Conflict actions • Two or more actions are said to be in conflict if: • The actions belong to different transactions. • At least one of the actions is a write operation. • The actions access the same object (read or write). • The following set of actions is conflicting: • T1:R(X), T2:W(X), T3:W(X) • While the following sets of actions are not: • T1:R(X), T2:R(X), T3:R(X) • T1:R(X), T2:W(Y), T3:R(X)

Conflict Serializable • We may take any schedule and make as many nonconflicting swaps as we wish. • With the goal of turning the schedule into a serial schedule. • If we can do so, then the original schedule is serializable, because its effect on the database state remains the same as we perform each of the nonconflicting • swaps.

Conflict Serializable • A schedule is said to be conflict-serializable when the schedule is conflict-equivalent to one or more serial schedules. • Another definition for conflict-serializability is that a schedule is conflict-serializable if and only if there exists an acyclic precedence graph/serializability graph for the schedule. • Which is conflict-equivalent to the serial schedule <T1,T2>, but not <T2,T1>.

Conflict equivalent / conflict-serializable • Let Ai and Aj are consecutive non-conflicting actions that belongs to different transactions. We can swap Ai and Aj without changing the result. • Two schedules are conflict equivalent if they can be turned one into the other by a sequence of non-conflicting swaps of adjacent actions. • We shall call a schedule conflict-serializable if it is conflict-equivalent to a serial schedule.

conflict-serializable

conflict-serializable Serial Schedule

END of present

References • Database Systems: The Complete Book (2nd Edition) (Hardcover)by Hector Garcia-Molina (Author), Jeffrey D. Ullman (Author), Jennifer Widom (Author) Publisher : Prenctice Hall. • http://en.wikipedia.org/wiki/Concurrency_control • http://www.utdallas.edu/~mxk055100/db07files/serilizable-defs.ppt • http://en.wikipedia.org/wiki/Schedule_(computer_science)#Serializable • http://www.cs.duke.edu/~shivnath/courses/fall06/Lectures/11_serial.ppt

Concurrency Control 18.1 – 18.2