Books
in black and white
Main menu
Share a book About us Home
Books
Biology Business Chemistry Computers Culture Economics Fiction Games Guide History Management Mathematical Medicine Mental Fitnes Physics Psychology Scince Sport Technics
Ads

Teradata RDBMS forUNIX SQL Reference - NCR

NCR Teradata RDBMS forUNIX SQL Reference - NCR, 1997. - 913 p.
Download (direct link): teradataforunix1997.pdf
Previous << 1 .. 161 162 163 164 165 166 < 167 > 168 169 170 171 172 173 .. 241 >> Next


User B:

BEGIN TRANSACTION;

SELECT z FROM t WHERE x = 1;

UPDATE t SET z = 0 WHERE x = 1;

Positioning Non-Default Locks

When Explicit Locking Is Necessary

8-200

Teradata RDBMS for UNIX SQL Reference
Teradata SQL Syntax Guide

LOCKING Modifier

The system assigns a ROW READ LOCK when a simple SELECT is done on a Unique Primary Index, Primary Index, or Unique Secondary Index.

User As UPDATE statements ROW WRITE LOCK request will wait for User Bs ROW READ LOCK to be released. The ROW READ LOCK will not be released because User Bs UPDATE statements ROW WRITE LOCK request is also waiting for User As ROW READ LOCK to be released. Previously, this deadlock was avoided by using the LOCK TABLE modifier:

BEGIN TRANSACTION;

LOCK TABLE t FOR WRITE SELECT z FROM t WHERE x = 1;

UPDATE ....

But locking an entire table across all AMPS is undesirable. The use of LOCK ROW here prevents the need to lock an entire table across all AMPs.

The following example illustrates the use of LOCK ROW:

User A:

BEGIN TRANSACTION;

LOCK ROW WRITE SELECT y from t where x=1;

UPDATE t SET y=0 where x=1 END TRANSACTION;

User B:

BEGIN TRANSACTION;

LOCK ROW WRITE SELECT z from t where x=1;

UPDATE t set z=0 where x=1;

No deadlock will occur because User Bs LOCK ROW WRITE request is blocked by User As LOCK ROW WRITE. User Bs LOCK ROW WRITE statement will complete when User As END TRANSACTION statement is complete. The LOCK ROW modifier will only work:

For single table retrievals, using primary (NUPIs or UPIs) or unique secondary index (USI) searches.

When specified with a SELECT statement, because the LOCK ROW modifier picks up the row hash to be locked from the SELECT statement.

To upgrade an existing lock, from READ to WRITE, or from READ TO EXCLUSIVE, or from WRITE to EXCLUSIVE.

The LOCK ROW modifier does not work:

Where a lock on the target table is already in place.

Where an aggregate/DISTINCT/GROUP BY operation is part of the SELECT statement

Teradata RDBMS for UNIX SQL Reference

8-201
Teradata SQL Syntax Guide

LOCKING Modifier

To downgrade a lock from READ to ACCESS, once that lock is in place. If no row hash lock is in place already, an ACCESS lock can be set instead of a default READ row hash lock.

When two primary key SELECTS are followed by primary key UPDATES and the SELECTs are on different row hashes deadlock occurs. This is because a primary index update requires a table write lock on the target table. For example:

User A:

BEGIN TRANSACTION;

LOCK ROW WRITE SELECT x FROM t WHERE x = 1; (x is UPI, NUPI, or USI) UPDATE t SET x=2 WHERE x=1;

User B:

BEGIN TRANSACTION;

LOCK ROW WRITE SELECT x FROM t WHERE x=2;

UPDATE t SET x=1 WHERE x=2;

Here User Bs SELECT ROW WRITE LOCK will not be queued behind User As transaction because it has a different row hash access. Deadlock occurs because User As table lock request waits on User Bs row lock, while User Bs table lock request waits on User As row lock.

If the LOCKING modifier is followed by a null SQL statement, the Using LOCks with NULL only effect of the modifier is to lock the specified object. While the

Statements LOCKING modifier may be used with a null statement, it is best to

use the LOCKING modifier with a user generated transaction, or as part of a multi-statement request.

Multiple LOCKING modifiers may precede a statement if it is Muldple Locks necessary to lock more than one object at the same time.

LOCKING TABLE Employee FOR ACCESS LOCKING TABLE Department FOR ACCESS SELECT Name, Loc

FROM Employee, Department WHERE (EmpNo=MgrNo);

The object that is locked by the LOCKING modifier does not have to Referencing the Locked be referenced in a subsequent SQL statement.

Object

A LOCKING modifier may be executed separately from the SQL statement that it precedes. Therefore, a LOCKING modifier must reference the object on which a lock is being placed; the objects referenced in the SQL statement have no effect on the execution of a LOCKING modifier.

When a LOCKING modifier references a view, the specified lock is applied to all underlying base tables. For example, if a view refers to

8-202

Teradata RDBMS for UNIX SQL Reference
Teradata SQL Syntax Guide

LOCKING Modifier

tables T_1 and T_2, then a lock on that view would apply to both tables.

LOCKING DATABASE is the only way to lock a database or a user.

Be sure to include the keyword for the entity (DATABASE, TABLE, Include the Keyword for or VIEW) that is to be locked.

the Locked Object

For example, if there is a database and a table, and if both are named AccntRec, then the following form could be used:

LOCKING TABLE AccntRec FOR ACCESS SELECT * FROM AccntRec;

If the TABLE keyword was not included, the lock would have been placed on the AccntRec database.

A LOCKING modifier may be included in a view definition. When a Locks and Views 8 view is defined with a LOCKING modifier, the specified lock is
Previous << 1 .. 161 162 163 164 165 166 < 167 > 168 169 170 171 172 173 .. 241 >> Next