transactions - SQL Continues Database Modifications...

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

View Full Document Right Arrow Icon

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

View Full DocumentRight Arrow Icon

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

View Full DocumentRight Arrow Icon

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

View Full DocumentRight Arrow Icon

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

View Full DocumentRight Arrow Icon

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

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

Unformatted text preview: SQL Continues Database Modifications Transaction Processing Slides are reused by the approval of Jeffrey Ullman’s 1 Our Running Example x All our SQL queries will be based on the following database schema.  Underline indicates key attributes. Beers(name, manf) Bars(name, addr, license) Drinkers(name, addr, phone) Likes(drinker, beer) Sells(bar, beer, price) Frequents(drinker, bar) 2 Database Modifications x A modification command does not return a result (as a query does), but changes the database in some way. x Three kinds of modifications: 1. Insert a tuple or tuples. 2. Delete a tuple or tuples. 3. Update the value(s) of an existing tuple or tuples. 3 Insertion x To insert a single tuple: INSERT INTO <relation> VALUES ( <list of values> ); x Example: add to Likes(drinker, beer) the fact that Sally likes Bud. INSERT INTO Likes VALUES(’Sally’, ’Bud’); 4 Specifying Attributes in INSERT x We may add to the relation name a list of attributes. x Two reasons to do so: 1. We forget the standard order of attributes for the relation. 2. We don’t have values for all attributes, and we want the system to fill in missing components with NULL or a default value. 5 Example: Specifying Attributes x Another way to add the fact that Sally likes Bud to Likes(drinker, beer): INSERT INTO Likes(beer, drinker) VALUES(’Bud’, ’Sally’); 6 Adding Default Values x In a CREATE TABLE statement, we can follow an attribute by DEFAULT and a value. x When an inserted tuple has no value for that attribute, the default will be used. 7 Example: Default Values CREATE TABLE Drinkers ( name CHAR(30) PRIMARY KEY, addr CHAR(50) DEFAULT ’123 Sesame St.’, phone CHAR(16) ); 8 Example: Default Values INSERT INTO Drinkers(name) VALUES(’Sally’); Resulting tuple: name address Sally 123 Sesame St phone NULL 9 Inserting Many Tuples x We may insert the entire result of a query into a relation, using the form: INSERT INTO <relation> ( <subquery> ); 10 Example: Insert a Subquery x Using Frequents(drinker, bar), enter into the new relation PotBuddies(name) all of Sally’s “potential buddies,” i.e., those drinkers who frequent at least one bar that Sally also frequents. 11 The other drinker Solution Pairs of Drinker tuples where the first is for Sally, the second is for someone else, and the bars are the same. INSERT INTO PotBuddies (SELECT d2.drinker FROM Frequents d1, Frequents d2 WHERE d1.drinker = ’Sally’ AND d2.drinker <> ’Sally’ AND d1.bar = d2.bar ); 12 Deletion x To delete tuples satisfying a condition from some relation: DELETE FROM <relation> WHERE <condition>; 13 Example: Deletion x Delete from Likes(drinker, beer) the fact that Sally likes Bud: DELETE FROM Likes WHERE drinker = ’Sally’ AND beer = ’Bud’; 14 Example: Delete all Tuples x Make the relation Likes empty: DELETE FROM Likes; x Note no WHERE clause needed. 15 Example: Delete Some Tuples x Delete from Beers(name, manf) all beers for which there is another beer by the same manufacturer. Beers with the same manufacturer and DELETE FROM Beers b a different name from the name of WHERE EXISTS ( the beer represented SELECT name FROM Beers by tuple b. WHERE manf = b.manf AND name <> b.name); 16 Semantics of Deletion ­­­ (1) x Suppose Anheuser­Busch makes only Bud and Bud Lite. x Suppose we come to the tuple b for Bud first. x The subquery is nonempty, because of the Bud Lite tuple, so we delete Bud. x Now, when b is the tuple for Bud Lite, do we delete that tuple too? 17 Semantics of Deletion ­­­ (2) x Answer: we do delete Bud Lite as well. x The reason is that deletion proceeds in two stages: 1. Mark all tuples for which the WHERE condition is satisfied. 2. Delete the marked tuples. 18 Updates x To change certain attributes in certain tuples of a relation: UPDATE <relation> SET <list of attribute assignments> WHERE <condition on tuples>; 19 Example: Update x Change drinker Fred’s phone number to 555­1212: UPDATE Drinkers SET phone = ’555-1212’ WHERE name = ’Fred’; 20 Example: Update Several Tuples x Make $4 the maximum price for beer: UPDATE Sells SET price = 4.00 WHERE price > 4.00; 21 TRANSACTION PROCESSING 22 Why Transactions? x Database systems are normally being accessed by many users or processes at the same time.  Both queries and modifications. x Unlike operating systems, which support interaction of processes, a DMBS needs to keep processes from troublesome interactions. 23 Concurrency Control T1 T2 … Tn DB (consistency constraints) 24 24 Example: Bad Interaction x You and your domestic partner each take $100 from different ATM’s at about the same time.  The DBMS better make sure one account deduction doesn’t get lost. x Compare: An OS allows two people to edit a document at the same time. If both write, one’s changes get lost. 25 Transactions x Transaction = process involving database queries and/or modification. x Normally with some strong properties regarding concurrency. x Formed in SQL from single statements or explicit programmer control. 26 ACID Transactions x ACID transactions are:  Atomic : Whole transaction or none is done.  Consistent : Database constraints preserved.  Isolated : It appears to the user as if only one process executes at a time.  Durable : Effects of a process survive a crash. x Optional: weaker forms of transactions are often supported as well. 27 COMMIT x The SQL statement COMMIT causes a transaction to complete.  It’s database modifications are now permanent in the database. 28 ROLLBACK x The SQL statement ROLLBACK also causes the transaction to end, but by aborting.  No effects on the database. x Failures like division by 0 or a constraint violation can also cause rollback, even if the programmer does not request it. 29 Example: Interacting Processes x Assume the usual Sells(bar,beer,price) relation, and suppose that Joe’s Bar sells only Bud for $2.50 and Miller for $3.00. x Sally is querying Sells for the highest and lowest price Joe charges. x Joe decides to stop selling Bud and Miller, but to sell only Heineken at $3.50. 30 Sally’s Program x Sally executes the following two SQL statements called (min) and (max) to help us remember what they do. (max) SELECT MAX(price) FROM Sells WHERE bar = ’Joe’’s Bar’; (min) SELECT MIN(price) FROM Sells WHERE bar = ’Joe’’s Bar’; 31 Joe’s Program x At about the same time, Joe executes the following steps: (del) and (ins). (del) DELETE FROM Sells WHERE bar = ’Joe’’s Bar’; (ins) INSERT INTO Sells VALUES(’Joe’’s Bar’, ’Heineken’, 3.50); 32 Interleaving of Statements x Although (max) must come before (min), and (del) must come before (ins), there are no other constraints on the order of these statements, unless we group Sally’s and/or Joe’s statements into transactions. 33 Example: Strange Interleaving x Suppose the steps execute in the order (max)(del)(ins)(min). {3.50} Joe’s Prices: {2.50,3.00} {2.50,3.00} (max) (del) (ins) (min) Statement: 3.00 3.50 Result: x Sally sees MAX < MIN! 34 Fixing the Problem by Using Transactions x If we group Sally’s statements (max) (min) into one transaction, then she cannot see this inconsistency. x She sees Joe’s prices at some fixed time.  Either before or after he changes prices, or in the middle, but the MAX and MIN are computed from the same prices. 35 Another Problem: Rollback x Suppose Joe executes (del)(ins), not as a transaction, but after executing these statements, thinks better of it and issues a ROLLBACK statement. x If Sally executes her statements after (ins) but before the rollback, she sees a value, 3.50, that never existed in the database. 36 Solution x If Joe executes (del)(ins) as a transaction, its effect cannot be seen by others until the transaction executes COMMIT.  If the transaction executes ROLLBACK instead, then its effects can never be seen. 37 Isolation Levels x SQL defines four isolation levels = choices about what interactions are allowed by transactions that execute at about the same time. x Only one level (“serializable”) = ACID transactions. x Each DBMS implements transactions in its own way. 38 Choosing the Isolation Level x Within a transaction, we can say: SET TRANSACTION ISOLATION LEVEL X where X = 1. 2. 3. 4. SERIALIZABLE REPEATABLE READ READ COMMITTED READ UNCOMMITTED 39 Serializable Transactions x If Sally = (max)(min) and Joe = (del) (ins) are each transactions, and Sally runs with isolation level SERIALIZABLE, then she will see the database either before or after Joe runs, but not in the middle. 40 Isolation Level Is Personal Choice x Your choice, e.g., run serializable, affects only how you see the database, not how others see it. x Example: If Joe Runs serializable, but Sally doesn’t, then Sally might see no prices for Joe’s Bar.  i.e., it looks to Sally as if she ran in the middle of Joe’s transaction. 41 Read­Commited Transactions x If Sally runs with isolation level READ COMMITTED, then she can see only committed data, but not necessarily the same data each time. x Example: Under READ COMMITTED, the interleaving (max)(del)(ins)(min) is allowed, as long as Joe commits.  Sally sees MAX < MIN. 42 Repeatable­Read Transactions x Requirement is like read­committed, plus: if data is read again, then everything seen the first time will be seen the second time.  But the second and subsequent reads may see more tuples as well. 43 Example: Repeatable Read x Suppose Sally runs under REPEATABLE READ, and the order of execution is (max)(del)(ins)(min).  (max) sees prices 2.50 and 3.00.  (min) can see 3.50, but must also see 2.50 and 3.00, because they were seen on the earlier read by (max). 44 Read Uncommitted x A transaction running under READ UNCOMMITTED can see data in the database, even if it was written by a transaction that has not committed (and may never). x Example: If Sally runs under READ UNCOMMITTED, she could see a price 3.50 even if Joe later aborts. 45 ...
View Full Document

Page1 / 45

transactions - SQL Continues Database Modifications...

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

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