Concurrency Control

Back to Research Page

Tao Guo & Qingxiu Luo

Concurrency Control and Transaction Recovery Introduction: Interactions among transactions can cause the database to become inconsistent, even when the transactions individually preserve correctness of the state, and there is no system failure. Thus the order in which the individual steps of di fferent transactions occur needs to be regulated in some manner. The function of controlling these steps is given to the scheduler component of the DBMS, and the general process of assuring that transactions preserve consistency when executing simultaneou sly is called concurrency control. This project discusses two topics: (1) The topic of concurrency control in relational database management systems and how transaction journaling can be used to recover lost data or restore an inconsistent database to a consistent state. (2) The topic of transaction processing.

Concurrency Control and Transaction Recovery 1. What’s concurrency control? Concurrency control deals with preventing concurrently running processes from improperly inserting, deleting,

or updating the same data. Concurrency control is maintained through two mechanisms: Transactions and Locks. 2. What’s transactions? Transactions are a mandatory facility for maintaining the integrity of a database while running multiple concurrent operations. A transaction is a logical unit of work. It is both the unit of work and the unit of recovery. The s tatements nested within a transaction must either all happen or none happen. Transactions are atomic: there is no such thing as a partial transaction. A set of transactions is said to be serializable if and only if it produces the same result as some arbi trary serial execution of those same transactions for arbitrary input. A set of transactions can be correct only if it is serializable. The Teradata RDBMS supports both ANSI transaction semantics and Teradata Transaction semantics. All ANSI transactions are implicit while Teradata mode transactions can be either implicit or explicit. Multistatement requests and macros are examples of implicit transactions. The type of transactions passed by embedded SQL applications are examples of explicit transactions. In ANSI mode the entire transaction is rolled back if the current request: l Results in a deadlock l Performs a DDL statement that aborts l Executes an explicit ROLLBACK or ABORT statement. The ABORT and ROLLBACK statements are accepted in ANSI mode, including conditional forms of those statements. If an error is detected for either a single or multi-statement request, only that request is rolled back, and the transaction remains open, except in

special circumstances. Application-initiated asynchronous aborts also cause full transaction rollback in the ANSI environment. Consider the following COBOL program with embedded SQL and transactions (no COBOL code is shown, but the embedded SQL code is of the type required by COBOL SQL programs). EXEC SQL BEGIN TRANSACTION END-EXEC EXEC SQL DELETE FROM Employee WHERE Name = ‘Smith T’ END-EXEC EXEC SQL UPDATE Department SET EmpCount=EmpCount-1 WHERE DeptNo=500 END-EXEC EXEC SQL END TRANSACTION END-EXEC

If an error were to occur during the processing of either the DELETE or UPDATE statement within the BEGIN TRANSACTION and END TRANSACTION statements, both Employee and Department tables would be restored to their sta tes before the transaction began. When an error occurs during a Teradata transaction, the entire transaction is rolled back. 3.What’s lock? A lock is a means of claiming usage rights on some resource. There can be several different types of resources that can be locked and several different ways of locking those resources. Most locks used on Teradata resources are locked automatically by default. The Teradata lock manager implicitly locks the following objects: Database, Table, View and Row hash. User can apply four different levels of locking on Teradata resources: Exclusive, Write, Read and Access. The Teradata R DBMS applies most of its locks automatically. The Teradata lock manager implicitly locks the following objects: Object Locked

Description

Database

Locks rows of all tables in the database

Table

Locks all rows in the table and any index and fallback subtables

View

Locks all underlying tables in the view

Row hash

Locks the primary copy of a row (all rows that share the same hash code)

Users can apply four different levels of locking on Teradata resources. The following table explains these levels. Lock Type

Read

Description

Execlusive

The requester has exclusive rights to the locked resource. No other process can read from, write to, or access the locked resource in any way. Exclusive locks are generally only necessary when structural changes are b eing made to the database.

Write

The requester has exclusive rights to the locked resource except for readers not concerned with data consistency. The requester has exclusive rights to the locked resource while it is reading that resource. Read locks ensure consistency during read operations such as those that occur during a SELECT statement. Several users can hold Read locks on a resource, during which no modification of that resource is permitted.

Access

The requester does not care about the consistency of the data while it is accessing the

database. An access lock permits modifications on the underlying data while the SELECT operation is in progress.

The following table illustrates how the different locks are applied for various types of SQL statements: Type of SQL Statement

