Transactions/concurrency
Database schema before class: database_day15_end_of_class.sql
soccer_unnormalized.mwb (practice from class)
soccer_normalized.mwb (forward engineer to create tables)
soccer.sql (fills tables created by soccer_normalized.mwb)
Slides from class
Code from class
Database notes
ACID
Acronym for Atomic, Consistent, Isolated, Durable.
Atomic
The transaction property that requires all parts of a transaction to be traded as a single, indivisible, logical unit of work. All parts of the transactions must complete or the entire transaction is aborted.
Consistent
A database condition in which all data integrity constraints are satisfied. To ensure consistency of a database, every transaction must begin in a consistent state. If not, the transaction will yield an inconsistent database that violates its integrity and business rules.
Isolation
A database transaction property in which a data item used by one transaction is not available to other transactions until the first transaction ends.
Durability
The transaction property that ensures that once transaction changes are done and committed, they cannot be undone or lost, even in the event of a system failure.
Serializability
A property in which the selected order of concurrent transaction operations creates the same final database state that would have been produced if the transactions had been executed in a serial fashion.
Transaction log
A feature used by the DBMS to keep track of all transaction operations that update the database. The information stored in the transaction log is used by the DBMS for recovery or rollback purposes.
Concurrency control
A feature that coordinates the simultaneous execution of transactions in a multiprocessing database system while preserving data integrity.
Lost update
A concurrency control problem in which a data update is lost during the concurrent execution of transactions.
Uncommitted data
A concurrency problem in which a transaction accesses uncommitted data from another transaction.
Inconsistent retrievals
A concurrency control problem that arises when a transaction calculating summary (aggregate) functions over a set of data while another transaction updates the data, yielding erroneous results.
Scheduler
The DBMS component that establishes the order in which concurrent transaction operations are executed. The scheduler interleaves the execution of database operations in a specific sequence to ensure serializability.
Serializable schedule
In transaction management, a schedule of operations in which the interleaved execution of the transactions yields the same results as if they were executed in serial order.
Lock
A method that guarantees unique use of a data item in a particular transaction operation. A transaction requires a lock prior to data access; the lock is released after the operation's execution to enable other transactions to lock the data item for their own use.
Lock manager
A DBMS component that is responsible for assigning and releasing locks.
Pessimistic locking
The use of locks based on the assumption that conflict between transactions is likely.
Optimistic locking
The use of locks based on the assumption that most transactions do not conflict.
Lock granularity
The level of lock use. Locking can take place at the database, table, page, row, or field levels.
Database-level lock
A lock that restricts database access to the owner of the lock and allows only one process at a time to access the database. This locks works well for batch processes, but is not well suited for concurrent database use.
Table-level lock
A locking scheme that only allows one transaction at a time to access a table. A table-level lock locks an entire table, preventing access to any row by another transaction.
Page-level lock
A locking scheme that locks an entire disk page or section of a disk. A disk page can contain data from one or more rows and from one or more tables.
Disk page
In permanent storage, the equivalent of a disk block, which is a directly accessible section of a disk. A disk page has a fixed size, typically 4K, 8L, or 16K.
Row-level lock
A lock which allows concurrent transactions to access different rows of the same table, even when the rows are on the same disk page.
Field-level lock
A lock that allows concurrent transactions to access the same rows as long as they required the user of different fields (attributes) within that row. This lock is the most flexible, but requires a high level of overhead and is not commonly used.
Binary lock
A lock that has only two states: locked (1), or unlocked (0). If a data item is locked by a transaction, no other transactions can use that data item. This is a mutex from CS10.
Exclusive lock
Issued when a transaction requests permission to update a data item and no other locks are held on that data item by another transaction. An exclusive lock does not allow other transaction to access (even to read) the data item.
Shared lock
Issued when a transaction requests permission to to read data from a database and no exclusive locks are held o nthe data by another transaction. A shared lock allows other transactions to read the data item.
Mutual exclusive rule
A condition in which one transaction at a time can own an exclusive lock on the same data item.
Deadlock
A condition in which two or more transactions wait indefinitely for the other to release the lock on a previously locked data item. Also called deadly embrace.
Two-phase locking (2PL)
A set of rules that governs how transaction acquire and release locks. Two-phase locking guarantees serializability, but doe so prevent deadlocks. The two-phase locking protocol is divided into two steps: (1) a growing phase where the transaction acquires the locks it needs without unlocking any existing data locks. Once all locks have been acquired, the transaction is in its locked point, and (2) A shrinking phase where the transaction releases all the locks and cannot obtain a new lock until all locks have been released.
Database recovery
The process of restoring a database to a previous consistent state.
Write-ahead-log protocol
A process that ensures transaction logs are written to permanent storage before any database data is actually updated.
Redundant transaction logs
Multiple copies of the transaction log kept by the DBMS to ensure that the physical failure of a disk will not impair the ability to recover data.
Checkpoint
An operation in which the DBMS writes all of its updates to disk.