Ch 10

Question 1

Transaction

Answer 1

A transaction is a logical unit of work that must be entirely completed or entirely aborted; no intermediate states are acceptable.

Question 2

Consistent database state

Answer 2

A consistent database state is one in which all data integrity constraints are satisfied.

Question 3

Database request

Answer 3

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.

Question 4

Atomicity

Answer 4

Atomicity requires that all operations (SQL requests) of a transaction be completed; if not, the transaction is aborted.

Question 5

Consistency

Answer 5

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.

Question 6

Isolation

Answer 6

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.

Question 7

Durability

Answer 7

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

Question 8

Serializability

Answer 8

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.

Question 9

Transaction log

Answer 9

A DBMS uses a transaction log to keep track of all transactions that update the database.

Question 10

Concurrency control

Answer 10

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.

Question 11

Lost update

Answer 11

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).

Question 12

Uncommitted data

Answer 12

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.

Question 13

Inconsistent retrievals

Answer 13

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.

Question 14

Scheduler

Answer 14

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.

Question 15

Serializable schedule

Answer 15

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.

Question 16

Lock

Answer 16

A lock guarantees exclusive use of a data item to a current transaction.

Question 17

Pessimistic locking

Answer 17

The use of locks based on the assumption that conflict between transactions is likely is often referred to as pessimistic locking.

Question 18

Lock manager

Answer 18

All lock information is handled by a lock manager, which is responsible for assigning and policing the locks used by the transactions.

Question 19

Lock granularity

Answer 19

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

Question 20

Database-level lock

Answer 20

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.

Question 21

Table-level lock

Answer 21

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.

Question 22

Page-level lock

Answer 22

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.

Question 23

Row-level lock

Answer 23

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.

Question 24

Field-level lock

Answer 24

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.

Question 25

Binary lock

Answer 25

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.

Question 26

Exclusive lock

Answer 26

An exclusive lock exists when access is reserved specifically for the transaction that locked the object.

Question 27

Shared lock

Answer 27

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.

Question 28

Mutual exclusive rule

Answer 28

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).

Question 29

Deadlock/deadly embrace

Answer 29

A deadlock occurs when two transactions wait indefinitely for each other to unlock data.

Question 30

Two-phase locking (2PL)

Answer 30

Two-phase locking defines how transactions acquire and relinquish locks. Two-phase locking guarantees serializability, but it does not prevent deadlocks.

Question 31

Time stamping

Answer 31

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.

Question 32

Wait/die scheme

Answer 32

In short, in the wait/die scheme, the older transaction waits for the younger one to complete and release its locks.

Question 33

Wound/wait scheme

Answer 33

In short, in the wound/wait scheme, the older transaction rolls back the younger transaction and reschedules it.

Question 34

Optimistic approach

Answer 34

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.

Question 35

Dirty read

Answer 35

Dirty read: a transaction can read data that is not yet committed.

Question 36

Nonrepeatable read

Answer 36

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.

Question 37

Phantom read

Answer 37

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.

Question 38

Read Uncommitted

Answer 38

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.

Question 39

Read Committed

Answer 39

Read Committed forces transaction to read only committed data. This is the default mode of operation for most databases.

Question 40

Repeatable Read

Answer 40

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.

Question 41

Serializable

Answer 41

The Serializable isolation is the most restrictive level defined by the ANSI SQL standard.

Question 42

Database recovery

Answer 42

Database recovery restores a database from a given state (usually inconsistent) to a previously consistent state.

Question 43

Atomic transaction property

Answer 43

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.

Question 44

Write-ahead-log protocal

Answer 44

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.

Question 45

Redundant transaction logs

Answer 45

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.

Question 46

Buffers

Answer 46

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.

Question 47

Checkpoints

Answer 47

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.

Question 48

Deferred-write technique

Answer 48

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.

Question 49

Write-through technique

Answer 49

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.