A logical unit of work that must be entirely completed or entirely aborted.
transaction
A successful transaction changes the database from one ______ to another.
consistent state
One in which all data integrity constraints are satisfied.
consistent database state
Any action that reads from and/or writes to a database
transaction
To ensure consistency of the database, every transaction must begin with the database in a _______.
known consistent state
The equivalent of a single SQL statement in an application program or transaction.
database request
(T/F) Not all transactions update the database.
True
(T/F) A transaction may consist of a single SQL statement or a collection of related SQL statements.
True
(T/F) Improper or incomplete transactions can have a devastating effect on database integrity.
True
Transaction Properties
Atomicity
Consistency
Isolation
Durability
Serializability
All operations of a transaction must be completed; if not, the transaction is aborted.
Atomicity
Indicates the permanence of the database’s consistent state.
Consistency
Data used during the execution of a transaction cannot be used by a second transaction until the first one is completed.
Isolation
Once transactions are done, they cannot be undone or lost.
Durability
Ensures that the schedule for the concurrent execution of the transactions yields consistent results.
Serializability
Defined standards that govern SQL database transactions.
American National Standards Institute (ANSI)
Transaction support is provided by two SQL statements:
COMMIT
ROLLBACK
Transaction sequence must continue until
A COMMIT statement is reached
A ROLLBACK statement is reached
The end of a program is reached
The program is abnormally terminated
Keeps track of all transactions that update the database.
Transaction Log
The coordination of the simultaneous execution of transactions in a multiuser database system.
concurrency control
Objective of concurrency control
Ensure the serializability of transactions in amultiuser database environment
The three main problems of concurrency control:
Lost updates
Uncommitted data
Inconsistent retrievals
Occurs when two concurrent transactions are updating the same data element and one of the updates is lost (overwritten by the other transaction).
Lost update
Occurs when two transactions are executed concurrently and the first transaction is rolled back after the second transaction has already accessed the uncommitted data.
Uncommitted data
Occur when a transaction accesses data before and after another transaction finish working with such data.
Inconsistent retrievals
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
scheduler
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.
Serializable schedule
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 to occur.
Pessimistic locking.
All lock information is managed by a _______, which is responsible for assigning and policing the locks used by the transactions
lock manager
Indicates the level of lock use.
Lock granularity
Locking can take place at the following levels:
database
table
page
row
field (attribute)
The entire database is locked.
database-level lock
The entire table is locked.
table-level lock
The DBMS will lock an entire diskpage.
page-level lock
The equivalent of a diskblock,which can be described as a directly addressable section of a disk.
diskpage, or page
Allows concurrent transactions to access different rows of the same table even when the rows are located on the same page.
row-level lock
Allows concurrent transactions to access the same row as long as they require the use of different fields (attributes) within that row.
field-level lock
Has only two states: locked (1) or unlocked (0).
binary lock
Exists when access is reservedspecifically for the transaction that locked the object
exclusive lock
Exists when concurrent transactions are granted read accesson the basis of a common lock
shared lock
Only one transaction at a time can own an exclusive lock on the same object.
mutual exclusive rule
Occurs when two transactions wait indefinitely for each other to unlock data
deadlock
Using the shared/exclusive locking concept, a lock can have three states:
unlocked
shared (read)
exclusive (write)
Defines how transactions acquire and relinquish locks.
Two-phase locking
The two phases of locking are:
growing phase
shrinking phase
Transaction acquires all required locks without unlocking any data.
growing phase
Transaction releases all locks and cannot obtain any new lock.
shrinking phase
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
deadly embrace
The three basic techniques to control deadlocks are:
Prevention
Detection
Avoidance
A transaction requesting a new lock is aborted when there is the possibility that a deadlock can occur.
Deadlock prevention
The DBMS periodically tests the database for deadlocks.
Deadlock detection
The transaction must obtain all of the locks it needs before it can be executed.
Deadlock avoidance
The ________ approach to scheduling concurrent transactions 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
The older transaction rolls back the younger transaction and reschedules it.
wound/wait
The older transaction waits for the younger to complete and release its locks.
wait/die
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
Optimistic approach three phases:
read
validation
write
The transaction reads the database, executes the needed computations, and makes the updates to a private copy of the database values.
read phase
The transaction is validated to ensure that the changes made will not affect theintegrity and consistency of the database
validation phase
The changes are permanently applied to the database.
write phase
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 are actually updated.
write-ahead-log protocol
Ensure that a physical disk failure will not impair the DBMS’s ability to recover data.
Redundant transaction logs
Temporary storage areas in primary memory used to speed up disk operations.
buffers
Operations in which the DBMS writes all of its updated buffers to disk.
checkpoints
The transaction operations do not immediately update the physical database. Instead, only the transaction log is updated.