Transaction Management Manage many queries/updates running - PDF document

Transaction Management • Manage many queries/updates running simultaneously. CS 235: – Airline reservations, auctions, ATMs. Introduction to Databases • Atomicity – all or nothing principle. • Serializability – the effect of transactions Svetlozar Nestorov as if they occurred one at a time. Lecture Notes #19 Transaction Control Transactions • Items – units of data to be controlled: • A transaction is a unit of work that must be: 1. Atomic = either all work is done, or none of it. – fine-grained – small items, e.g. tuples. 2. Consistent = relationships among values – coarse-grained – large items, e.g. relations. maintained. • Controlling access by locks. 3. Isolated = appear to have been executed when – Read – sharable with other readers. no other DB operations were being performed. – Write – not sharable with anyone else. – Often called serializable behavior. • Model – (item, locktype, transactionID). 4. Durable = effects are permanent even if system crashes. Commit or Abort Transaction Boundaries • In the ad-hoc query interface ( e.g ., mysql client), every • Each transaction ends with either: query or modification statement is a transaction. 1. Commit = the work of the transaction is installed • You can disable this mode by: in the database; previously its changes may be SET AUTOCOMMIT = 0; invisible to other transactions. – A COMMIT or ROLLBACK ends the previous transaction and starts a new one. Exiting mysql forces implicit COMMIT. 2. Abort = no changes by the transaction appear in • You can also start transaction explicitly: the database; it is as if the transaction never START TRANSACTION; occurred. • Transactions work with InnoDB tables but not with – ROLLBACK is the term used in SQL and MySQL. MyISAM tables. 1

Example Problem With Rollback • Spoon sells Bud for $2.50 and Miller for $3.00. • Suppose Spoon inserts Heineken, but then, • Sally is querying the database for the highest during the transaction thinks better of it and and lowest price Spoon charges: issues a ROLLBACK statement. • At the same time, Spoon has decided to replace • If Sally is allowed to execute finding the max Miller and Bud by Heineken at $3.50: price just before the rollback, she gets the • Can Sally find that the cheapest beer sold at answer $3.50, even though Spoon doesn't sell Spoon is more expensive than the most any beer for $3.50. expensive one?! • Fix by making the insert a transaction, or part of • Fix the problem by grouping Sally's two a transaction, so its effects cannot be seen by statements into one transaction, e.g ., with one Sally unless there is a COMMIT action. SQL statement. SQL Isolation Levels Serializable Example • Isolation levels determine what a transaction is • The transaction must execute as if at a point in allowed to see. The declaration, valid for one time, where all other transactions occurred transaction, is: either completely before or completely after. SET TRANSACTION ISOLATION LEVEL X ; • Sally's queries are one transaction and Spoon • X can be: updates are another transaction. If Sally's – SERIALIZABLE – READ COMMITTED transaction runs at isolation level – REPEATABLE READ SERIALIZABLE, she would see the Sells relation – READ UNCOMMITTED either before or after the updates ran, but not in • In MySQL (with InnoDB) REPEATABLE READ the middle. is the default. Read-Committed Example Repeatable-Read Example • The transaction can read only committed • If a transaction reads data twice, then what it saw the first time, it will see the second time (it data. may see more the second time). • If transactions are as before, Sally could • If find max is executed before, then it must see see the original Sells for statement 1 and the Bud and Miller tuples when it computes the the completely changed Sells for min, even if it executes after their deletion. But if statement 2. find max executes between the deletion and insertion, then find min may see the Heineken tuple. 2

Read-Uncommitted Example Another Example • No constraint, even on reading data T1 T2 start with A = 5 Read A A on disk A in T1 A in T2 written and then removed by a rollback. Read A 5 5 5 • Sally’s two queries could see Heineken, A:= A + 1 even if Spoon rolled back the transaction. A:= 2* A Write A Write A Locks Example with Locks T1 T1 T2 WLOCK A RLOCK A NO R W Read A WLOCK A NO OK OK OK WLOCK A A:= A+1 UNLOCK A T2 R OK OK bad Write A waits W OK bad bad UNLOCK A granted Read A RLOCK –> UNLOCK can enclose a read A:=2*A WLOCK –> UNLOCK can enclose a write or read Write A UNLOCK A Deadlock! Another Deadlock T1 T2 T1 T2 RLOCK A WLOCK A Read A WLOCK B RLOCK A WLOCK B Read A A:= A+1 wait WLOCK A A:= 2*A UNLOCK A wait deadlock! WLOCK A upgrade lock request UNLOCK B WLOCK A upgrade lock request wait wait UNLOCK B Deadlock! UNLOCK A 3

Deadlock Conditions Authorization in SQL 1. Hold some locks while you wait for others. • File systems identify certain access privileges on files, e.g ., read, write, 2. Circular chain of waiters wait-for graph. execute. 3. No pre-emption. • We can avoid deadlock by doing at least • In partial analogy, SQL identifies six ONE of: access privileges on relations, of which the 1. Get all your locks at once most important are: 2. Apply an ordering to acquiring locks 1. SELECT = the right to query the relation. 3. Allow preemption (for example, use timeout on waits) More Privileges Granting Privileges 2. INSERT: the right to insert tuples into the • You have all possible privileges to the relation – may refer to one attribute, in which relations you create. case the privilege is to specify only one column • You may grant privileges to any user if you of the inserted tuple. have those privileges “with grant option.” � MySQL does not support attribute-level privileges. –You have this option to your own 3. DELETE: the right to delete tuples from the relation. relations. 4. UPDATE: the right to update tuples of the relation – may refer to one attribute. Example Revoking Privileges • Your privileges can be revoked. 1. Here, Sally can query and update Sells, but • Syntax is like granting, but REVOKE ... FROM cannot pass on this power: instead of GRANT ... TO. GRANT SELECT ON Sells, UPDATE ON Sells • Determining whether or not you have a privilege is tricky, involving “grant diagrams” as in text. TO sally; However, the basic principles are: 2. Here, Sally can also pass these privileges to – If you have been given a privilege by several different whom she chooses: people, then all of them have to revoke in order for GRANT SELECT ON Sells, you to lose the privilege. UPDATE ON Sells – Revocation is transitive. if A granted P to B , who then TO sally granted P to C , and then A revokes P from B , it is as if WITH GRANT OPTION; B also revoked P from C . 4