Transaction Management

1
ITC114

• Transaction Management

2
Objectives - Transaction Management

• The objectives for this lecture are
• Explain the concept properties of a
  transaction.
• Describe problems that can arise in a database
  that allows concurrent access.
• Explain how record locking is used to overcome
  concurrent access problems.
• Describe problems which may arise from the use of
  record locking.
• Detail other solutions to concurrent access
  problems.
• Explain how a database be recovered in the event
  of failure.

3
What is a transaction?

• What is a transaction?
• created by reading and/or writing to the
  database.
• A transaction
• made up of at least one database request (a
  single user task or activity).
• it is a logical unit of work (that must be
  entirely completed) no intermediate states are
  acceptable.
• the database will be in a consistent state after
  the transaction is completed.

4

• Examples of transactions
• Deleting a subject from student record
• Updating a student result.
• Adding a new patient to a medical rescord.
• Ordering a product
• Completing a reservation

5
Example of a transaction
Amount of stock X
X 40
Consistent state
Initial state
Transaction ARead ? Modify ? Write
Inconsistent state
X X - 10
X 30
Final state
Consistent state
6
Consider the following SQL statement

• SELECT NAME, ADDRESSFROM STUDENT WHERE ID
  111506443
• Even though there is no UPDATE to the STUDENT
  table it is still a transaction because it
  accessed the database.
• The database must be in a consistent state
  before the transaction and will be in a
  consistent state after the transaction.

7

• In the previous example only ONE SQL query was
  activated for the transaction.
• In cases where more than one SQL statement is
  required ALL these must be completed in order
  for the transaction to be satisfactorily
  completed.

8
Properties of a transaction

• 1. atomicity - defines a transaction as being a
  single, indivisible logical unit of work. If the
  transaction does not finish, it must be
  completely undone.
• 2. durability - indicates the permanence of the
  databases consistent state. Once a transaction
  is committed, these changes can not be lost (e.g.
  by a system failure).
• 3. serialisability allows for a number of
  concurrent transactions to be completed
  (basically each transaction are treated as if
  they were executed one after the after)
• 4. isolation - means that during a transaction
  execution, a second transaction cannot access
  this transactions data.

9
Properties of a transaction

• Single-user database
• automatically ensure serialisation and isolation
• Atomicity and durability of transaction must be
  guaranteed
• Multi-user database
• must implement controls to ensure serialisability
  and isolation of transaction
• also must implement controls for atomicity and
  durability.

10
Transaction Management

• To ensure all transactions display
• atomicity, durability, serialisability, and
  isolation
• then effective Transaction Management must be
  implemented.
• How does Transaction Management differ in the
  following situations single-user
  database? multi-user database environment?

11
SQL

• ANSI has defined standards for SQL database
  transactions to assist transaction management. In
  particular when a transaction is initiated it
  must continue through all the SQL statements
  until ONE of the following four events occur
• 1. COMMIT when all changes are permanently
  updated to the database. It will ends the
  transaction. e.g.The end of the program is
  reached changes are permanently made same as
  a COMMIT.

12
Means to end a transaction sequence

• 2. ROLLBACK which will terminate changes and
  rollback the database to its previous consistent
  state.
• Basically the changes made in the database need
  to be aborted, and the database is rolled back to
  its previous consistent state that is ROLLBACK
  needs to occur.

13
Transaction log

• A Transaction Log records all updates made to
  the database.
• used, where required, to rollback the DBMS.
• maintained by the DBMS it is a database itself
• data stored includes before values in tables,
  tuples, and attributes that participated in the
  transaction.
• overhead cost associated with the log.
• once a COMMIT is issued the details in the log
  are generally deleted.

14
Concurrency Control

• CONCURRENCY - is the theoretical simultaneous
  execution of transactions in a multiprocessing
  database system. It is required to ensure
  serialisation.
• Why have concurrency controls?
• lost updates
• uncommitted data
• inconsistent retrievals
• Concurrency problems arise because the computer
  cannot execute TWO commands simultaneously.

15
Lost Updates

• Consider
• An error can occur if two transactions can
  proceed on the same data.
• Assume initial QOH 35
• Transaction ComputationT1 Purchase 100
  units ? QOH QOH 100T2 Sell 30 units ? QOH
  QOH 30

16
Uncommitted data

• Problems can occur when two transactions are
  executed currently the first one is ROLLBACK
  however the 2nd transaction has already accessed
  this uncommitted data.
• Assume initial QOH 35
• Transaction ComputationT1 Purchase 100
  units ? QOH QOH 100 (roll back)T2 Sell 30
  units ? QOH QOH 30

17
Inconsistent retrieval

• An inconsistent retrieval is when a transaction
  calculates a summary on data while other
  transactions are updating the data.
• Basically updates are made while another
  transaction attempts to perform calculations.
• This means that there has to be a lock or some
  control in cases where one transaction attempts
  to access the data which is being acted upon by
  another transaction.

18
Consider inconsistent retrievals

• If an error is made during processing a
  transaction it needs to be reversed. If another
  transaction accesses the incorrect data before
  this occurs then this is referred to as an
  inconsistent retrieval.

19
Concurrency Control

• DBMS SCHEDULER - controls the database
  consistency of state. It determines the order in
  which operations with concurrent transactions are
  executed.
• no problems if access is to unrelated data.
• order controlled by the DBMS scheduler.
• the scheduler controls potential errors when two
  transactions perform READ or/and WRITE operations
  on the same data. Methods used include
