Transaction Concurrency Control Flashcards
- any action that reads from or writes to a database.
- sequence of database requests that accesses the database.
Transaction
a logical unit of work; that is, it must be entirely completed or aborted—no intermediate ending states are accepted
Transaction
Transaction support is provided by two SQL statements
COMMIT and ROLLBACK.
____ require that when a transaction sequence is initiated by a user or an
application program, the sequence must continue through all succeeding SQL statements
ANSI standards
This keeps track of all transactions that update the database
transaction log
A DBMS feature that coordinates the simultaneous execution of transactions in a
multiprocessing database system while preserving data integrity
Concurrency Control?
Three main problems of Concurrency Control
Problem occurs when two concurrent transactions, T1 and T2, are updating the same
data element and one of the updates is lost (overwritten by the other transaction).
Lost Updates
It occurs when two transactions, T1 and T2, are executed concurrently and the first transaction (T1) is rolled back after the second transaction (T2) has already accessed the uncommitted data—thus violating the isolation property of transactions.
Uncommitted Data
It occur when a transaction accesses data before and after one or more other transactions finish working with such data.
Inconsistent Retrieval
a special DBMS process that establishes the order in which the operations are
executed within concurrent transactions.
Scheduler
The scheduler ________ the execution of database operations to ensure
serializability and isolation of transactions
interleaves
scheduler’s main job
to create a serializable schedule of a transaction’s operations
guarantees exclusive use of a data item to a current transaction.
lock
The use of locks based on the assumption that conflict between transactions is likely usually referred to as
pessimistic locking
issued when a transaction requests permission to update a data item and no locks are held on that data item by any other transaction. An exclusive lock does not allow other transactions to access the database
exclusive lock
allows other read only transactions to access the database.
shared lock
a condition in which only one transaction at a time can own an exclusive lock on the same object.
mutual exclusive rule
assigns a global,
unique time stamp to each transaction.
time stamping
ensures that no equal time stamp values can exist
Uniqueness
ensures that time stamp values always increase.
monotonicity
________ is based on the assumption that the majority of database operations do not conflict.
optimistic approach
In this phase the transaction reads the database, executes the needed computations, and makes the updates to a private copy of the database values.
read phase
During this phase, the transaction is validated to ensure that the changes made will not affect the integrity and consistency of the database.
validation phase
During this phase, the changes are permanently applied to the database.
write phase
The ____ defines transaction management based on transaction isolation levels.
ANSI SQL standard (1992)
a transaction can read data that is not yet committed.
Dirty read
a transaction reads a given row at time t1, and then it reads the same row at time t2, yielding different results. The original row may have been updated or deleted.
Nonrepeatable read
a transaction executes a query at time t1, and then it runs the same query at time t2, yielding additional rows that satisfy the query.
Phantom read
At this isolation level, the database does not place any locks on the data, which increases transaction
performance but at the cost of data consistency
Read Uncommitted
forces transactions to read only committed data.
Read Committed
isolation level ensures that queries return consistent results. This type of isolation level uses shared locks to ensure other transactions do not update a row after the original query reads.
The Repeatable Read
restores a database from a given state (usually inconsistent) to a previously consistent state.
Database recovery
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
atomic transaction property
ensures that transaction logs are always written before any database data is actually updated.
write-ahead-log protocol
(several copies of the transaction log) ensure that a physical disk failure will not impair the DBMS’s ability to recover data
Redundant transaction logs
are temporary storage areas in primary memory used to speed up disk operations
Database buffers
are operations in which the DBMS writes all of its updated buffers in memory (also known as dirty buffers) to disk.
Database checkpoints