Transactions

Transactions

ACID Properties
1. Atomicity
  1. The transaction is performed in its entirety or not at all.
2. Consistency
  1. A transaction must transform the database from one consistent state to another.
  2. A successfully completed transaction is committed by the DBMS.
  3. If the transaction is not completed in its entirety, it is aborted.
  4. Aborted transactions are rolled back to ensure consistency.
3. Isolation
  1. Transactions should be isolated from other transactions occurring at the same time.
  2. Hence the need for concurrency control.
4. Durability
  1. Once a transaction is completed (committed), its effects are permanently recorded in the database.
  2. These effects must not be lost because of subsequent failures.
Transaction Management
1. Database Recovery
  1. The process of restoring the database to a correct state should a failure occur in the middle of a transaction.
  2. Preserves atomicity and durability.
2. Concurrency Control
  1. The process of managing simultaneous operations on a multi-user DB without allowing them to interfere with one another.
  2. Preserves consistency and isolation.
Types of backup
1. Complete copy of whole database.
2. Incremental backup (copy of modifications made since the last complete or incremental backup).
3. Backups of the logs file.
Concurrency Control
1. The Scheduler
  1. A DBMS module responsible for implementing a particular strategy for concurrency control.
  2. Allows the CPU to execute several transactions concurrently instead of waiting for the I/O operations of one transaction to complete before another can begin.
  3. Transactions are interleaved.
2. The Lost Update Problem
  1. When two transactions accessing the same item have their operations interleaved in a way that makes the value of some database items incorrect.
3. The Temporary Update
  1. When one transaction updates a database item and then the transaction fails. Updated item is accessed by another transaction before it is changed back to its original value.
4. The Incorrect Summary
  1. Aggregate function may calculate some values before they are updated and others after they are updated.
Types of Schedules
1. Serial Schedule
  1. Operations of each transaction are executed consecutively.
  2. No interleaved operations from other transactions.
  3. Transactions cannot interfere with each other.
2. Non-serial Schedule
  1. Operations from a set of concurrent transactions are interleaved.
  2. Potential for interference between concurrent transactions.
Concurrency Control (Locking)
1. Locking
  1. Procedure used to control concurrent access to data (items).
  2. When one transaction is accessing a data item, a lock may deny access to other transactions.
2. Granularity of locks
  1. Entire database.
  2. A file.
  3. A page.
  4. A record.
  5. A field value of a record.
3. Read (shared) locks
  1. If a transaction has a shared lock on a data item, it can read the item but not update it.
  2. Read locks cannot conflict, so more than one transaction can hold shared locks on the same item.
4. Write (exclusive) locks
  1. Transaction can read and update item.
  2. Only one transaction at a time can hold this lock on an item.
  3. Other transactions cannot lock, read or update that item.
5. Releasing locks.
  1. It explicitly releases lock during execution.
  2. Transaction terminates (commits or aborts).
Two-Phase Locking (2PL)
1. Transaction follows the two-phase locking protocol if all locking operations precede the first unlock operation.
2. Guarantees serializability and is the best known protocol.
3. Growing Phase
  1. Transaction must lock every item it operates on.
  2. Transaction acquired all the locks it will need and cannot release any lock.
4. Shrinking Phase
  1. Transaction releases its locks.
  2. Once a lock has been released, no new locks can be applied.
Resolving Deadlocks
1. Impasse that may result when two (or more) transactions are each waiting for locks to be released that are held by each other.
2. Timeouts
  1. Transaction that requests a lock will wait for a given time only and then the request times out.
  2. Simple and practical strategy, used by several commercial DBMS's.
3. Deadlock Prevention
  1. DBMS looks ahead to determine if a transaction would cause a deadlock.
  2. Wait - Die Strategy
    1. O needs a resource held by Y. O waits.
  3. Wound - Wait Strategy
    1. O needs a resource held by Y. Y dies.
4. Deadlock Detection and Recovery
  1. DBMS allows deadlock to occur but recognises occurrences of deadlock and breaks them.
  2. Deadlock detection handled by construction of wait-for graphs that show transaction dependencies.
  3. Deadlock recovery involves aborting one or more transactions.
5. Timestampting
  1. No locks involved, avoids deadlocks.
  2. No waiting, conflicting transactions are rolled back and restarted.
  3. Concurrency control protocol that orders transactions in a way that older transactions get priority in the event of a conflict.

Next up

Description

Resource summary

Similar

	Created by i7752068 over 10 years ago