Concurrency and Transaction Management By Adrienne Watt Transaction

Concurrency and Transaction Management By Adrienne Watt

Transaction Management 2 represents a real-world event a transaction is a logical unit of work. No portion stands by itself a transaction that changes the contents of the database must alter the database from one consistent state to another a consistent database is one in which all data-integrity constraints are satisfied Examples: receivable (1) SELECT accno, name, balance FROM checkacc WHERE accno = ‘xyz’ (2) Reduce inventory of product X by 100 Add $500. 00 to customer Y’s accounts

Transaction Properties 3 atomicity - a single, indivisible logical unit of work the transaction either carries out all its operations on the database or it is totally cancelled durability - the completed transaction renders the database consistent, and this consistent state cannot be lost, even if the system fails. Once committed, it cannot be rolled back. serializability order - all concurrent transactions are executed in serial isolation - data used by one transaction cannot be used by a second transaction until the first one is successfully completed consistency - transformations preserve database consistency

Two states in transaction management 4 Commit - a transaction is committed if all updates are properly recorded in the database on the disk Rollback - if a transaction cannot be carried out in its entirety (for some reason), then all partial updates must be erased a log or journal of all update transactions is maintained by the system in particular before and after images of the updated object is kept. this log is used to recover the database after a transaction is aborted or after a system failure the log is usually stored on a different hard disk or on a different media (tape) to prevent the failure caused by a media error.

Database Recovery Management 5 Reasons for Database Failures: 1. Local failure 2. Global failure - system failure - media failure Recovery Methods 1. Local failure - Rollback 2. Global failure System failure – Backup

Database Recovery Management 6 Check. Point The point of synchronization between the database and the transaction log file. Checkpoint Record a record of all transactions that were in progress at time checkpoint was taken storing contents of database buffers

Database Recovery Management 7

Database Recovery Management 8 Restart Procedure 1. Transaction T 1 is okay 2. Establish two lists: UNDO list = all transactions in checkpoint record REDO list = empty 3. Search through log, starting from the checkpoint record: If a transaction is started, add it to the UNDO list If a COMMIT is found, remove it from the UNDO list, add it to the REDO list Media failure - reload the database from the most recent back-up - redo all transactions since the back-up was taken

CONCURRENCY By Adrienne Watt

Concurrency Concepts 10 Refers to the fact that a DBMS allows many transactions to access the same data at the same time. In a multi-user environment, several users have access to the same database at the same time A mechanism must exist to ensure that concurrent transactions do not interfere with each other’s operations There are three ways in which things can go wrong 1. the lost update 2. the uncommitted dependency (dirty read) 3. the inconsistent analysis

Lost Update Problem 11 Successfully completed updated is overridden by another user. Transaction A withdrawing $10 from account with balance initially at $100 Transaction B depositing $100 into same account Serially, final balance would be $190

Lost Update Problem 12 Loss of Transaction A’s update is avoided by preventing Transaction B from reading balance until after Transaction A’s update has been completed.

The uncommitted dependency problem (the dirty read) 13 Occurs when one transaction can see intermediate results of another transaction before it has committed. Transaction B updates balance to $200 but it aborts, so balance should be back at original value of $100 Transaction A has read new value of balance (200) and uses the value as basis of $10 reduction, giving a new balance of $190, instead

The uncommitted dependency problem (the dirty read) 14 Problem is avoided by preventing Transaction A from reading balance until after Transaction B commit or abort.

The inconsistent analysis problem 15

Serializability 16 Objective of a concurrency control protocol is to schedule transactions in such a way as to avoid any interference. Could run transactions serially, but this limits degree of concurrency or parallelism in system. Schedule: A transaction consists of a sequence of reads and write to the database. The sequence of reads/writes by a set of concurrent transactions taken together is known as a schedule.

Serializability 17 Serial Schedule: Schedule where operations of each transaction are executed consecutively without any interleaved operations from other transactions. Nonserial Schedule: Schedule where operations from set of concurrent transactions are interleaved. Objective of serializability is to find nonserial schedules that allow transactions to execute concurrently without interfering with one another.

Serializability 18 In serializability, ordering of read/writes is important: If two transactions only read a data item, they do no conflict and order is not important. If two transactions either read or write completely separate data items, they do not conflict and order is not important. If one transaction writes a data item and another reads or writes same data item, order of execution is important.

Concurrency Control with Locking Methods 19 Read Lock: If a transaction has read lock on item, can read but not update item. Write Lock: If transaction has write lock on item, can both read and update item. Transaction uses locks to deny access to other transactions and so prevent incorrect updates. A transaction must claim a read (shared) or write (exclusive) lock on a data item before read or write. Lock prevents another transaction from modifying the item or even reading it, in the case of a write lock. Reads cannot conflict, so more than one transaction can hold read locks simultaneously on same item. Write lock gives transaction exclusive access to that item.

2 PL – Two-phase locking 20 Growing Phase – transaction acquires all required locks without unlocking any data. Shrinking phase – transaction releases all locks and cannot obtain any new locks. Two transactions cannot have conflicting locks No unlock operation can precede a lock operation in the same transaction No data are affected until all locks are obtained

Preventing the lost update problem using 2 PL 21

Deadlocks 22 Deadlocks (the deadly embrace) – An impasse that may result when two or more transactions are each waiting for locks held by the other to be released. Techniques to control deadlocks: Deadlock prevention: A transaction requesting a new lock is aborted if there is a possibility that a deadlock may occur. Deadlock detection: The DBMS tests for deadlocks. If one is found, one of the transactions is aborted (rolled back and restarted) Deadlock avoidance: Obtain all locks needed before execution.

Concurrency – other ways 23 Timestamping: A concurrency control protocol in which the fundamental goal is to order transactions globally in such a way that older transactions, transactions with smaller timestamps, get priority in the event of conflict. Timestamp: A unique identifier created by the DBMS that indicates the relative starting time of transaction. Uniqueness ensures that no equal time stamp values can exist.

Optimistic concurrency control 24 Optimistic concurrency control (OCC) is a concurrency control method that assumes that multiple transactions can complete without affecting each other, and that therefore transactions can proceed without locking the data resources that they affect. Before committing, each transaction verifies that no other transaction has modified its data. If the check reveals conflicting modifications, the committing transaction rolls back.

OCC 25 Each transaction moves through two or three phases, referred to as read, validation, and write. Read phase – Transaction reads the database, execute sthe needed computations and makes the updates to a private copy of the database values. All update operations of the transaction are recorded in a temporary update file, which is not accessed by the remaining transactions. Validation phase - Transaction is validated to ensure that the changes made will not affect the integrity and consistency of the database. If validation is positive, the transaction goes to the write phase. If it’s negative, it is restarted and the changes are discarded. Write phase – the changes are permanently applied to the database The optimistic approach is acceptable for most read or query database systems that require few update transactions.

Locking granularity - the level of lock use 26 Exclusive Lock - no other user is permitted to access that data - only one transaction can place an exclusive lock at any given time Database level - the entire database is locked - access to any table in the database is denied Table level - access to the locked table is denied Row level - access to the tuple is denied Field level - access to the field is denied