Database Systems: Chapter 10 - Transactions

Front 1

What is a "transaction"?

Back 1

simply, any action that reads from and/or writes to a database
-may consist of a simple SELECT statement or any combination of SELECT, UPDATE, and INSERT statements
-A transaction is a logical unit of work that must be entirely completed/entirely aborted
no intermediate states are acceptable
If any fail, the entire transaction is rolled back to the original database state that existed before the transaction started. A successful transaction changes the database from one consistent state to another.

Front 2

What is a consistent database state?

Back 2

A consistent db state is one where all data integrity constraints r satisfied
To ensure consistency, every transaction must begin with the db in a known consistent state. If db not in a consistent state, the transaction will yield an inconsistent db that violates its integrity/business rules
All transactions controlled and executed by the DBMS to guarantee database integrity.

Front 3

What is a database request?

Back 3

the equivalent of a single SQL statement in an application program/transaction
Eg if a transaction has two UPDATE statements and one INSERT statement ->3 database requests
In turn, each database request generates several I/O operations that read/write to physical storage media.

Front 4

What are the required properties of transactions?

Back 4

Each transaction must display atomicity, consistency, isolation, and durability
sometimes referred to as the ACID test
Also, when executing multiple transactions, the DBMS must schedule the concurrent execution of the transaction's operations
The schedule must exhibit the property of serializability.

Front 5

What is Atomicity?

Back 5

Atomicity requires all operations (SQL requests) of a transaction be completed, if not, the transaction is aborted. Eg If transaction Tl has 4 SQL requests, all 4 requests must be successfully completed otherwise, entire transaction is aborted
In other words, a transaction is treated as a single, indivisible, logical unit of work.

Front 6

What is Consistency?

Back 6

Consistency indicates the permanence of the database's consistent state
A transaction takes a database from one consistent state to another consistent state
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.

Front 7

What is Isolation?

Back 7

Isolation means the data used during the execution of a transaction cannot be used by a second transaction until the first one is completed
This is particularly useful in multiuser db environments due to several users accessing/updating the db at the same time.

Front 8

What is Durability?

Back 8

Durability ensures that once transaction changes are done (committed), they cannot be undone or lost, even in the event of a system failure.

Front 9

What is Serializability ?

Back 9

Serializability ensures the schedule for the concurrent execution of the transactions yields consistent results
important in multiuser and distributed databases, where multiple transactions are likely to be executed concurrently.

Front 10

How are transactions managed with SQL?

Back 10

ANSI has defined standards that govern SQL database transactions
Transaction support is provided by two SQL statements: COMMIT and ROLLBACK
The ANSI standards require that when a transaction sequence is initiated, it must continue through all succeeding SQL statements until one of following 4 occurs: A COMMIT statement is reached, -> all changes are permanently recorded to db and the COMMIT statement automatically ends the transaction
A ROLLBACK statement is reached -> all changes are aborted and the database is rolled back to its previous consistent state
The end of a program is successfully reached, in which case all changes are permanently recorded (equivalent to COMMIT) or program is abnormally terminated -> the changes made in the database are aborted and rolled back to its previous consistent state (equivalent to ROLLBACK.

Front 11

What is the transaction log?

Back 11

A DBMS uses a transaction log to track all transactions that update the database. This info used by the DBMS for a recovery requirement triggered by a ROLLBACK statement, a program with abnormal termination, or a system failure. Some RDBMSs use the transaction log to recover a database forward to a currently consistent state. After a server failure for eg this behavior is required for transactional correctness , typical of any transactional DBMS.

Front 12

What does the transaction log store?

Back 12

The transaction log stores: a record for the beginning of the transaction
for each transaction statement: The type of operation (update, delete, insert)
names of the objects affected by the transaction (eg table)
The "before" and "after" values for fields being updated, pointers to the previous and next transaction log entries for the same transaction. The ending (COMMIT) of the transaction.

Front 13

What is concurrency control?

Back 13

The coordination of 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. Concurrency control is important because the simultaneous execution of transactions over a shared database can create several data integrity and consistency problems. The three main problems are lost updates, uncommitted data, and inconsistent retrievals.

Front 14

What are lost updates?

Back 14

The lost update 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).

Front 15

What is uncommitted data?

Back 15

Uncommitted data 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.

Front 16

