CONCURRENCY CONTROL WITH LOCKING METHODS

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

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.

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

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.

Table Level

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.

Page Level

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.

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 multiuser DBMS locking method.

Row Level

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.

Field Level�

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

Lock Types

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

Binary Locks

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

​

• the user does not need to be concerned about locking or unlocking data items.
• i.e 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.)

Shared/Exclusive Locks

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

​

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

Two-Phase Locking to Ensure Serializability

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

Deadlocks

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

The three basic techniques to control deadlocks are

• Deadlock prevention
• Deadlock detection
• Deadlock avoidance

CONCURRENCY CONTROL WITH TIME STAMPING METHODS

• The time stamping approach to scheduling concurrent transactions assigns a global, unique time stamp to each transaction.
• The time stamp value produces an explicit order in which transactions are submitted to the DBMS.
• Timestamps must have two properties: uniqueness and monotonicity.
• Uniqueness ensures that no equal time stamp valuescan exist, and monotonicity1 ensures that time stamp values always increase.

Wait/Die and Wound/Wait Schemes

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.

​

i.E in the wait/die scheme, the older transaction waits for the younger to complete and release its locks.

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.

What is the need for recovery?

What are the reasons for a transaction to fail?

1. A computer failure
2. A transaction or system error:
3. Local errors or exception conditions detected by the transaction:
4. Concurrency control enforcement
5. Disk failure
6. Physical problems and catastrophes (diaster)

​

​

​

32

Crash recovery

Phases of ARIES�Algorithm for Recovery and Isolation Exploiting Semantics (ARIES)��

• Analysis
• Redo
• Undo

​

Principles of ARIES

• Write Ahead logging.
• Repeating history during redo.
• Logging changes during undo.