310 likes | 428 Views
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
E N D
Transactions ACID Concurrency Control NOEA/IT - FEN: Databases/Transactions
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
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
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
Lost Update • Fig. 19.3a NOEA/IT - FEN: Databases/Transactions
Uncommitted Dependency • Fig. 19.3b NOEA/IT - FEN: Databases/Transactions
Inconsistent Analysis • Fig 19.3c NOEA/IT - FEN: Databases/Transactions
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
Transaction States NOEA/IT - FEN: Databases/Transactions
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
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
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
Concurrency Control Techniques • locking: • Two Phase Locking (= = 2PL) • time stamps • multi version techniques • optimistic methods NOEA/IT - FEN: Databases/Transactions
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
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
Deadlock Detection: wait-for-graph Maybe simply time-out NOEA/IT - FEN: Databases/Transactions
Exercise NOEA/IT - FEN: Databases/Transactions
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
#objects locked time 2PL Shrinking phase Growing phase NOEA/IT - FEN: Databases/Transactions
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
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
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
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
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
MS SQL Server: Isolation Levels NOEA/IT - FEN: Databases/Transactions
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
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
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
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
Redo? Recovery… Undo? NOEA/IT - FEN: Databases/Transactions