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 different 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 simultaneous 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 statements 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 arbitrary 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. Multi-statement 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 states 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
|
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 being made to the database.
|
|
Write
|
The requester has exclusive rights to the locked
resource except for readers not concerned with data consistency.
|
|
Read
|
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
|
Access Type
|
Locking
Mode
|
|
UPI/NUPI/USI
|
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
|
|
CREATE DATABASE
DROP DATABASE
MONIFY DATABASE
|
Not
applicable
|
Database
|
Exclusive
|
|
CREATE TABLE
DROP TABLE
ALTER TABLE
|
Not
applicable
|
Table
|
Exclusive
|
4.What’s deadlock?
A deadlock occurs when
transaction 1 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
|
|
Single transaction
|
The RDBMS aborted a single transaction because
of:
l Transaction deadlock timeout
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 statement.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 table-level 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 target 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 Checksum
|
Lock
Granted
|
Lock
Granted
|
Lock
Granted
|
Lock
Granted
|
Request Queued*
|
|
Read
|
Lock
Granted
|
Lock
Granted
|
Lock
Granted
|
Request Queued*
|
Request Queued*
|
|
Write
|
Lock
Granted
|
Lock
Granted
|
Request Queued*
|
Request Queued*
|
Request Queued*
|
|
Exclusive
|
Lock
Granted
|
Request Queued*
|
Request Queued*
|
Request Queued*
|
Request Queued*
|
|
* 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 are 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
|
Selection Criteria
|
Object Locked
|
Locking Mode
|
|
SELECT
|
UPI or USI
|
Row
|
Read
|
|
|
NUPI
|
Set of rows
|
Read
|
||
|
Any other
|
Table
|
Read
|
||
|
INSERT…
[VALUES]…
|
|
|
Primary row
Write
|
|
|
INSERT…
SELECT…
|
Select table
|
|||
|
UPI or USI
|
Row
|
Read
|
||
|
NUPI
|
Set of rows
|
Read
|
||
|
Any other
|
Table
|
Read
|
||
|
Insert table
|
Write
|
|||
|
UPDATE
|
Neither UPI nor USI
|
UPI or USI
|
Row
|
Write
|
|
Neither NUPI nor USI
|
NUPI
|
Set of Rows
|
Write
|
|
|
Any other
|
Table
|
Write
|
||
|
DELETE
|
UPI or USI
|
Row
|
Write
|
|
|
NUPI
|
Set of Rows
|
Write
|
||
|
Any other
|
Table
|
Write
|
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
l Selects a transaction
that is participating in the deadlock (usually, the more recent)
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
l Selects a transaction
that is participating in the deadlock (usually, the more recent)
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:
l 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
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
User A request for a row hash Write lock is granted, which blocks the User B
request for a Write lock on that row. The User B transaction is queued until the
User A lock is released. The User A lock is held until the entire transaction is
complete. Thus, the User B ’LOCK ROW ...’ request is granted only after the
User A 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.
l 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.
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.
l SET is the default in
the CREATE TABLE statement, i.e. by default, duplicate rows are not allowed
when creating tables in Teradata mode.
Changing Mode
To change to ANSI from
Teradata mode (or vice versa), carry out the following:
|
For this software
|
USE these commands or
options
|
TO switch to this mode
|
|
BETQ
|
.[SET] SESSION TRANSACTION ANSI
|
ANSI
|
|
.[SET] SESSION TRANSACTION BTET
|
Teradata
|
|
|
Log off prior to entering this command.
The command does not take effect until the next
logon.
Refer to the Teradata BTEQ Reference for
more detail on using BTEQ commands.
|
||
|
Preprocessor2
|
TRANSACT(ANSI)
|
ANSI
|
|
TRANSACT(BTET)
TRANSACT(2PC)
TRANSACT(COMMIT)
|
Teradata
|
|
|
Refer to Teradata Application Programming With
Embedded
SQL for C, COBOL, and PL/I for
more detail on setting
Preprocessor options.
|
||
|
CLI
|
Set tx_semantics = ‘A’
Set tx_smantics to ‘A’
|
ANSI
|
|
Set tx_semantics = ‘T’
|
Teradata
|
|
|
Set tx_semantics = ‘D’
|
Server default
|
|
|
Refer to Teradata Call-Level Interface
Version 2 for more detail on setting the tx_semantics field.
|
||
No comments:
Post a Comment