Transaction Management and Concurrency Control

A logical unit of work that must be entirely completed or entirely aborted.

A successful transaction changes the database from one ______ to another.

One in which all data integrity constraints are satisfied.

Any action that reads from and/or writes to a database

To ensure consistency of the database, every transaction must begin with the database in a _______.

The equivalent of a single SQL statement in an application program or transaction.

(T/F) Not all transactions update the database.
True False

(T/F) A transaction may consist of a single SQL statement or a collection of related SQL statements.
True False

(T/F) Improper or incomplete transactions can have a devastating effect on database integrity.
True False

Transaction Properties

All operations of a transaction must be completed; if not, the transaction is aborted.

Indicates the permanence of the database’s consistent state.

Data used during the execution of a transaction cannot be used by a second transaction until the first one is completed.

Once transactions are done, they cannot be undone or lost.

Ensures that the schedule for the concurrent execution of the transactions yields consistent results.

Defined standards that govern SQL database transactions.

Transaction support is provided by two SQL statements:

Transaction sequence must continue until

Keeps track of all transactions that update the database.

The coordination of the simultaneous execution of transactions in a multiuser database system.

Objective of concurrency control

The three main problems of concurrency control:

Occurs when two concurrent transactions are updating the same data element and one of the updates is lost (overwritten by the other transaction).

Occurs when two transactions are executed concurrently and the first transaction is rolled back after the second transaction has already accessed the uncommitted data.

Occur when a transaction accesses data before and after another transaction finish working with such data.

A special DBMS process that establishes the order in which the operations within concurrent transactions are executed and interleaves the execution of database operations to ensure serializability and isolation of transactions

Schedule of a transaction’s operations in which the interleaved execution of the transactions yields the same results as if the transactions were executed in serial order.

Guarantees exclusive use of a data item to a current transaction.

The use of locks based on the assumption that conflict between transactions is likely to occur.

All lock information is managed by a _______, which is responsible for assigning and policing the locks used by the transactions

Indicates the level of lock use.

Locking can take place at the following levels:

The entire database is locked.

The entire table is locked.

The DBMS will lock an entire diskpage.

The equivalent of a diskblock,which can be described as a directly addressable section of a disk.

Allows concurrent transactions to access different rows of the same table even when the rows are located on the same page.

Allows concurrent transactions to access the same row as long as they require the use of different fields (attributes) within that row.

Has only two states: locked (1) or unlocked (0).

Exists when access is reservedspecifically for the transaction that locked the object

Exists when concurrent transactions are granted read accesson the basis of a common lock

Only one transaction at a time can own an exclusive lock on the same object.

Occurs when two transactions wait indefinitely for each other to unlock data

Using the shared/exclusive locking concept, a lock can have three states:

Defines how transactions acquire and relinquish locks.

The two phases of locking are:

Transaction acquires all required locks without unlocking any data.

Transaction releases all locks and cannot obtain any new lock.

If T1 has not unlocked data item Y, T2 cannot begin; if T2 has not unlocked data item X, T1 cannot continue.Consequently, T1 and T2 each wait for the other to unlock the required data item. Such a deadlock is also known as

The three basic techniques to control deadlocks are:

A transaction requesting a new lock is aborted when there is the possibility that a deadlock can occur.

The DBMS periodically tests the database for deadlocks.

The transaction must obtain all of the locks it needs before it can be executed.

The ________ approach to scheduling concurrent transactions assigns a global, unique time stamp to each transaction.

Ensures that no equal time stamp values can exist.

Ensures that time stamp values always increase.

The older transaction rolls back the younger transaction and reschedules it.

The older transaction waits for the younger to complete and release its locks.

Based on the assumption that the majority of the database operations do not conflict and requires neither locking nor time stamping techniques. Instead, a transaction is executed without restrictions until it is committed.

Optimistic approach three phases:

The transaction reads the database, executes the needed computations, and makes the updates to a private copy of the database values.

The transaction is validated to ensure that the changes made will not affect theintegrity and consistency of the database

The changes are permanently applied to the database.

Restores a database from a given state (usually inconsistent) to a previously consistent state.

All portions of the transaction must be treated as a single, logical unit of work in which all operations are applied and completed to produce a consistent database

Ensures that transaction logs are always written before any database data are actually updated.

Ensure that a physical disk failure will not impair the DBMS’s ability to recover data.

Temporary storage areas in primary memory used to speed up disk operations.

Operations in which the DBMS writes all of its updated buffers to disk.

The transaction operations do not immediately update the physical database. Instead, only the transaction log is updated.