What are inconsistent retrievals?

Back 16

Inconsistent retrievals occur when a transaction accesses data before and after another transaction(s) finish working with such data. For example, an inconsistent retrieval would occur if transaction T1 calculates some summary (aggregate) function over a set of data while another transaction (T2) is updating the same data.

Front 17

What is the scheduler?

Back 17

a special DBMS process that establishes the order in which the operations within concurrent transactions are executed
The scheduler interleaves the execution of database operations to ensure serializability and isolation of transactions. The scheduler bases its actions on concurrency control algorithms, such as locking or time stamping methods
not all transactions are serializable! Generally, transactions that are not serializable are executed on a first-come, first-served basis by the DBMS. The scheduler's main job is to create a serializable schedule of a transaction's operations.

Front 18

What is a serializable schedule?

Back 18

A serializable schedule is a schedule of a transaction's operations in which the interleaved execution of the transactions (T1, T2, T3, etc.) yields the same results as if the transactions were executed in serial order (one after another).

Front 19

What methods are proposed to schedule conflicting operations in concurrent transactions?

Back 19

Those methods have been classified as locking, time stamping, and optimistic. Locking methods are used most frequently.

Front 20

What is a lock?

Back 20

A lock guarantees exclusive use of a data item to a current transaction. In other words, transaction T2 does not have access to a data item that is currently being used by transaction T1. A transaction acquires a lock prior to data access. The lock is released (unlocked) when the transaction is completed so that another transaction can lock the data item for its exclusive use. Data consistency cannot be guaranteed during a transaction
the database might be in a temporary inconsistent state when several updates are executed. Therefore, locks are required to prevent another transaction from reading inconsistent data.

Front 21

What is a lock manager?

Back 21

Most multiuser DBMSs automatically initiate and enforce locking procedures. All lock information is managed by a lock manager, which is responsible for assigning and policing the locks used by the transactions.

Front 22

What is lock granularity?

Back 22

Lock granularity indicates the level of lock use. Locking can take place at the following levels: database, table, page, row, or even field (attribute).

Front 23

What is a database level lock?

Back 23

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 Tl is being executed. This level of locking is good for batch processes, but it is unsuitable for multiuser DBMSs.

Front 24

What is a table level lock?

Back 24

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 tables, each table may be locked. However, two transactions can access the same database as long as they access different tables. Table-level locks, while less restrictive than database-level locks, cause traffic jams when many transactions are waiting to access the same table, particularly if the lock forces a delay when different transactions require access to different parts of the same table, when the transactions would not interfere with each other
table-level locks are not suitable for multiuser DBMSs.

Front 25

What is a page level lock?

Back 25

In a page-level lock, the DBMS will lock 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. A page has a fixed size, such as 4K, 8K, or 16K. For example, if you want to write only 73 bytes to a 4K page, the entire 4K page must be read from disk, updated in memory, and written back to disk. A table can span several pages, and a page can contain several rows of one or more tables. Page- level locks are currently the most frequently used multi-user DBMS locking method.

Front 26

What is a row level lock?

Back 26

A row-level lock is much less restrictive than the locks discussed earlier. 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. Modern DBMS automatically escalate a lock from row level to page level lock when the application session requests multiple locks on the same page.

Front 27

What is a field level lock?

Back 27

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. Although field-level locking clearly yields the most flexible multiuser data access, it is rarely implemented in a DBMS because it requires an extremely high level of computer overhead and because the row-level lock is much more useful in practice.

Front 28

What are the different lock types?

Back 28

Regardless of the level of locking, the DBMS may use different lock types: binary or shared/exclusive.

Front 29

What is a binary lock?

Back 29

A binary lock has only two states: locked (1) or unlocked (0). If an object-that is, 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. Every database operation requires that the affected object be locked. As a rule, a transaction must unlock the object after its termination. Therefore, every transaction requires a lock and unlock operation for each data item that is accessed. Such operations are automatically managed and scheduled by the DBMS
the user does not need to be concerned about locking or unlocking data items. (Every DBMS has a default locking mechanism. If the end user wants to override the default, the LOCK TABLE and other SQL commands are available for that purpose.)

Front 30

What are shared/exclusive locks?

Back 30

