slides13 - Schedule Today: Feb. 21 (TH) x x Transactions,...

Info iconThis preview shows pages 1–6. Sign up to view the full content.

View Full Document Right Arrow Icon
Winter 2002 Arthur Keller – CS 180 13–1 Schedule Today: Feb. 21 (TH) Transactions, Authorization. Read Sections 8.6-8.7. Project Part 5 due. Feb. 26 (T) Datalog. Read Sections 10.1-10.2. Assignment 6 due. Feb. 28 (TH) Datalog and SQL Recursion, ODL. Read Sections 10.3-10.4, 4.1-4.4. Project Part 6 due. Mar. 5 (T) More ODL, OQL. Read Sections 9.1. Assignment 7 due.
Background image of page 1

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
Winter 2002 Arthur Keller – CS 180 13–2 TRANSACTION MANAGEMENT Airline Reservations many updates Statistical Abstract of the US many queries Atomicity – all or nothing principle Serializability – the effect of transactions as if they occurred one at a time Items – units of data to be controlled fine-grained – small items course-grained – large items (granularity) Controlling access by locks Read – sharable with other readers shared Write – not sharable with anyone else exclusive Model – (item, locktype, transaction ID)
Background image of page 2
Winter 2002 Arthur Keller – CS 180 13–3 Transactions = units of work that must be: 1. Atomic = either all work is done, or none of it. 2. Consistent = relationships among values maintained. 3. Isolated = appear to have been executed when no other DB operations were being performed. Often called serializable behavior. 1. Durable = effects are permanent even if system crashes.
Background image of page 3

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
Winter 2002 Arthur Keller – CS 180 13–4 Commit/Abort Decision Each transaction ends with either: 1. Commit = the work of the transaction is installed in the database; previously its changes may be invisible to other transactions. 2. Abort = no changes by the transaction appear in the database; it is as if the transaction never occurred. ROLLBACK is the term used in SQL and the Oracle system. In the ad-hoc query interface ( e.g ., PostgreSQL psql interface), transactions are single queries or modification statements. Oracle allows SET TRANSACTION READ ONLY to begin a multistatement transaction that doesn't change any data, but needs to see a consistent “snapshot” of the data. In program interfaces, transactions begin whenever the database is accessed, and end when either a COMMIT or ROLLBACK statement is executed.
Background image of page 4
Arthur Keller – CS 180 13–5 Example Sells(bar , beer , price) Joe's Bar sells Bud for $2.50 and Miller for $3.00. Sally is querying the database for the highest and lowest price Joe charges: (1) SELECT MAX(price) FROM Sells WHERE bar = 'Joe''s Bar'; (2) SELECT MIN(price) FROM Sells WHERE bar = 'Joe''s Bar'; At the same time, Joe has decided to replace Miller and Bud by Heineken at $3.50: (3) DELETE FROM Sells WHERE bar = 'Joe''s Bar' AND (beer = 'Miller' OR beer = 'Bud'); (4) INSERT INTO Sells VALUES('Joe''s bar', 'Heineken', 3.50); If the order of statements is 1, 3, 4, 2, then it appears to Sally that Joe’s minimum price is greater than his maximum price. Fix the problem by grouping Sally’s two statements into one transaction,
Background image of page 5

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
Image of page 6
This is the end of the preview. Sign up to access the rest of the document.

Page1 / 36

slides13 - Schedule Today: Feb. 21 (TH) x x Transactions,...

This preview shows document pages 1 - 6. Sign up to view the full document.

View Full Document Right Arrow Icon
Ask a homework question - tutors are online