[Notes] CSCI 585 DB Transaction Management and Concurrency Control

18 Feb 2020 | CSCI585, English/英文, Note

Credit to: Prof. Saty Raghavachary, CSCI 585, Spring 2020

outline

• database transactions and their properties
• What concurrency control is and what role it plays in maintaining the database’s integrity
• What locking methods are and how they work
• How stamping methods are used for concurrency control
• How optimistic methods are used for concurrency control
• How database recovery management is used to maintain database integrity

---

Chapter 10

Transaction

• Logical unit of work that must be entirely completed or aborted
• Consists of:
  • SELECT statement
  • Series of related UPDATE statements
  • Series of INSERT statements
  • Combination of SELECT, UPDATE, and INSERT statements

• Consistent database state: All data integrity constraints are satisfied
  • Must begin with the database in a known consistent state to ensure consistency
• Formed by two or more database requests
  • Database requests: Equivalent of a single SQL statement in an application program or transaction
• Consists of a single SQL statement or a collection of related SQL statements

Transaction Properties

• Atomicity
  • All oprerations of a transaction must be completed, if not, the transaction is aborted
• Consistency
  • Permanence of database’s consistent state
• Isolation
  • Data used during transaction cannot be used by second transaction until the first is completed
• Durability
  • Ensures that once transactions are committed, they cannot be undone or lost
• Serializability (transaction is serilizable)
  • Ensures that the schedule for the concurrent execution of several transactions should yield consistent results

Transaction Management with SQL

• SQL statements that provide transaction support
  • COMMIT
  • ROLLBACK
• Transaction sequence must continue until:
  • COMMIT statement is reached
  • ROLLBACK statement is reached
  • End of program is reached
  • Program is abnormally terminated

Transaction Log (tracking update)

• Keeps track of all transactions that update the database
• DBMS uses the information stored in a log for:
  • Recovery requirement triggered by a ROLLBACK statement
  • A program’s abnormal termination
  • A system failure

Concurrency Control

• Coordination of the simultaneous transactions execution in a multiuser database system
• Objective - Ensures serializability of transactions in a multiuser database environment

Problems in Concurrency Control

• Lost update
  • Occurs in two concurrent transactions when:
    • Same data element is updated
    • One of the updates is lost
• Uncommitted data
  • Occurs when:
    • Two transactions are executed concurrently
    • First transaction is rolled back after the second transaction has already accessed uncommitted data
• Inconsistent retrievals
  • Occurs when a transaction accesses data before and after one or more other transactions finish working with such data
  • It is also called ‘dirty read
• non-repeatable reads
  • the data is consistently updated, make read inconsistency
• phantom row reads
  • while do aggregate calculation more rows of data is generated, make result wrong

The Scheduler

• Establishes the order in which the operations are executed within concurrent transactions
  • Interleaves the execution of database operations to ensure serializability and isolation of transactions
• Based on concurrent control algorithms to determine the appropriate order
• Creates serialization schedule
  • Serializable schedule: Interleaved execution of transactions yields the same results as the serial execution of the transactions

Concurrency Control with Locking Methods

• Locking methods - Facilitate isolation of data items used in concurrently executing transactions
• Lock: Guarantees exclusive use of a data item to a current transaction
• Pessimistic locking: Use of locks based on the assumption that conflict between transactions is likely (always)
• Lock manager: Responsible for assigning and policing the locks used by the transactions

Lock Granularity

• Indicates the level of lock use
• Levels of locking
  • Database-level lock
  • Table-level lock
  • Page-level lock
    • Page or diskpage: Directly addressable section of a disk
  • Row-level lock
  • Field-level lock

Lock Types (unlocked, locked_read, locked_write)

• Binary lock
  • Has two states, locked (1) and unlocked (0)
    • If an object is locked by a transaction, no other transaction can use that object
    • If an object is unlocked, any transaction can lock the object for its use
• Exclusive lock (write lock)
  • Exists when access is reserved for the transaction that locked the object
  • Issued when a transaction wants to WRITE data, and no lock is held (on a data item)
• Shared lock (read lock)
  • Exists when concurrent transactions are granted read access on the basis of a common lock
  • Issued when a transaction when a transaction wants to READ data, and no exclusive lock is held (on a data item)

Two-Phase Locking (2PL)

• Defines how transactions acquire and relinquish locks
• Guarantees serializability but does not prevent deadlocks
• Phases
  • Growing phase - Transaction acquires all required locks without unlocking any data
  • Shrinking phase - Transaction releases all locks and cannot obtain any new lock

Governing rules

• 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

Once all the locks are acquired, a transaction can proceed ‘smoothly’, ie won’t ‘hang’ or lead to dirty reads etc.

As for the middle point above (unlocking can’t precede locking)

Deadlocks

• Occurs when two transactions wait indefinitely for each other to unlock data
  • Known as deadly embrace
• Control techniques
  • Deadlock prevention
    • A transaction requesting a new lock is aborted when there is the possibility that a dead-lock can occur. If the transaction is aborted, all changes made by this transaction are rolled back and all locks obtained by the transaction are released. The transaction is then rescheduled for execution. Deadlock prevention work because it avoids the conditions that lead to deadlocking.
  • Deadlock detection
    • The DBMS periodically tests the database for deadlocks. If a deadlock is found, the “victim” transaction is aborted (rolled back and restarted) and the other transaction continues.
  • Deadlock avoidance
    • The transaction must obtain all of the locks it needs before it can be executed. This technique avoids the rolling back of conflicting transactions by requiring the locks be obtained in succession. However, the serial lock assignment required in deadlock avoidance increases action response times.
• Choice of deadlock control method depends on database environment

Time Stamping

• Assigns global, unique time stamp to each transaction
  • Produces explicit order in which transactions are submitted to DBMS
• Properties
  • Uniqueness: Ensures no equal time stamp values exist
  • Monotonicity: Ensures time stamp values always increases

Disadvantages

• Each value stored in the database requires two additional stamp fields
• Increases memory needs
• Increases the database’s processing overhead
• Demands a lot of system resources

Deadlock prevention

Note that wound-wait is a preemptive deadlock prevention scheme, whereas wait-die is a non-preemptive one.

Phases of Optimistic Approach (fix it latter)

• Read
  • Transaction:
    • Reads the database
    • Executes the needed computations
    • Makes the updates to a private copy of the database values
• Validation
  • Transaction is validated to ensure that the changes made will not - affect the integrity and consistency of the database
• Write
  • Changes are permanently applied to the database