Setting • DBMS must allow concurrent access to databases. – Imagine a bank where account information is Database Transactions stored in a database not allowing concurrent access. Then only one person could do a withdrawal in an ATM machine at the time – anywhere! • 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 given time. (book) Interleaving Serializability • Two programs are run in serial if one finishes A lists all available A decides to book rooms at time T, before the other starts. VR for her lecture. which includes VR. • The running of two programs is serializable if the time → effects are the same as if they had been run in A: (get) (list) (book) serial. B: (get) (list) (book) A: (get) (list) (book) B: (get) (list) (book) B lists all available B decides to book rooms at time T, VR for his lecture. Serializable which includes VR. But now VR is no Not serializable longer free! A: (get) (list)(book) B: (get) (list)(book) 1
Example: Atomicity Assume we perform the following operations to transfer 100 SEK from account X to account Y. • For many programs, we require that ”all or SELECT balance nothing” is executed. 1. Check account FROM Accounts WHERE accountID = X; balance in account X. – We say a sequence of actions is executed atomically if it is executed either in entirety, or 2. Subtract 100 from UPDATE Accounts SET balance = balance - 100 not at all. account X. WHERE accountID = X; • The state in the middle is never visible from 3. Add 100 to account Y. UPDATE Accounts outside the sequence. SET balance = balance + 100 • cf. Greek atom = indivisible. WHERE accountID = Y; • In case of a crash in the middle, any changes that Two things can go wrong: We can have strange interleavings like were made up until that point must be undone. before. But also, assume the program crashes after executing 1 and 2 – we’ll have lost 100 SEK! ACID Transactions Transactions in SQL • A DBMS is expected to support ”ACID • SQL supports transactions, often behind the transactions”, which are scenes. – Atomic : Either the whole transaction is run, or – An SQL statement is a transaction. nothing. • E.g. an update of a table can’t be interrupted after half the rows. – Consistent : Database constraints are • Any triggers, procedures, functions etc. that are started by preserved. the statement is part of the same transaction. – Isolated : Different transactions may not interact with each other. – Durable : Effects of a transaction are not lost in case of a system crash. Controlling transactions Read-only vs. Read-write • We can explicitly start transactions using the • A transaction that does not modify the database START TRANSACTION or BEGIN statement, and is called read-only . end them 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 or ABORT causes an SQL transaction to end database is called read-write . by 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. 2
SET TRANSACTION Drawbacks • We can hint the DBMS that a transaction • Serializability and atomicity are necessary, only does reading, by issuing the but don’t come without a cost. statement: – We must retain old data until the transaction commits. SET TRANSACTION READ ONLY; – Other transactions may need 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. 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 example The ANSI SQL standard describes: T2 performs dirty read of uncommitted value a b c • Dirty read T1 T2 B := 19 19 B? 19! • Non-repeatable read a b c …woops... • Phantom a b c B := 42 42 COMMIT (These, and other kinds of interference, are discussed in: 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.) 3
Non-repeatable read example Phantom example a b c T1 Data? T2 a b c B := 19 19 T2 commit ... Add 2 rows Rows have changed and commit T1 B? 19! a b c phantom rows introduced during T1 19 != 42 T3 a b c ... B changed during T1 B := 42 42 due to non-repeatable data? commit read B? 42! Isolation levels - differences Choosing isolation level • Within a transaction we can choose the What kinds of interference are possible? isolation level: SET TRANSACTION ISOLATION LEVEL X; Dirty reads Non-repeatable Phantoms reads where X is one of READ Yes Yes Yes UNCOMMITTED • SERIALIZABLE Increasing READ No Yes Yes COMMITTED Isolation • READ COMMITTED strictness REPEATABLE No No Yes • READ UNCOMMITTED READ SERIALIZABLE No No No • REPEATABLE READ Authorization • Not every user can be allowed to do everything. – Some data are secret and may only be seen Database Authorization by some users. – Some data are high integrity and may only be modified by certain users. 4
Privileges on relations Privileges on relations • SELECT ( attributes ) ON table • DELETE ON table – Allows the user to select data from the specified table. – Allows the user to delete tuples from the – Can be parametrized on attributes, meaning the user table. may only see certain attributes of the table. – Cannot be parametrized on attributes. • INSERT ( attributes ) ON table • UPDATE ( attributes ) ON table – Allows the user to insert tuples into the table. – Allows the user to update data in the table. – Can be parametrized on attributes, meaning the user may only supply values for certain attributes of the – Parametrizing means the user may only table. Other attributes are then set to NULL. update values of certain attributes. Quiz! Other privileges What privileges are needed to perform the • REFERENCES ( attributes ) ON table following insertion? – Allows the user to create a foreign reference to (attributes of) that table. INSERT INTO Lectures(course, period, weekday) • TRIGGER ON table SELECT course, period, ’Monday’ FROM GivenCourses G – Allows the user to create triggers for events on that WHERE NOT EXISTS table. (SELECT course, period FROM Lectures L • EXECUTE ON procedure WHERE L.course = G.course AND L.period = G.period – Allows the user to execute the procedure or function, AND weekday = ’Monday’); and use it in declarations. • USAGE, UNDER, TRUNCATE, CREATE, ALL, We need privileges INSERT on Lectures(course, period, … weekday) , SELECT on GivenCourses(course, period) , and SELECT on Lectures(course, period, weekday) . Granting privileges GRANT statement • You have all possible privileges on • Granting a privilege in SQL: elements that you have created. GRANT list of privileges • You may grant privileges to other users on ON element those elements. TO list of authorization Ids; – A user is referred to by an authorization ID , which is typically a user name. – Example: – There is a special authorization ID, public GRANT SELECT(course, period, teacher) – Granting a privilege to public makes it ON GivenCourses available to all users. TO public; 5
Recommend
More recommend