Lock Level by UPI/NUPI/USI

Access Type

Locking Mode

NUSI/Full Table Scan

SELECT

Row Hash

Table

Read

UPDATE

Row Hash

Table

Write

DELETE

Row Hash

Table

Write

INSERT

Row Hash

Not applicable

Write

Not applicable

Database

Exclusive

Not applicable

Table

Exclusive

CREATE DATABASE DROP DATABASE MONIFY DATABASE CREATE TABLE DROP TABLE ALTER TABLE

4.What’s deadlock?

A deadlock occurs when transaction1 places a lock on resources A, then needs to lock resources B. But resource B has already been locked by transaction 2, which in turn needs to place a lock on resource A. This state of affairs is called a deadlock or a deadly embrace. The Teradata RDBMS resolves deadlocks by aborting one of the transactions. If the transaction originated from BTEQ, then BTEQ resubmits it. Any other client software may pr may not resubmit the transaction. 5. Transaction Recovery Unscheduled restarts occur for one of the following reasons: l AMP or disk failure l Software failure l Parity error Transaction recovery describes how the Teradata RDBMS restarts itself after a system or media failure. Two types of automatic recovery of transactions can occur when an unscheduled restart occurs: l Single transaction recovery l RDBMS recovery The following table details when these two automatic recovery mechanisms take place: This Recovery Type

Happens When The RDBMS aborted a single transaction because of:

l Transaction deadlock timeout Single transaction

l User error l User-initiated abort command l An inconsistent data table l Unavailable resources for parsing Single transaction recovery uses the transient journal to effect its data restoration

RDBMS

A RDBMS restart is caused by: l Hardware failure l Software failure l User command

6. Two-Phase Commit Protocol Two-phase commit (2PC) is a protocol for assuring concurrency of data in multiple databases in which each participant database manager votes to either commit or abort the changes. The participants wait before committing the chan ge until it is known that all participants can commit. By voting to commit, the participant guarantees that it can either commit or rollback its part of the transaction, even if it crashes before receiving the result of the vote. The 2PC protocol allows C ICS and IMS applications to be developed that can update one or more Teradata RDBMS databases and/or databases under some other DBMS in a synchronized manner. The result is that all updates requested in a defined unit of work will either succeed or fail.< /P>

Transaction Processing This topic discusses how the Teradata RDBMS processes transactions. Topics discussed include: l

Lock Manager

• Locking Modes l

DDL Statements, DCL Statements, and Locks

l

DML Statements and Locks

l

Blocked Requests

l

Deadlock Detection and Resolution

l

Preventing Deadlocks

l

Transaction Semantics: Operating in ANSI or Teradata Mode 1.Lock Manager

Introduction 5

Any number of users and applications can simultaneously access data stored in a Teradata RDBMS. The Teradata Lock Manager imposes concurrency control by locking the data being accessed by each transaction and unlocking the data when the transaction completes. This control ensures that the data remains consistent for all users. The Lock Manager implicitly locks an object at the following levels: Database, Table, View And Row. When determining whether to grant a lock, the Lock Manager takes into consideration both the requested locking mode and the object to be locked. It is possible to exhaust Lock Manager

resources; any transaction that requests a lock when the Lo ck Manager resources are exhausted will be aborted. In such cases, row-hash locking for DDL statements can be disabled.

Locks and Concurrency 5

Ordinarily, a SELECT statement that requests a READ lock against a table cannot run FALLBACK statement for the same table. You can specify a READ lock for the CREATE INDEX or ALTER TABLE statement to allow concurrency. If the CREATE INDEX or ALTER TABLE . . .FALLBACK locking modifier specifies WRITE (or if there is no locking modifier), specify an ACCESS lock in your SELECT statement to allow concurrency. Note that the ALTER TABLE operation can be to add FALLBACK only; if other attributes are added, it cannot run concurrently with SELECT. The following example shows two transactions, with the first transaction starting before the second transaction. Consider the following table definition. CREATE TABLE T1 (C1 INT, C2 INT, C3 INT, C4 INT) PRIMARY INDEX (C1);

The following two transactions are running simultaneously. LOCKING T1 FOR READ CREATE INDEX (C3, C4) ON T1; SELECT * FROM T1 WHERE C3 = 124 and C4=93;