the labels "shared" and "exclusive" indicate the nature of the lock. An exclusive lock exists when access is reserved specifically for the transaction that locked the object. The exclusive lock must be used when the potential for conflict exists
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. A shared lock is issued when a transaction wants to read data from the database and no exclusive lock is held on that data item. An exclusive lock is issued when a transaction wants to update (write) a data item and no locks are currently held on that data item by any other transaction. Using the shared/exclusive locking concept, a lock can have three states: unlocked, shared (read), and exclusive (write). Two transactions conflict only when at least one of them is a WRITE transaction. Because the two READ transactions can be safely executed at once, shared locks allow several READ transactions to read the same data item concurrently.
only one transaction at a time can own an exclusive lock on the same object. Although the use of shared locks renders data access more efficient, increases the lock manager's overhead, for several reasons: The type of lock held must be known before a lock can be granted. Three lock operations exist: READ_LOCK (to check the type of lock), WRITE_LOCK (to issue the lock), and UNLOCK (to release the lock). The schema has been enhanced to allow a lock upgrade (from shared to exclusive) and a lock downgrade (from exclusive to shared). Although locks prevent serious data inconsistencies, they can lead to two major problems: The resulting transaction schedule might not be serializable. The schedule might create deadlocks.

Front 31

What is two-phase locking?

Back 31

Two-phase locking defines how transactions acquire and relinquish locks. Two- phase locking guarantees serializability, but it does not prevent deadlocks. The two phases are: 1. A growing phase, in which a transaction acquires all required locks without unlocking any data. Once all locks have been acquired, the transaction is in its locked point. 2. A shrinking phase, in which a transaction releases all locks and cannot obtain any new lock. The two-phase locking protocol is governed by the following rules: Two transactions cannot have conflicting locks. No unlock operation can precede a lock operation in the same transaction. No data are affected until all locks are obtained-that is, until the transaction is in its locked point.

Front 32

What aqre the possible effects of two-phase locking?

Back 32

Two-phase locking increases the transaction processing cost and might cause additional undesirable effects. One undesirable effect is the possibility of creating deadlocks.

Front 33

What is a deadlock?

Back 33

A deadlock occurs when two transactions wait indefinitely for each other to unlock data. For example, a deadlock occurs when two transactions, T1 and T2, exist in the following mode: T1 = access data items X and Y T2 = access data items Y and X 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 a deadly embrace.

Front 34

What are the basic techniques to control deadlocks?

Back 34

The three basic techniques to control deadlocks are: Deadlock prevention: A transaction requesting a new lock is aborted when there is the possibility that a deadlock can occur
all changes are rolled back and all locks obtained by the transaction are released. The transaction is then rescheduled for execution. Deadlock prevention works because it avoids the conditions that lead to deadlocking. Deadlock detection: The DBMS periodically tests the database for deadlocks. If found, one of the transactions (the "Victim") is aborted (rolled back and restarted) and the other transaction continues. Deadlock avoidance: he transaction must obtain all of the locks it needs before it can be executed. This avoids the rollback of conflicting transactions by requiring that locks be obtained in succession. However, the serial lock assignment required increases action response times.

Front 35

What factors influence which deadlock control method is used?

Back 35

The choice of the best deadlock control method to use depends on the database environment. For example, if the probability of deadlocks is low, deadlock detection is recommended. However, if the probability of deadlocks is high, deadlock prevention is recommended. If response time is not high on the system's priority list, deadlock avoidance might be employed. All current DBMSs support deadlock detention in transactional databases, while some DBMSs use a blend of prevention and avoidance techniques for other types of data, such as data warehouses or XML data.

Front 36

What is the time stamping method?

Back 36

The time stamping approach to scheduling concurrent transactions assigns a global, unique time stamp to each transaction. This produces an explicit order in which transactions are submitted to the DBMS. Time stamps must have two properties: uniqueness and monotonicity. Uniqueness ensures that no equal time stamp values can exist, and monotonicity ensures that time stamp values always increase. All database operations (READ and WRITE) within the same transaction must have the same time stamp. The DBMS executes conflicting operations in time stamp order, thereby ensuring serializability of the transactions. If two transactions conflict, one is stopped, rolled back, rescheduled, and assigned a new time stamp value. The disadvantage of the time stamping approach is that each value stored in the database requires two additional time stamp fields: one for the last time the field was read and one for the last update. This increases memory needs and processing overhead.