• locking
• time stamping
• optimistic

20
Inconsistent state
Inconsistent state
Consistent state 1
Consistentstate 2
Transaction x
Operation 1Operation 2..Operation n
21
Locking Methods

• Locks - ensures exclusive use of data. It must
  be unlocked after the transaction.
• Lock Granularity - LEVEL of locks
• a. database level
• b. table level
• c. page level locks
• d. row level locks
• e. field level locks

22
Locking Methods

• TYPE of Lock
• i. Binary - either locked or unlocked no
  access at all
• ii. Shared/Exclusive indicates the nature of
  the lock
• Shared - allows for READ access only
• OR
• Exclusive - allows for WRITE access
• OR
• Unlocked - no assigned access

23
PROBLEMS with Shared/Exclusive locks

• - serialisation is not guaranteed.
• - when two transactions wait for a release of a
  lock - a Deadlock occurs.
• Deadlock basically occurs as follows
• John is waiting for a record from Mary,
• while Mary is waiting on a record from Bill
• who in turn is waiting for a record held by John
  
• That is, where one transaction is waiting for
  another to finish before it can proceed.

24

• iii. Two Phase Locking Protocol
• Defines how a transaction acquires and
  relinquishes locks to ensure serialisation (does
  not prevent deadlocks). The two phases are
• Acquire locks for the transaction (growing phase)
• Determines the rows required for the transaction
  acquires these rows and performs the
  transaction releasing none of the locks
• 2. Releases all acquired locks (shrinking phase)

25
How does a deadlock occur?

• Time Transaction Reply Lock status Data A
  Data B
• 0 Unlocked Unlocked
• 1 T1 (lock X) OK Locked Unlocked
• 2 T2 (lock Y) OK Locked Locked
• 3 T1 (lock Y) WAIT Locked Locked
• 4 T2 (lock X) WAIT Locked Locked
• 5 T1 (lock Y) WAIT Locked Locked
• 6 T2 (lock X WAIT Locked Locked
• etc

26
How to overcome a deadlocks

• Basic techniques to control deadlocks
• Deadlock Prevention aims to avoid the conditions
  that lead to deadlocks in which case its
  transaction is wound back.
• Deadlock Detection DBMS checks regularly for
  deadlocks. If found aborts one of the
  transactions.
• Deadlock Avoidance transactions must obtain all
  required locks prior to execution of the
  transaction.
• Choice of deadlock depends on database environment

27
Concurrency control - Time Stamping Methods

• Each transaction is assigned an unique
  timestamp. It ensures each transaction
• has a unique timestamp (uniqueness).
• assume a progressively increasing timestamp value
  (monotonicity).
• all transactions within the same transaction MUST
  have the same timestamp. Operations are executed
  in timestamp order.

28
Concurrency Control -Optimistic Method

• This control is based on the assumption that most
  transaction operations will not conflict.
• Basically this approach involves three phases
• Read Phase reads database, executes
  computations, updates a private copy of database
  values not accessible to other transactions.
• Validation Phase changes made checked that will
  not effect database integrity/consistency. If
  true, sent to write phase, if false, will abort
  start again.
• Write Phase changes permanently applied to
  database.

29
Management Issues with Database Recovery

• Failures do occur we would be naïve to assume
  otherwise .. some examples can include
• Software operating system, the DBMS software,
  or viruses
• Hardware memory chip errors, disk crashes, bad
  disk sectors, full disk errors, etc
• Application software errors in the programming
• Termination of a transaction due to system
  detecting a deadlock.

30
Recovery of the database

• Recovery restores a database from a given site,
  usually inconsistent state to a previously
  consistent state.
• Remember if all operations are not completed,
  then the transaction must be aborted and rolled
  back.
• How should the database be recovered?
• Management of database must account for
• normal business operations must continue
  (manually).
• recovery work must be managed.
• advice must be given to users as to what they
  need to do when the database is restored.

31
Recovery procedures

• Recovery is the process of returning the
  database to a state that is known to be correct.
• The following explanation is for large scale DBMS
  rather than PC-based DBMS
• a. Recovery via reprocessing time consuming
• b. Recovery via Backward recovery or forward
  recovery both of these steps involve returning
  the database to the last known consistent state.

32
Recovery of data ..

• This can be facilitated through use of
• Journal basically a log of events that occur
  during database updating. Typically a journal
  will include allow for
• Before images a record of the data looked like
  before the transaction occurred.
• After images a record of the data looked like
  after the transaction occurred.
• Commit information details of the start and
  finish of all successful transactions is also
  recorded.

33
Types of recovery

• Forward recovery
• Assume a disaster occurs the data in our
  database is corrupted.
• Last backup 18 hours earlier transactions have
  occurred since the disaster.
• Backup can be restored BUT 18 hours of
  transactions have occurred.
• Forward recovery program is the implemented
• This program scans the log for the latest update
  made to each respective record.
• Copies the after image of the final update made
  to each record.
• Updates this information to the backup copy of
  the database

34
Types of recovery.

• Backward recovery
• Sometimes transactions may not have completed
  resulting in the database not being in a valid
  state. This needs to be corrected.
• Backward recovery or rollback undoes the
  problem transaction/s to the last known valid
  state.
• The DBMS reads the log for the problem
  transaction and takes the before image

35
Disaster recovery for PC-based DBMS

• Recovery not as sophisticated as larger DBMS.
• Best to make regular backups
• Alternative is to customise your DBMS to write a
  record to a log