Lecture #19AgendaTriggersElements of Triggers (in SQL3)Example: Row Level TriggerStatement Level TriggerBad Things Can HappenA Naïve Database SystemA Naïve DBMSWhat is Wrong with the Naïve DBMSSlide 11What Should a DBMS Do?Generic ArchitectureQuery OptimizationAlternate PlansACID PropertiesProblems with Transaction ProcessingThe Memory HierarchyMain MemorySecondary StorageBuffer Management in a DBMSBuffer ManagerTertiary StorageThe Mechanics of DiskDisk Access CharacteristicsThe I/O Model of ComputationLecture #19May 13th, 2002Agenda•Trip Report•End of constraints: triggers.•Systems aspects of SQL – read chapter 8 in the book.•Going under the lid!TriggersEnable the database programmer to specify:• when to check a constraint,• what exactly to do.A trigger has 3 parts:• An event (e.g., update to an attribute)• A condition (e.g., a query to check)• An action (deletion, update, insertion)When the event happens, the system will check the constraint, and if satisfied, will perform the action.NOTE: triggers may cause cascading effects. Database vendors did not wait for standards with triggers!Elements of Triggers (in SQL3)• Timing of action execution: before, after or instead of triggering event• The action can refer to both the old and new state of the database.• Update events may specify a particular column or set of columns.• A condition is specified with a WHEN clause.• The action can be performed either for• once for every tuple, or• once for all the tuples that are changed by the database operation.Example: Row Level TriggerCREATE TRIGGER NoLowerPricesAFTER UPDATE OF price ON ProductREFERENCING OLD AS OldTuple NEW AS NewTupleWHEN (OldTuple.price > NewTuple.price) UPDATE Product SET price = OldTuple.price WHERE name = NewTuple.nameFOR EACH ROWStatement Level Trigger CREATE TRIGGER average-price-preserveINSTEAD OF UPDATE OF price ON ProductREFERENCING OLD_TABLE AS OldStuf NEW_TABLE AS NewStufWHEN (1000 < (SELECT AVG (price) FROM ((Product EXCEPT OldStuf) UNION NewStuf))DELETE FROM Product WHERE (name, price, company) IN OldStuf;INSERT INTO Product (SELECT * FROM NewStuf)Bad Things Can HappenCREATE TRIGGER Bad-triggerAFTER UPDATE OF price IN ProductREFERENCING OLD AS OldTuple NEW AS NewTupleWHEN (NewTuple.price > 50) UPDATE Product SET price = NewTuple.price * 2 WHERE name = NewTuple.nameFOR EACH ROWA Naïve Database System•Store data in text files–Schema:Students(sid, name, dept), Courses(cid, name), Takes(sid,cid)–Schema file:Students #sid#INT #name#STR#dept#STRCourses#cid#INT#name#STRTakes#sid#INT#cid#INT–Data file:Smith#123#CSEJohn#456#EE…A Naïve DBMS•Query processing•Execution:–Read/parse query–Read schema file to determine attributes–Execute as nested loops–Print resultsSELECT Students.nameFROM Students, Takes, CoursesWHERE Students.sid=Takes.sid AND Takes.cid=Courses.cid AND Courses.name=‘Databases’SELECT Students.nameFROM Students, Takes, CoursesWHERE Students.sid=Takes.sid AND Takes.cid=Courses.cid AND Courses.name=‘Databases’What is Wrong with the Naïve DBMS•Tuple layout is rigid: what do we do on updates ?•Search is expensive: always read the entire relation•Query processing is by “brute force”: more clever ways existsWhat is Wrong with the Naïve DBMS•No way to buffer data in memory•No concurrency control•No reliability: we can lose data in a crash•No securityWhat Should a DBMS Do?•Store large amounts of data•Process queries efficiently•Allow multiple users to access the database concurrently and safely.•Provide durability of the data.•How will we do all this??Generic ArchitectureQuery compiler/optimizerExecution engineIndex/record mgr.Buffer managerStorage managerstorageUser/ApplicationQueryupdateQuery executionplanRecord,indexrequestsPage commandsRead/writepagesTransaction manager:•Concurrency control•Logging/recoveryTransactioncommandsQuery OptimizationPurchasePersonBuyer=nameCity=‘seattle’ phone>’5430000’buyer(Simple Nested Loops)Imperative query execution plan:SELECT Q.snameFROM Purchase P, Person QWHERE P.buyer=Q.name AND Q.city=‘seattle’ AND Q.phone > ‘5430000’ SELECT Q.snameFROM Purchase P, Person QWHERE P.buyer=Q.name AND Q.city=‘seattle’ AND Q.phone > ‘5430000’ Declarative SQL queryPlan: Tree of R.A. ops, with choice of alg for each op.Ideally: Want to find best plan. Practically: Avoid worst plans!Goal:Alternate PlansFind names of people who bought telephony productsPurchasePersonBuyer=nameCategory=“telephony”buyer(hash join)Productprod=pname(hash join)PurchaseProductBuyer=nameCategory=“telephony”buyer(hash join)Personprod=pname(hash join)But what if we’re only looking for Bob’s purchases?ACIDACID PropertiesAAtomicity: all actions of a transaction happen, or none happen.CConsistency: if a transaction is consistent, and the database starts from a consistent state, then it will end in a consistent state.IIsolation: the execution of one transaction is isolated from other transactions.DDurability: if a transaction commits, its effects persist in the database.Problems with Transaction ProcessingAirline reservation system: Step 1: check if a seat is empty. Step 2: reserve the seat.Bad scenario: (but very common) Customer 1 - finds a seat emptyCustomer 2 - finds the same seat emptyCustomer 1 - reserves the seat.Customer 2 - reserves the seat.Customer 1 will not be happy; spends night in airport hotel.The Memory HierarchyMain Memory Disk Tape•Volatile•limited address spaces • expensive• average access time: 10-100 nanoseconds• 5-10 MB/S transmission rates• 2-10 GB storage• average time to access a block: 10-15 msecs.• Need to consider seek, rotation, transfer times.• Keep records “close” to each other.• 1.5 MB/S transfer rate• 280 GB typical capacity• Only sequential access• Not for operational dataCache: access time 10 nano’sMain Memory•Fastest, most expensive•Today: 512MB are common on PCs•Many databases could fit in memory–New industry trend: Main Memory Database–E.g TimesTen•Main issue is volatilitySecondary Storage•Disks•Slower, cheaper than main memory•Persistent !!!•Used with a main memory bufferBuffer
View Full Document
Unlocking...