This Lecture • Concurrency control • Serialisability Concurrency • Schedules of transactions • Serial and serialisable schedules • Locks Database Systems • 2 Phase Locking Protocol • Further reading Michael Pound • The Manga Guide to Databases, Chapter 5 • Database Systems, Chapter 22 Transactions so Far Transactions so Far • Atomicity • Isolation • Transactions are the • COMMIT • Transactions • Incomplete transactions ‘logical unit of work’ in • Signals the successful conceptually have no are invisible to others a database end of a transaction component parts until they have • Changes are made • ACID properties committed • The run completely, or permanent and visible to • Also the unit of recovery not at all • Durability other transactions • Transactions will involve • Consistency • Committed transactions • ROLLBACK • Transactions take the must be made some read and/or permanent • Signals the unsuccessful database from one writes on a database end of a transaction consistent state to another • Changes are undone Concurrency Concurrency Problems • If we don’t allow for • Large databases are • In order to run two or • This can lead to several concurrency then used by many people more concurrent problems transactions are run transactions, their • Many transactions are to • Lost updates sequentially be run on the database operations must be • Uncommitted updates • Have a queue of • It is helpful to run these interleaved • Incorrect updates transactions simultaneously • Each transaction gets a • All arise when isolation • Easy to preserve • Still need to preserve atomicity and isolation share of the computing is broken isolation • Long transactions (e.g. time backups) will delay others 1
Lost Update Uncommitted Update • T1 and T2 both read X, • T2 sees the change to X T1 T2 T1 T2 both modify it, then made by T1, but T1 is Read(X) Read(X) both write it out then rolled back X = X - 5 X = X - 5 • The net effect of both • The change made by T1 Read(X) Write(X) transactions should be is rolled back X = X + 5 Read(X) no change to X • It should be as if that Write(X) X = X + 5 • Only T2’s change is seen change never happened Write(X) Write(X) COMMIT however ROLLBACK COMMIT COMMIT Inconsistent Analysis Concurrency Control • T1 doesn’t change the • Concurrency control is the process of T1 T2 sum of X and Y, but T2 managing simultaneous operations on the Read(X) records a change database without having them interfere with X = X - 5 • T1 consists of two parts - Write(X) each other take 5 from X then add 5 Read(X) • Possibly reading and writing the same data to Y Read(Y) • T2 sees the effect of the • Long transactions must not hold up others Sum = X + Y first change, but not the Read(Y) • ACID properties must be maintained second Y = Y + 5 Write(Y) Schedules Example Schedule • A schedule is a sequence of the operations in a • Three transactions: • Example schedule set of concurrent transactions that preserves T1 the order of operations in each of the Read(X) individual transactions Read(Y) Write(X) T1 Read(X) • A serial schedule is a schedule where the T2 Read(Y) T2 T2 Read(Z) operations of each transaction are executed Read(Y) T3 Read(Z) Read(Z) T1 Read(Y) consecutively without any interleaved Write(Y) T1 Write(X) operations from other transactions (each must T3 Write(Z) T3 T2 Write(Y) commit before the next can begin) Read(Z) Write(Z) 2
Example Schedule Serial Schedules • Three transactions: • Example serial schedule • A serial schedule is guaranteed to avoid interference between transactions, and T1 Read(X) preserve database consistency Read(Y) T1 Read(X) • However, this approach does not allow for Write(X) T1 Read(Y) T2 T1 Write(X) concurrent access, i.e. Interleaving operations Read(Y) T2 Read(Y) Read(Z) from multiple transactions T2 Read(Z) Write(Y) T2 Write(Y) T3 Read(Z) T3 T3 Write(Z) Read(Z) Write(Z) Serialisability Serial and Serialisable • Interleaved (nonserial) • Serial Schedule • Two schedules are equivalent if they always have Schedule the same effect • A schedule is serialisable if it is equivalent to T1 Read(X) T2 Read(X) T2 Read(X) some serial schedule T2 Read(Y) T2 Read(Y) T2 Read(Z) • For example: T1 Read(Z) • If two transactions only read from some data items, T1 Read(Y) T1 Read(X) the order in which they do this is not important T2 Read(Z) T1 Read(Z) • If T1 reads and then updates X, and T2 reads then T1 Read(Y) This schedule is serialisable – has the updates Y, then again this can occur in any order same effect as a serial schedule Conflict Serialisability Conflict Serialisable Schedule • • Interleaved Schedule Serial Schedule • Two transactions have a • A schedule is conflict confict: serialisable if the T1 Read(X) T1 Read(X) transactions in the • NO If they refer to T1 Write(X) T1 Write(X) T2 Read(X) T1 Read(Y) different resources schedule have a T2 Write(X) T1 Write(Y) • NO If they only read conflict, but the T1 Read(Y) • YES If at least one is a schedule is still T1 Write(Y) T2 Read(X) write and they use the serialisable T2 Read(Y) T2 Write(X) same resource T2 Write(Y) T2 Read(Y) T2 Write(Y) This schedule is serialisable, even though T1 and T2 read and write the same resources X and Y: They have a conflict 3
Conflict Serialisability Graphs • Conflict serialisable • Important questions • In mathematics, a graph is a structure (V,E) of schedules are the main • How do we determine Vertices and Edges. In the case of a directed focus of concurrency whether or not a graph, these edges include directions schedule is conflict control • For example: serialisable? • They allow for • How do we construct B interleaving and at the conflict serialisable same time they are schedules guaranteed to behave A D as a serial schedule C Precedence Graphs Precedent Graph Example • To determine if a • Edge T1 → T2 if in the • No cycles: conflict T1 T2 schedule is conflict schedule we have: serialisable schedule serialisable we use a • T1 Read(R) followed by precedence graph T2 Write(R) Read(X) • T1 reads X before T2 writes X • T1 Write(R) followed by • Transactions are vertices Write(X) • T1 writes X before T2 reads X T2 Read(R) of the graph Read(X) • T1 writes X before T2 writes X • T1 Write(R) followed by • There is an edge from T1 Write(X) to T2 if T1 must happen T2 Write(R) before T2 in any • The schedule is equivalent serialisable serialisable if there are T1 T2 schedule no cycles Precedent Graph Example Locking • The lost update • Locking is a procedure used to control T1 T2 problem has this concurrent access to data (to ensure precedence graph: Read(X) serialisability of concurrent transactions) X = X - 5 • In order to use a ‘resource’ a transaction must • Read(X) T1 reads X before T2 writes X first acquire a lock on that resource • T1 writes X before T2 writes X X = X + 5 Write(X) • A resource could be a single item of data, some or Write(X) all of table, or even a whole database T1 T2 COMMIT • This may deny access to other transactions to COMMIT prevent incorrect results • T2 reads X before T1 writes X 4
Lock Types Locking • There are two types of lock • Before reading from a resource a transaction • Shared lock (often called a read lock) must acquire a read-lock • Exclusive lock (often called a write lock) • Before writing to a resource a transaction • Read locks allow several transactions to read must acquire a write-lock • A lock might be released during execution data simultaneously, but none can write to that data when no longer needed, or upon COMMIT or • Write locks allow a single transaction exclusive ROLLBACK access to read and write a resource Locking Locking Example • A transaction may not acquire a lock on any • Locking won’t successfully allow us to serialise resource that is currently write-locked by all schedules. For example: another transaction T1 T2 • A transaction may not acquire a write lock on write-lock(X) Read(X) any resource that is currently locked by X = X - 500 Write(X) write-lock(X) unlock(X) another transaction Read(X) write-lock(Y) Read(Y) • If the requested lock is not available, the X = X * 1.1 Y = Y * 1.1 transaction waits Write(X) Write(Y) unlock(X) • The DBMS is responsible for issuing locks write-lock(Y) Read(Y) unlock(Y) Y = Y + 500 Write(Y) unlock(Y) Two-Phase Locking Two-Phase Locking Example • T1 follows 2PL protocol • A transaction follows • Two phases: T1 T2 • All locks in T1 are two-phase locking • Growing phase where write-lock(X) write-lock(X) acquired before any are protocol (2PL) if all locks are acquired Read(X) Read(X) released • Shrinking phase where X = X + 100 X = X + 100 locking operations • This happens even if the Write(X) Write(X) locks are released precede all unlocking resource is no longer write-lock(Y) unlock(X) operations used unlock(X) write-lock(Y) Read(Y) Read(Y) • T2 does not • Other operations can Y = Y – 100 Y = Y – 100 • Releases a lock on X, Write(Y) Write(Y) happen at any time which is no longer unlock(Y) unlock(Y) throughout the needed, before acquiring transaction COMMIT COMMIT on Y 5
Recommend
More recommend