Front 37

What are the two schemes for time stamping?

Back 37

the wait/die scheme and the wound/wait scheme.

Front 38

What is the wait/die scheme?

Back 38

Using the wait/die scheme: • If the transaction requesting the lock is the older of the two transactions, it will wait until the other transaction is completed and the locks are released. • If the transaction requesting the lock is the younger of the two transactions, it will die (roll back) and is rescheduled using the same time stamp. In short, in the wait/die scheme, the older transaction waits for the younger to complete and release its locks.

Front 39

What is the wound/wait scheme?

Back 39

n the wound/wait scheme: • If the transaction requesting the lock is the older of the two transactions, it will preempt (wound) the younger transaction (by rolling it back). T1 preempts T2 when T1 rolls back T2. The younger, preempted transaction is rescheduled using the same time stamp. • If the transaction requesting the lock is the younger of the two transactions, it will wait until the other transaction is completed and the locks are released. In short, in the wound/wait scheme, the older transaction rolls back the younger transaction and reschedules it.

Front 40

What is the optimistic approach?

Back 40

based on the assumption that the majority of the database operations do not conflict. Requires neither locking nor time stamping techniques. Instead, a transaction is executed without restrictions until it is committed. Each transaction moves through two or three phases, referred to as read, validation, and write. - The read phase: the transaction reads the database, executes the needed computations, and makes the updates to a private copy of the database values. All update operations of the transaction are recorded in a temporary update file, which is not accessed by the remaining transactions
During the validation phase, the transaction is validated to ensure that the changes made will not affect the integrity and consistency of the database. If the validation test is positive, the transaction goes to the write phase. If the validation test is negative, the transaction is restarted and the changes are discarded
During the write phase, the changes are permanently applied to the database
The optimistic approach is acceptable for most database systems that require few update transactions.

Front 41

What is database recovery?

Back 41

Database recovery restores a database from a given state (usually inconsistent) to a previously consistent state. 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. If, for some reason, any transaction operation cannot be completed, the transaction must be aborted and any changes to the database must be rolled back (undone). In short, transaction recovery reverses all of the changes that the transaction made to the database before the transaction was aborted.

Front 42

What are examples of critical events?

Back 42

Critical events can cause a database to become in-operational and compromise data integrity. Examples of are:
-Hardware/software failures (hard disk media failure, a bad capacitor on a motherboard, or a failing memory bank).
- Other causes include application program o/s errors that cause data to be overwritten, deleted, or lost.
-Human-caused incidents : categorized as unintentional or intentional. An unintentional failure is caused by carelessness by end-users. Such errors include deleting the wrong rows from a table, pressing the wrong key on the keyboard, or shutting down the main database server by accident.
-Intentional events including security threats eg hackers trying to gain access to data resources and virus attacks caused by disgruntled employees trying to damage the company.
- Natural disasters. This category includes fires, earthquakes, floods, and power failures.

Front 43

What is transaction recovery?

Back 43

Database transaction recovery uses data in the transaction log to recover a database from an inconsistent state to a consistent state.

Front 44

What are the important concepts of transaction recovery?

Back 44

- The write-ahead-log protocol
- Redundant transaction logs
- Database buffers &
- Database checkpoints

Front 45

What is the write-ahead-log protocol?

Back 45

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.

Front 46

What are redundant transaction logs ?

Back 46

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.

Front 47

What are database checkpoints?

Back 47

Database checkpoints are operations in which the DBMS writes all of its updated 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. This synchronization is required because update operations update the copy of the data in the buffers and not in the physical database. Checkpoints are automatically scheduled by the DBMS several times per hour.

Front 48

What techniqies are used for transaction recovery procedures?

Back 48

Transaction recovery procedures generally make use of deferred-write and write-through techniques.

Front 49

What is the deferred-write technique?

Back 49

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 after the transaction reaches its commit point, using information from the transaction log. If the transaction aborts before it reaches its commit point, no changes (no ROLLBACK or undo) need to be made to the database because the database was never updated.

Front 50

What is the write-through technique?

Back 50

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. If the transaction aborts before it reaches its commit point, a ROLLBACK or undo operation needs to be done to restore the database to a consistent state. In that case, the ROLLBACK operation will use the transaction log "before" values.