1 / 13

Transactions

Transactions. Properties. Transactions. Solution for both concurrency and failures A transaction is a sequence of one or more SQL operations treated as a unit Transactions appear to run in isolation If the system fails, each transaction’s changes are

fausta
Download Presentation

Transactions

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Transactions Properties

  2. Transactions Solution for both concurrency and failures A transaction is a sequence of one or more SQL operations treated as a unit • Transactions appear to run in isolation • If the system fails, each transaction’s changes are reflected either entirely or not at all Transactions

  3. Transactions ACID Properties A C I D

  4. Transactions (ACID Properties) Isolation Serializability Operations may be interleaved, but execution must be equivalent to some sequential (serial) order of all transactions . . . DBMS Data

  5. Transactions Concurrent Access: Attribute-level Inconsistency UpdateCollegeSetenrollment=enrollment+1000 WherecName=‘Stanford’ concurrent with … UpdateCollegeSetenrollment=enrollment+1500 WherecName=‘Stanford’

  6. Transactions Concurrent Access: Tuple-level Inconsistency UpdateApplySetmajor=‘CS’WheresID=123 concurrent with … UpdateApplySetdecision=‘Y’WheresID=123

  7. Transactions Concurrent Access: Table-level Inconsistency UpdateApplySetdecision=‘Y’ WheresIDIn (Select sIDFrom Student Where GPA>3.9) concurrent with … UpdateStudentSetGPA=(1.1)GPAWheresizeHS>2500

  8. Transactions Concurrent Access: Multi-statement inconsistency Insert Into Archive Select * From Apply Wheredecision=‘N’; • Delete From Apply Where decision=‘N’; concurrent with … Select Count(*)From Apply; Select Count(*) From Archive;

  9. Transactions (ACID Properties) Durability If system crashes after transaction commits, all effects of transaction remain in database DBMS Data

  10. Transactions (ACID Properties) Atomicity Each transaction is “all-or-nothing,” never left half done DBMS Data

  11. Transactions Transaction Rollback (= Abort) • Undoes partial effects of transaction • Can be system- or client-initiated Each transaction is “all-or-nothing,” never left half done BeginTransaction; <getinputfromuser> SQLcommandsbasedoninput <confirmresultswithuser> Ifans=‘ok’ThenCommit;ElseRollback;

  12. Transactions (ACID Properties) Consistency • Each client, each transaction: • Can assume all constraints hold when • transaction begins • Must guarantee all constraints hold • when transaction ends . . . Serializability constraints always hold DBMS Data

  13. Transactions Solution for both concurrency and failures Atomicity Consistency Isolation Durability Transactions

More Related