Transaction Management - Data Base Management System - Lecture Slides, Slides of Database Management Systems (DBMS)

Download Transaction Management - Data Base Management System - Lecture Slides and more Slides Database Management Systems (DBMS) in PDF only on Docsity!

Transaction Management

Overview

• Transaction
• Properties of transaction
• Pessimistic & optimistic techniques
  • Locking
  • No lock techniques
• Oracle recovery tools

Properties of Transaction

• ACID

• Atomicity : ‘all or nothing’ property. (Responsible by DBMS recovery subsystem)
• Consistency : database must be transform from one consistent state to another consistent state.

Properties of Transaction (cont.)

• ACID

• Isolation : transaction execute independently of one another. (Responsible by concurrency control)
• Durability : The effects of a successfully completed (committed) transaction are permanently recorded in the database and must not be lost because of a subsequent failure (Responsible by recovery subsystem)

Pessimistic & Optimistic techniques

• Pessimistic
  • Check for conflict when read/write
• Optimistic
  • Only check for conflict when the transaction wishes to commit

Pessimistic & Optimistic techniques

• Pessimistic
  • Using locking techniques
• Optimistic
  • The concurrency control that does not use lock and it involve the following phases: - Read phase - Validation phase - Write phase

Read phase

• Extends from start of transaction to just before commit
• Read from database
• Store in local variable

Validation phase

• Follow the read phase
• Purpose is to ensure that data is still current.

Pessimistic techniques

• Using locking technique to reduce conflict and ensure data consistency. - The lost update problem

The lost update problem

T1 T balx = $0 balx = $

The lost update problem

T1 T balx = $90 balx = $

The lost update problem

• T1 T
• balx = $90 balx = $
  • bal should be = (100+100 -10) = $
• Since T1 committed after T2, it written over the value of T

The inconsistent analysis problem

How to deal with these problem?

• Serial schedule
  • A schedule where the operations of each transaction are executed consecutively without any interleaved operations from the other transaction
• Locking technique
  • A procedure used to control concurrent access to data. When one transaction is accessing the database, a lock may deny access to other transactions to prevent incorrect results.