Ch 10 Flashcards
Transaction
A transaction is a logical unit of work that must be entirely completed or entirely aborted; no intermediate states are acceptable.
Consistent database state
A consistent database state is one in which all data integrity constraints are satisfied.
Database request
A database request is the equivalent of a single SQL statement in an application program or transaction. E.g. if a transaction is composed of two UPDATE and one INSERT statement, the transaction uses three database requests.
Atomicity
Atomicity requires that all operations (SQL requests) of a transaction be completed; if not, the transaction is aborted.
Consistency
Consistency indicates the permeance of the database’s consistent state. A transaction takes a database from one consistent state to another. When a transaction is completed, the database must be in a consistent state; if any of the transaction parts violates an integrity constraint, the entire transaction is aborted.
Isolation
Isolation means that the data used during the execution of a transaction cannot be used by a second transaction until the first one is completed.
Durability
Durability ensures that once transaction changes are done and committed, they cannot be undone or lost, even in the event of a system failure.
Serializability
Serializability ensures that the schedule for the concurrent execution of the transactions yields consistent results. This property is important in multiuser and distributed databases in which multiple transactions are likely to be executed concurrently. Naturally, if only a single transaction is executed, serializability is not an issue.
Transaction log
A DBMS uses a transaction log to keep track of all transactions that update the database.
Concurrency control
Coordinating the simultaneous execution of transactions in a multiuser database system is known as concurrency control. The objective of concurrency control is to ensure the serializability of transactions in a multiuser database environment.
Lost update
The lost update problem occurs when two concurrent transactions are updating the same data element and one of the updates is lost (overwritten by the other transaction).
Uncommitted data
The phenomenon of uncommitted data occurs when two transactions are executed concurrently and the first transaction is rolled back after the second transaction has already accessed the uncommitted data - thus violating the isolation property of transactions.
Inconsistent retrievals
Inconsistent retrievals occur when a transaction accesses data before and after one or more other transactions finish working with such data. E.g. an inconsistent retrieval would occur if transaction T1 calculated some summary (aggregate) function over a set of data while another transaction (T2) was updating the same data. The problem is that the transaction might read some data before they are changed and other data after they are changed, thereby yielding inconsistent results.
Scheduler
The scheduler is a special DBMS process that establishes the order in which the operations are executed within concurrent transactions. The scheduler interleaves the execution of database operations to ensure serializability and isolation of transactions.
Serializable schedule
The scheduler’s main job is to create a serializable 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 one after another.
Lock
A lock guarantees exclusive use of a data item to a current transaction.
Pessimistic locking
The use of locks based on the assumption that conflict between transactions is likely is often referred to as pessimistic locking.
Lock manager
All lock information is handled by a lock manager, which is responsible for assigning and policing the locks used by the transactions.
Lock granularity
Lock granularity indicates the level of lock use. Locking can take place at the following levels: database, table, page, row, or even field (attribute).
Database-level lock
In a database-level lock, the entire database is locked, thus preventing the use of any tables in the database by transaction T2 while transaction T1 is being executed.
Table-level lock
In a table-level lock, the entire table is locked, preventing access to any row by transaction T2 while transaction T1 is using the table. If a transaction requires access to several table, each table may be locked.
Page-level lock
In a page-level lock, the DBMS locks an entire diskpage. A diskpage, or page, is the equivalent of a diskblock, which can be described as a directly addressable section of a disk.
Row-level lock
A row-level lock is much less restrictive than the locks previously discussed. The DBMS allows concurrent transactions to access different rows of the same table even when the rows are located on the same page. Although the row-level locking approach improves the availability of data, its management requires high overhead because a lock exists for each row in a table of the database involved in a conflicting transaction.
Field-level lock
The field-level lock allows concurrent transactions to access the same row as long as they require the use of different fields (attributes) within that row.
Binary lock
A binary lock has only two states: locked (1) and unlocked (0). If an object such as a database, table, page or row 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
An exclusive lock exists when access is reserved specifically for the transaction that locked the object.
Shared lock
A shared lock exists when concurrent transactions are granted read access on the basis of a common lock. A shared lock produces no conflict as long as all the concurrent transactions are read-only.
Mutual exclusive rule
The exclusive lock is granted if and only if no other locks are held on the data item (this condition is known as the mutual exclusive rule: only one transaction at a time can own an exclusive lock on an object).
Deadlock/deadly embrace
A deadlock occurs when two transactions wait indefinitely for each other to unlock data.
Two-phase locking (2PL)
Two-phase locking defines how transactions acquire and relinquish locks. Two-phase locking guarantees serializability, but it does not prevent deadlocks.
Time stamping
The time stamping approach to scheduling concurrent transactions assigns a global, unique time stamp to each transaction. The time stamp produces an explicit order in which transactions are submitted to the DBMS. Uniqueness ensures that no equal time stamp values can exist, and monotonicity ensures that time stamp values always increase.
Wait/die scheme
In short, in the wait/die scheme, the older transaction waits for the younger one to complete and release its locks.
Wound/wait scheme
In short, in the wound/wait scheme, the older transaction rolls back the younger transaction and reschedules it.
Optimistic approach
The optimistic approach is based on the assumption that the majority of database operations do not conflict. The optimistic approach requires neither locking nor time stamping techniques. Instead, a transaction is executed without restrictions until it is committed.
Dirty read
Dirty read: a transaction can read data that is not yet committed.
Nonrepeatable read
Nonrepeatable 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.
Phantom read
Phantom 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.
Read Uncommitted
Read Uncommitted will read uncommitted data from other transactions. At this isolation level, the database does not place locks on the data, which increases transaction performance but at the cost of data consistency.
Read Committed
Read Committed forces transaction to read only committed data. This is the default mode of operation for most databases.
Repeatable Read
The Repeatable Read 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 it.
Serializable
The Serializable isolation is the most restrictive level defined by the ANSI SQL standard.
Database recovery
Database recovery restores a database from a given state (usually inconsistent) to a previously consistent state.
Atomic transaction property
Recovery techniques are based on the atomic transaction property: 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.
Write-ahead-log protocal
The write-ahead-log protocol ensures that transaction logs are always written before any database data are actually updated. This protocol ensures that, in case of a failure, the database can later be recovered to a consistent state using the data in the transaction log.
Redundant transaction logs
Redundant transaction logs (several copies of the transaction log) ensure that a physical disk failure will not impair the DBMS’s ability to recover data.
Buffers
Database buffers are temporary storage areas in primary memory used to speed up disk operations. To improve processing time, the DBMS software reads the data from the physical disk and stores a copy of it on a “buffer” in primary memory. When a transaction updates data, it actually updates the copy of the data in the buffer because that process is much faster than accessing the physical disk every time. Later, all buffers that contain updated data are written to a physical disk during a single operation, thereby saving significant processing time.
Checkpoints
Database checkpoints are operations in which the DBMS writes all of its updated buggers in memory (also known as dirty buffers) to disk. While this is happening, the DBMS does not execute any other requests. A checkpoint operation is also registered in the transaction log. As a result of this operation, the physical database and the transaction log will be in sync.
Deferred-write technique
When the recovery procedure uses a deferred-write technique (also called a deferred update), the transaction operations do not immediately update the physical database. Instead, only the transaction log is updated. The database is physically updated only with data from committed transactions, using information from the transaction log.
Write-through technique
When the recovery procedure uses a write-through technique (also called an immediate update), the database is immediately updated by transaction operations during the transaction’s execution, even before the transaction reaches its commit point.