Each transaction places a table-level READ lock on table T1. The transactions obtain access to table T1 and run concurrently. Note that the SELECT statement does not recognize the index being created by the CREATE INDEX statem ent. To eliminate concurrency, do the following: LOCKING T1 FOR EXCLUSIVE CREATE INDEX (C3, C4) ON T1;

SELECT * FROM T1 WHERE C3 = 124 and C4=93;

The LOCKING modifier in the first transaction blocks the tablelevel READ lock request on table T1 in the second transaction.

2.Locking Modes 5

Introduction 5

Depending on the request being processed, a certain mode of lock is placed on the object of the request; that is, on the database, table, view, or row. The locking mode determines whether other users can access the targ et object. Locking modes, from most to least restrictive, are as follows: EXCLUSIVE, WRITE, READ, CHECKSUM and ACCESS. The action taken when a requested locking mode is in competition with an existing locking mode is summarized in the following table. Lock Mode Requested

Locking Mode Held None

Access

Read

Write

Exclusive

Access or Lock Checksum Granted

Lock

Lock

Lock

Granted

Granted

Granted

Read

Lock

Lock

Lock

Request Queued*

Request Queued*

Granted

Granted

Granted

Lock

Lock

Request Queued*

Request Queued*

Request Queued*

Granted

Granted

Lock

Request

Request

Request

Request

Write

Exclusive

Request Queued*

Queued*

Queued*

Queued*

Queued*

Granted * If NOWAIT is specified, the transaction aborts instead of queueing.

When Are Locks Released? 5

Locks are released upon completion of an implicit or two-phase commit transaction, and upon completion of the outermost END TRANSACTION statement of an explicit transaction, or the COMMIT or ROLLBACK of an ANSI mode transaction. This occurs regardless of when the user receives the data (because the spool file may exist beyond the end of the transaction).

3.DDL Statements, DCL Statements, and Locks 5

A DDL or DCL statement (for example, GRANT and CREATE, ALTER, or DROP TABLE) causes the Data Dictionary to be updated, and appropriate locks to be placed on system tables while the statement is processing. To improve co ncurrency, DDL and DCL processing uses the finest granularity of locking that is practical, and delays the placing of such locks for as long as possible.

4.DML Statements and Locks 5

In processing DML statements (for example, INSERT, UPDATE), the parser accesses necessary information from the Data Dictionary tables via internal express-request transactions that place Read locks on rows. These locks ar e released when the data is returned to the parser.

Locks applied as a result of DML statement processing are listed in the following table. DML Request

Updated Fields

SELECT

Selection Criteria

Object Locked

Locking Mode

UPI or USI

Row

Read

NUPI

Set of rows

Read

Any other

Table

Read

INSERT…

Primary row

[VALUES]…

Write

INSERT…

Select table

SELECT…

UPI or USI

Row

Read

NUPI

Set of rows

Read

Any other

Table

Read

Insert table

Write

Row

Write

Set of Rows

Write

Table

Write

UPI or USI

Row

Write

NUPI

Set of Rows

Write

Any other

Table

Write

UPDATE

Neither UPI nor USI

UPI or USI

Neither NUPI NUPI nor USI Any other DELETE

5.Single-Statement Transactions 5

When several requests that compete for the same table are submitted as separate, single-statement transactions, the lock manager resolves the locking requirements as follows: Stage

Process

1

Job1 requires a Read lock on Table A. Table A is free, so the lock is granted and Job1 begins.

2

While Job1 is still running, Job2 requires a Write lock. This conflicts with the active Read lock, so the Write lock is denied and Job2 is queued.

3

Job3 requires an Access lock. An Access lock is compatible with both Read and Write locks (i.e., if Job1 completes, releasing the Read lock, Job2 can begin whether or not Job3 still holds the Access lock), so the Acces s lock is granted, and Job3 is allowed to run concurrently with Job1.

4

Job4 requires a Read lock. This conflicts with the queued Write lock, so Job4 is queued behind Job2.

5

Job5 requires an Exclusive lock. An Exclusive lock conflicts with all other locks, so Job5 is queued behind Job4.

6

Job6 requires an Access lock. This conflicts with the queued Exclusive lock, so Job6 is queued behind Job5.

6.Multi-Statement Transactions 5

Explicit multi-statement transactions also should be reviewed for any scheduling concerns. When competing locks are needed by multiple requests in a single transaction, the lock manager automatically upgrades the mode fo r each request, in turn, until the transaction is completed.

7.Deadlock Detection and Resolution

5

Introduction 5

