1 / 15

Transactions

Transactions. Introduction. Transactions. Motivated by two independent requirements Concurrent database access Resilience to system failures. Transactions. Concurrent Database Access. Even more software. Select… Update… Create Table… Drop Index… Help… Delete…. More software.

seanna
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 Introduction

  2. Transactions Motivated by two independent requirements • Concurrent database access • Resilience to system failures

  3. Transactions Concurrent Database Access Even more software Select… Update… Create Table… Drop Index… Help… Delete… More software DBMS Data

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

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

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

  7. 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;

  8. Transactions Concurrency Goal Execute sequence of SQL statements so they appear to be running in isolation • Simple solution: execute them in isolation But want to enable concurrency whenever safe to do so

  9. Transactions Resilience to System Failures Bulk Load DBMS Data

  10. Transactions Resilience to System Failures Insert Into Archive Select * From Apply Wheredecision=‘N’; • Delete From Apply Where decision=‘N’; DBMS Data

  11. Transactions Resilience to System Failures Lots of updates buffered in memory DBMS Data

  12. Transactions System-Failure Goal Guarantee all-or-nothing execution, regardless of failures DBMS Data

  13. 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

  14. Transactions Solution for both concurrency and failures A transaction is a sequence of one or more SQL operations treated as a unit. SQL standard: • Transaction begins automatically on first SQL statement • On “commit” transaction ends and new one begins • Current transaction ends on session termination • “Autocommit” turns each statement into transaction Transactions

  15. Transactions Solution for both concurrency and failures A transaction is a sequence of one or more SQL operations treated as a unit Transactions

More Related