1 / 15

Concurrency control using transactions

Concurrency control using transactions. Transaction, the concept. Logical unit of work on the database Examples Transfer money from one bank account to another Reserve a seat on a flight Transaction boundaries are defined by the database user / application programmer That’s us!.

keiki
Download Presentation

Concurrency control using 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. Concurrency controlusing transactions Transactions

  2. Transaction, the concept • Logical unit of work on the database • Examples • Transfer money from one bank account to another • Reserve a seat on a flight • Transaction boundaries are defined by the database user / application programmer • That’s us! Transactions

  3. Interleaved processingversus parallel processing Transactions

  4. Transaction operations • Reading data • Read_item(X) • Reads data X from the database • Find the address of the disk block containing X • Copy the disk block from hard disk to buffer in main memory • Copy data from the buffer to program variable X • Writing data • Write_item(X) • Writes data X to the database • Find the address of the disk block containing X • Copy the disk block from hard disk to buffer in main memory • Copy item X from program variable to buffer • Store the updated buffer on hard disk • SELECT = Read_item(X) • INSERT = Write_item(X) • UPDATE = {Read_item(X)} + Write_item(X) Transactions

  5. Example transactions • Transferring N kroner from account X to account Y • Updating account X, adding M kroner Transactions

  6. Concurrency problems: Lost update + dirty read Transactions

  7. Concurrency problems:Incorrect analysis Transactions

  8. Concurrency problems:Unrepeatable reads • A transaction reads the same data item twice, with different results! • Example: Ticket reservation • The seat is free • After at little while, your decide to buy the ticket • But now the seat is not longer free • Someone else bought it while you are ”thinking”. Transactions

  9. Transaction states • Transactions can end in two ways • COMMIT_TRANSACTION • Successful end of transaction • ROLLBACK / ABORT • Unsuccessful end of transaction Transactions

  10. ACID properties of a transaction • Atomicity • A transaction is an atomic unit of processing: It should either be performed in its entirety OR not performed at all. • Rollback means rollback the transaction as if it never happened. • Consistency • A transaction takes the database from one consistent state to another • Isolation • Transactions should be executed in isolation from other transactions • Durability • The results of a committed transaction can never be undone Transactions

  11. Schedules of transactions • Schedule, a definition • A schedule of a set of transactions is an ordering of the operations of the transactions • Examples, fig. 20.3 • Sa: r1(X); r2(X); w1(X);r1(Y); w2(X); w1(Y) • T1 reads X, T2 reads X, T1 writes X, etc. • Sb: r1(X); w1(X); r2(X); w2(X); r1(Y); abort T1 • Serial schedule • No concurrency permitted • T1 finishes before T2 starts, or vice versa • Not good: Too much waiting time • (Conflict) serializable schedule • Schedule where the end result is (conflict) equal to the result of some serial schedule • This is OK. But how do we achieve it? Transactions

  12. Serial and non-serial schedules Transactions

  13. Testing for (conflict) serializability of a schedule • A simple(?) way to test if a schedule is serializable • Make a precedence graph • Nodes: Transactions • Edges • Tj does Read_item(X) AFTER Ti does Write_item(X) • Ti → Tj • Tj does Write_item(X) AFTER Ti does Read_item(X) • Ti → Tj • Tj does Write_item(X) AFTER Ti does Write_item(X) • Ti → Tj • Schedule is serializable if the precedence graph has no cycles Transactions

  14. Example: Precedence graphs Transactions

  15. Transaction support in Microsoft SQL Server • BEGIN TRANSACTION • COMMIT TRANSACTION • ROLLBACK TRANSACTION • Documentation • http://msdn.microsoft.com/en-us/library/ms174377.aspx Transactions

More Related