1 / 30

Transactions

Transactions. ACID Concurrency Control. Transaction - Definition. A transaction is an operation on data in the database. A transaction may be composed of several database operations, but is viewed as a logical unit of work A transaction must be done completely or not done at all

wilmet
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 ACID Concurrency Control NOEA/IT - FEN: Databases/Transactions

  2. Transaction - Definition • A transaction is an operation on data in the database. • A transaction may be composed of several database operations, but is viewed as a logical unit of work • A transaction must be done completely or not done at all • A transaction must have the ACID properties: • A: Either it is done in total or it is not done at all (Atomicity) • C: The database moves from one consistent state to an other consistent state (Consistency) • I: If more operations are accessing the same that, they are not to disturb each other – the must execute as if they executed alone (Isolation) • D: When a transaction completes, its changes to the database are permanent (Durability) NOEA/IT - FEN: Databases/Transactions

  3. time Transactions – example:T1 and T2 are executing concurrently • T1: Transfers N DKKs from account X to account Y: read_item(X); X:= X-N; write_item(X); read_item(Y); Y:= Y+N; write_item(Y); • T2: Deposits M DKK on account Y: read_item(Y); Y:= Y+M; write_item(Y); Any possible problems? NOEA/IT - FEN: Databases/Transactions

  4. Transactions - Problems • We want several transactions to execute concurrently (Why?) • Three types of problems: • lost update • uncommitted dependency (temporary update) • inconsistent analysis (incorrect summary) • Crash during execution of a transaction must be handled NOEA/IT - FEN: Databases/Transactions

  5. Lost Update • Fig. 19.3a NOEA/IT - FEN: Databases/Transactions

  6. Uncommitted Dependency • Fig. 19.3b NOEA/IT - FEN: Databases/Transactions

  7. Inconsistent Analysis • Fig 19.3c NOEA/IT - FEN: Databases/Transactions

  8. Transaction States • Operations: • Begin_transaction • read and write • end_transaction: • A transaction is in a committed state when it completes normally and changes can be written to the database • May also complete in abort (or rollback) state. In that case any changes to the database made by that transaction are to be undone (or rolled back) NOEA/IT - FEN: Databases/Transactions

  9. Transaction States NOEA/IT - FEN: Databases/Transactions

  10. The Log File • Holds information about: • start_transaction • write_item() • read_item() • commit • abort UPDATE/INSERT/DELETE in SQL SELECT in SQL ROLLBACK in some DBMSs NOEA/IT - FEN: Databases/Transactions

  11. Concurrency Control • Aim: to ensure consistency allowing maximum concurrency: • If all transactions are executed in sequence (serial), then consistency is assured, but no concurrency is allowed • If a parallel execution of a number of transactions is equivalent to a serial execution of the same transactions, then the executions schema is serialisable NOEA/IT - FEN: Databases/Transactions

  12. Concurrency Control and Recovery • Concurrency control: Managing concurrent execution of transactions, so no operations are conflicting • Recovery: Re-establishing the database in a consistent state after some error • The two are connected: concurrency control based on rollback is using the ability to recover • Recovery and rollback (abort) are based on logging NOEA/IT - FEN: Databases/Transactions

  13. Concurrency Control Techniques • locking: • Two Phase Locking (= = 2PL) • time stamps • multi version techniques • optimistic methods NOEA/IT - FEN: Databases/Transactions

  14. Concurrency Control - Locking • A transaction can get a lock on an object in the database so no other transaction can access that object. • An object may be • Read-locked (shared-locked): Some transaction(s) is (are) reading the object • Write-locked (Exclusive-locked): Some transaction is writing to the object • Unlocked • Deadlock is possible • The granularity of the locking system? (table, row, row set, attributes,…?) NOEA/IT - FEN: Databases/Transactions

  15. Deadlock Prevention • Conservative 2PL-protocol: • all affected objects are locked at transactions start • are the needed locks not available the locks already acquired are released and the transaction is restarted later • If all locks are acquired they are hold until the transaction completes • Guarantees deadlock-free executions • Provides very little concurrency • Is unrealistic in practical use • More about alternative (and not deadlock-free) versions of the 2PL protocol NOEA/IT - FEN: Databases/Transactions

  16. Deadlock Detection: wait-for-graph Maybe simply time-out NOEA/IT - FEN: Databases/Transactions

  17. Exercise NOEA/IT - FEN: Databases/Transactions

  18. Concurrency Control - 2PL • If transactions get locks on objects before accessing confliction operations can be avoided • Two-phase locking (2PL) protocol: • No lock is to be taken after first unlock • Expanding Phase: We are gathering objects (and may start to use them). If an object is already locked we will wait and try again later. • Shrinking Phase: We releasing objects again. • Strict 2PL hold locks until commit. • By releasing objects as we are finished with them, we allow for more concurrency, but increases the risk of cascading abort (rollback). NOEA/IT - FEN: Databases/Transactions

  19. #objects locked time 2PL Shrinking phase Growing phase NOEA/IT - FEN: Databases/Transactions

  20. 2PL • 2PL guarantees serialisable execution at the cost of concurrency • Deadlocks may occur.Usually handled by aborting (and restarting) transactions that repeatedly time out waiting for a lock on some object. • The ability to recover is normally present already in the recovery system. • Conservative 2PL guarantees deadlock prevention NOEA/IT - FEN: Databases/Transactions

  21. Concurrency Control - Timestamps • At transaction start every transaction is assigned a time stamp representing the starting time. • Every time a transaction is accessing an object, the time stamp of the transactions is stored (with the object). • Transactions are only allowed to access objects in time stamp order: • If T1 accesses object A and T2 then tries to access object A, then it is: • OK If(T1.TimeStamp is earlier than T2.TimeStamp) • ERROR : If(T2.TimeStamp is earlier than T1.TimeStamp) In this case T2 is aborted and restarted later. • Serialisable execution is guaranteed. NOEA/IT - FEN: Databases/Transactions

  22. Optimistic Concurrency Control (OCC) • Assumes that conflicts are rare, because most transactions operate on different objects. • Handle conflicts only if they actually occur. • Allow transactions to get on with their jobs. • Nothing is written physical before commit. Work is done on a local copy of the objects involved. • At commit it is checked that execution was serialisable – in case of conflict all involved transactions are aborted. NOEA/IT - FEN: Databases/Transactions

  23. Discussion • 2PL is most common, but has an considerable overhead due to deadlock management, especially in case of conflicts • time stamps: concurrency control is done on the affected object, and hence is a very nice strategy in distributed systems and in the case of few conflicts • optimistic methods: efficient if there are few conflicts, also preferable if there are real time requirements • optimistic locking is widely used in web-systems Why? NOEA/IT - FEN: Databases/Transactions

  24. SQL Support for Transactions • By default any SQL statement is considered to be atomic, that is: a transaction. • Transactions involving more than one SQL statement must be opened by BeginTransaction() and terminated by either Commit() or Rollback(). • It is possible to specify the isolation level of a transaction: • ReadUncommitted • ReadCommitted (Default on MS SQL Server) • RepeatableRead • Serializable NOEA/IT - FEN: Databases/Transactions

  25. MS SQL Server: Isolation Levels NOEA/IT - FEN: Databases/Transactions

  26. Recovery - Why • To be able to re-establish the system into a consistent state after some crash • To be able to use abort (rollback): • in concurrency control • From the transaction itself NOEA/IT - FEN: Databases/Transactions

  27. Recovery - How • Recovery is usually based on • logging (remember what you are doing) • But may also be based on • shadowing (updates are done on a copy and written to the database later) NOEA/IT - FEN: Databases/Transactions

  28. Recovery - logging • Execute all updates, but remember in the log-file what is executed • From the log one can do • redo: execute again • undo: write back before values (rollback) • The log holds information about: • which transaction executes • which operations on • which objects with • which arguments (values) • State before and after the operation • Begin_transaction, commit and abort (rollback) NOEA/IT - FEN: Databases/Transactions

  29. Recovery… • The log must be force-written to disc before a transaction commits • A checkpoint is entered into the log. At checkpoint: • All database objects and the log are force-written • The checkpoint is recorded • To do recovery • Start with a consistent version of the database (that is at a checkpoint) • From the log a Undo-list and a Redo-list is created • The Undo-list is traversed firstly, then the Redo-list • If deferred update is used, then Undo is not necessary NOEA/IT - FEN: Databases/Transactions

  30. Redo? Recovery… Undo? NOEA/IT - FEN: Databases/Transactions

More Related