Transaction Management Notes, Study notes of Database Management Systems (DBMS)

Download Transaction Management Notes and more Study notes Database Management Systems (DBMS) in PDF only on Docsity!

MECM

Database Management System

Transaction Management

Lecture 1

2 Chapter - Objectives

Function and importance of transactions.

Properties of transactions.

Concurrency Control

 (^) Meaning of serializability.  (^) How locking can ensure serializability.  (^) Deadlock and how it can be resolved.  (^) How timestamping can ensure serializability.  (^) Optimistic concurrency control.  (^) Granularity of locking.

4 Transaction Support Transaction Action, or series of actions, carried out by user or application, which accesses or changes contents of database. Logical unit of work on the database. Application program is series of transactions with non-database processing in between. Transforms database from one consistent state to another, although consistency may be violated during transaction.

5 Example Transaction

7 State Transition Diagram for Transaction

8 Properties of Transactions Four basic (ACID) properties of a transaction are: Atomicity 'All or nothing' property. Consistency Must transform database from one consistent state to another. Isolation Partial effects of incomplete transactions should not be visible to other transactions. Durability Effects of a committed transaction are permanent and must not be lost because of later failure.

10 Concurrency Control Process of managing simultaneous operations on the database without having them interfere with one another. Prevents interference when two or more users are accessing database simultaneously and at least one is updating data. Although two transactions may be correct in themselves, interleaving of operations may produce an incorrect result.

11 Need for Concurrency Control

Three examples of potential problems

caused by concurrency:

 (^) Lost update problem  (^) Uncommitted dependency problem  (^) Inconsistent analysis problem.

13 Lost Update Problem Loss of T 2 's update avoided by preventing T 1 from reading balx until after update.

14 Uncommitted Dependency Problem

Occurs when one transaction can see

intermediate results of another

transaction before it has committed.

T

4 updates balx to £200 but it aborts, so

balx should be back at original value of

T

3 has read new value of balx (£200) and

uses value as basis of £10 reduction,

giving a new balance of £190, instead of

16 Inconsistent Analysis Problem Occurs when transaction reads several values but second transaction updates some of them during execution of first. Sometimes referred to as dirty read or unrepeatable read. T 6 is totaling balances of account x (£100), account y (£50), and account z (£25). Meantime, T 5 has^ transferred^ £10^ from balx to balz, so T 6 now has wrong result (£10 too high).

17 Inconsistent Analysis Problem Problem avoided by preventing T 6 from reading balx and balz until after T 5 completed updates.

19 Serializability Schedule Sequence of reads/writes by set of concurrent transactions. Serial Schedule Schedule where operations of each transaction are executed consecutively without any interleaved operations from other transactions. No guarantee that results of all serial executions of a given set of transactions will be identical.

20 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. In other words, want to find nonserial schedules that are equivalent to some serial schedule. Such a schedule is called serializable.