Setting DBMS must allow concurrent access to databases. Database - PDF document

Setting • DBMS must allow concurrent access to databases. Database Usage – Imagine a bank where account information is (and Construction) stored in a database not allowing concurrent access. Then only one person could do a Transactions withdrawal in an ATM machine at the time – anywhere! Authorization • Uncontrolled concurrent access may lead to problems. Running in parallel Example: Imagine a program that does the following: • Assume two people, A and B, both try to book a SELECT * 1. Get a day, a time and a room for the same time, at the same time. FROM ROOMS course from the user in WHERE name NOT IN • Both programs perform the sequence order to schedule a (SELECT room FROM Lectures (get)(list)(book) , in that order. lecture. (get) WHERE weekday = theDay AND hour = theTime); • But we can interleave the blocks of the two 2. List all available rooms at that time, with number of sequences in any way we like! INSERT INTO Lectures VALUES seats, and let the user – Here’s one possible interleaving: (theCourse, thePeriod, choose one. (list) theDay, theTime, chosenRoom); A: (get) (list) (book) 3. Book the chosen room for B: (get) (list) (book) the given course at the What could go wrong? given time. (book) Interleaving DBMS vs OS • An operating system supports concurrent A lists all available A decides to book rooms at time T, VR for her lecture. access, and interaction. which includes VR. – E.g. two users modify the same file. If both time � save their changes, then the changes of one A: (get) (list) (book) get lost. B: (get) (list) (book) • A DBMS must support concurrent access, B lists all available B decides to book but must keep processes from interacting! rooms at time T, VR for his lecture. which includes VR. But now VR is no longer free! 1

Quiz! Serializability Look again at the interleaving: • Two programs are run in serial if one finishes before the other starts. time � • The running of two programs is serializable if the A: (get) (list) (book) effects are the same as if they had been run in B: (get) (list) (book) serial. What can we do to fix it? A: (get) (list) (book) B: (get) (list) (book) The only way that we get the desired behavior is if both A Serializable and B may perform the operations (list)(book) without Not serializable the other doing a (book) in the middle! A: (get) (list)(book) B: (get) (list)(book) Example: Quiz! Assume we perform the following operations to Assume we run the following two programs in parallel, and transfer 100 SEK from account X to account Y. assume the databases contains only the Databases lecture in room VR on Mondays (and all lectures are 2h long): SELECT balance 1. Check account FROM Accounts P 1 : P 2 : balance in account X. WHERE accountID = X; 1. Insert a lecture for the 1. Find the first lecture of the Databases course in room day in room VR on Mondays. 2. Subtract 100 from UPDATE Accounts SET balance = balance - 100 VR at 10 on Mondays. (min) account X. WHERE accountID = X; (ins) 2. Find the last lecture of the 3. Add 100 to account Y. 2. Delete the lecture in the day in room VR on Mondays. UPDATE Accounts Databases course in room (max) SET balance = balance + 100 VR at 13 on Mondays. WHERE accountID = Y; 3. Return the total time that (del) room VR is occupied, ((max+2)-min). (ret) Two things can go wrong: We can have strange interleavings like before. But also, assume the program crashes after executing 1 and 2 – we’ll have lost 100 SEK! …Quiz continued! ACID Transactions • A DBMS is expected to support ”ACID P 1 (ins)(del) What could transactions”, which are P 2 (min)(max)(ret) P 2 return? – Atomic : Either the whole transaction is run, or nothing. • Need to consider possible schedules of the actions that access or update the database: (ins)(del)(min)(max) – Consistent : Database constraints are preserved. P 2 returns 2 (ins)(del)(min)(max) – Isolated : Different transactions may not P 2 returns 2 (ins)(min)(del)(max) interact with each other. P 2 returns 5 (ins)(min)(max)(del) (min)(ins)(del)(max) P 2 returns -1 – Durable : Effects of a transaction are not lost (min)(ins)(max)(del) P 2 returns 2 in case of a system crash. P 2 returns 2 (min)(max)(ins)(del) 2

Atomicity Transactions in SQL • For many programs, we require that ”all or • SQL supports transactions, often behind the scenes. nothing” is executed. – An SQL statement is a transaction. – We say a sequence of actions is executed • E.g. an update of a table can’t be interrupted after half the atomically if it is executed either in entirety, or rows. not at all. • Any triggers, procedures, functions etc. that are started by the statement is part of the same transaction. • The state in the middle is never visible from – In PSM or Embedded SQL, a transaction begins at outside the sequence. the first SQL operation and ends when the program • cf. Greek atom = indivisible. does, or it is explicitly ended by the programmer. • In case of a crash in the middle, any changes that were made up until that point must be undone. Controlling transactions Read-only vs. Read-write • We can explicitly start transactions using the • A transaction that does not modify the database START TRANSACTION statement, and end them is called read-only . using COMMIT or ROLLBACK : – A read-only transaction can never interfere with another transaction (but not the other way around!). – COMMIT causes an SQL transaction to complete successfully. – Any number of read-only transactions can be run concurrently. • Any modifications done by the transaction are now permanent in the database. • A transaction that both reads and modifies the – ROLLBACK causes an SQL transaction to end by database is called read-write . aborting it. – No other transaction may write between the read and • Any modifications to the database must be undone. write. • Rollbacks could be caused implicitly by errors e.g. division by 0. SET TRANSACTION Drawbacks • We can hint the DBMS that a transaction • Serializability and atomicity are necessary, only does reading, by issuing the but come at a cost. statement: – We must retain old data until the transaction commits. SET TRANSACTION READ ONLY; – Other transactions might have to wait for one to complete. – Possibly the DBMS can make use of the • In some cases some interference may be information and optimize scheduling. acceptable, and could speed up the system greatly. 3

Isolation levels Example: Recall the first example of booking rooms: • ANSI SQL standard defines four isolation time � A: (get) (list) (book) levels , which are choices about what kinds B: (get) (list) (book) of interference are allowed between transactions. It could take time for the user to decide which room to choose after getting the list. If we make • Each transaction chooses its own isolation this a serializable transaction, all other users level, deciding how other transactions may would have to wait as well. interfere with it. The worst thing that could happen is that B is told • Isolation level is defined in terms of three to choose another room when he tries to book phenomena that can occur. the room that A just booked. Kinds of interference Dirty read The ANSI SQL standard describes: • Transaction T1 modifies a data item. • Another transaction T2 then reads that • Dirty read data item before T1 performs a COMMIT • Non-repeatable read or ROLLBACK. • Phantom • If T1 then performs a ROLLBACK, T2 has read a data item that was never committed (These, and other kinds of interference, are discussed in: and so never really existed. Berenson, H., Bernstein, P., Gray, J., Melton, J., O'Neil, E., & O'Neil, P. (1995). A critique of ANSI SQL isolation levels. ACM SIGMOD Record, 24(2), 1-10.) Non-repeatable read Phantom • Transaction T1 reads a data item. • Transaction T1 reads a set of data items satisfying some <search condition>. • Another transaction T2 then modifies or deletes that data item and commits. • Transaction T2 then creates data items that satisfy T1’s <search condition> and • If T 1 then attempts to re-read the data commits. item, it receives a modified value or discovers that the data item has been • If T1 then repeats its read with the same deleted. <search condition>, it gets a set of data items different from the first read. 4