When two or more transactions are competing for locks on the same data, a deadlock may occur in which none of the deadlocked transactions can proceed. A local deadlock is detected by using information from the AMP lock table, local to each AMP. The deadlock is resolved by aborting the most recent of the deadlocked transactions. l

Locates a deadlock on local to each AMP

Selects a transaction that is participating in the deadlock (usually, the more recent) l

l

Aborts and rolls back that transaction

l

Notifies the user whose transaction was aborted

5

Detecting and resolving global deadlocks are more complex operations, and are coordinated by a PE. The coordinating PE initiates global deadlock detection on a timed basis. The frequency of global deadlock detection is based on a system-wide, deadlock-detection time parameter that is set in the user-definable DeadlockTimeOut field of the DBS Control Record. Global deadlock detection performs the following: l

Locates the deadlock on a system wide basis

Selects a transaction that is participating in the deadlock (usually, the more recent) l

l

Aborts and rolls back that transaction

l

Notifies the user whose transaction was aborted

8.Preventing Deadlocks

5Introduction 5

For particular types of transactions, or for very large or urgent applications, users can reduce or prevent the chance of a deadlock by including the LOCK[ING] modifier in the statement syntax. The LOCKING modifier can b e used to improve performance and reduce conflicts in the following ways: With the NOWAIT option, to abort a transaction if a lock cannot be granted immediately l

With LOCK ROW FOR WRITE, to eliminate the chance of a deadlock during upgrading, when multiple transactions select and then update the same row l

To apply a higher mode of lock than that normally applied by the lock manager l

To apply a lock at a different level than that normally applied by the lock manager l

Example1. 5

The possibility of an inconsistent return is especially high when an ACCESS request uses a secondary index value in a conditional expression, because satisfied index constraints are not always rechecked against the ret rieved data row. For example, assuming that "QualifyAccnt" is defined as a secondary index, the following request: LOCKING TABLE AccntRec FOR ACCESS SELECT AccntNo, QualifyAccnt FROM AccntRec WHERE QualifyAccnt = 1587;

could return the following: AccntNo QualifyAccnt

------- -----------1761 4214

In this case, the value "1587" was found in the secondary index subtable, and the corresponding data row was selected and returned. However, the data for account 1761 had been changed by the other user while this se lection was in progress. Returns such as this are possible even if the data is changed or deleted only momentarily by a transaction that is subsequently aborted. This type of inconsistency can occur even if the data is changed only momentarily by a transaction that is later backed out. Example2. LOCKING ROW LOCKING ROW is appropriate only for single table selects that are based on a primary index or unique secondary index constraint, as shown in the following example: User A: BEGIN TRANSACTION; LOCK ROW FOR WRITE SELECT y FROM tableA WHERE usi=1; UPDATE tableA SET y=0 WHERE usi=1; END TRANSACTION;

User B: BEGIN TRANSACTION; LOCK ROW WRITE SELECT z FROM tableA WHERE usi=1; UPDATE tableA SET z=0 WHERE usi=1; END TRANSACTION;

In this example, the UserA request for a rowhash Write lock is granted, which blocks the UserB request for a Write lock on that row. The UserB transaction is queued until the UserA lock is released. The UserA lock is held unt il the entire transaction is complete. Thus, the UserB ’LOCK ROW ...’ request is granted only after the UserA END TRANSACTION statement has been processed.

9.Transaction Semantics: Operating in ANSI or Teradata Mode 5

Introduction 5

You can operate in either of the following modes: ANSI mode and Teradata mode. The system default mode is Teradata mode. The following rules hold in ANSI mode: l

A transaction initiation is always implicit.

A transaction is opened by the first SQL statement executed in a session or by the first statement executed following the close of a transaction. l

A transaction is initiated when no transaction is currently active, and a SQL statement is executed. l

The COMMIT [WORK] or ROLLBACK [WORK] statements close a transaction. l

The following rules hold in Teradata mode: l

Transactions can be implicit.

l

Transactions can be explicit (BT/ET).

l

Statement failures roll back the entire transaction.

l

An error result rolls back the entire transaction.

l

Control of character truncation causes no error.

l

The default in character comparison is NOT CASESPECIFIC.

SET is the default in the CREATE TABLE statement, i.e. by default, duplicate rows are not allowed when creating tables in Teradata mode. l

Changing Mode To change to ANSI from Teradata mode (or vice versa), carry out the following: