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 .. 162 163 164 165 166 167 < 168 > 169 170 171 172 173 174 .. 241 >> Next


placed on the underlying table(s) each time the view is referenced in a SQL statement.

Refer to the “CREATE VIEW” statement for more information on defining views.

Although views are often created to enforce a LOCKING FOR When Both the Request ACCESS rule, any user can override the LOCKING FOR ACCESS

and View Referenced by using a LOCKING FOR READ statement on the view. For

Include Locking Clauses 8 example:

REPLACE VIEW VPROD.AD_ME_INF AS

LOCKING TABLE PROD.AD_ME_INF FOR ACCESS

SELECT

AD_ME_ID

AD_ME_DSC_TX

FROM PROD.AD_ME_INF;

If an EXPLAIN is done on a SEL COUNT(*) FROM VPROD.AD_ME_INF; statement, the access lock can be seen in statement 1.

If you did an EXPLAIN on the statement:

LOCKING TABLE VPROD.AD_ME_INF FOR READ SEL COUNT (*) FROM VPROD.AD_ME_INF;

a read lock can be seen in statement 1 of the EXPLAIN.

Teradata RDBMS for UNIX SQL Reference

8-203
Teradata SQL Syntax Guide

LOCKING Modifier

What Locks Have Been Set?

Types of Locks

This behavior could be considered undesirable because the LOCKING FOR ACCESS statement can be overridden by anyone at anytime. However, certain customers consider this a useful feature, and depend on being able to override locking clauses in views by placing a lock in the request.

The EXPLAIN modifier may be used at the beginning of a SQL statement to determine what locks are set when the statement is executed.

Users with the performance monitor MONITOR SESSION privilege can view all locks in the system through use of the Performance Monitor/Application Programming Interface (PM/API), and can determine which other users’ locks are blocking their own.

A LOCKING modifier may be used to specify the following types of locks:

Lock Type Description
ACCESS used to allow selection of data from a table that may be locked for write access by other users. Because the data selected using ACCESS may be inconsistent because the data is concurrently being modified, this lock should be used only for casual inspection of data. Placing an ACCESS lock requires the SELECT privilege on the specified database or table.
READ used to ensure data consistency during a read operation (execution of a SELECT statement, for example). A number of users may hold a READ lock on a table at the same time. As long as a read lock is in place, no modification of the table is allowed. Placing a READ lock requires the SELECT privilege on the specified database or table.
WRITE enables a single user to modify data. As long as the WRITE lock is in place, all other users are locked out except readers who are viewing data using an ACCESS lock. Until a WRITE lock is released, no new READ locks are permitted. Placing a WRITE lock requires an UPDATE, INSERT, or DELETE privilege on the specified database or table.

8-204

Teradata RDBMS for UNIX SQL Reference
Teradata SQL Syntax Guide

LOCKING Modifier

Lock Type Description
EXCLUSIVE excludes all other users. An EXCLUSIVE lock is rarely used, except to make structural changes to a database. This is the most restrictive lock. Placing an EXCLUSIVE lock requires the DROP privilege on the specified database or table.
CHECKSUM only used with updatable cursors. Refer to the Teradata Application Programming with Embedded SQL for C, Cobol, and PLA

The wait for a requested lock may be indefinite, unless the NOWAIT Cancelling a Lock 8 option is specified. When an interactive user does not want to wait

for a lock, a BTEQ .ABORT command may be issued to cancel the transaction.

The following examples illustrate the use of LOCKING:

Examples8

The following LOCKING clause may be used to select data from the Example 1 8 Employee table while the table is being modified:

LOCKING TABLE Personnel.Employee IN ACCESS MODE SELECT Name, Salary FROM Employee WHERE Salary < 25000 ;

This select operation may do any of the following things.

• Return rows whose data may be updated or deleted an instant later by a concurrent operation initiated by another user who has obtained a WRITE lock.

• Omit rows that are undergoing a concurrent insert operation.

• Include rows that were never permanently in the table, because a transaction that was being used to insert new rows was aborted and its new rows backed out.

The system deals properly with the synchronization of base data ExampIs 2 rows and index subtable rows. However, an ACCESS lock may

allow inconsistent results even when secondary indexes are used in conditional expressions, because index constraints are not always rechecked against the data row.

Teradata RDBMS for UNIX SQL Reference

8-205
Teradata SQL Syntax Guide

LOCKING Modifier

For example, if a column named “QualifyAccnt” is defined as a secondary index for a table named “AccntRec”, the following request:

LOCKING TABLE AccntRec FOR ACCESS SELECT AccntNo/ QualifyAccnt FROM AccntRec WHERE QualifyAccnt = 1587;

could return:

AccntNo QualifyAccnt

1761 4214
Previous << 1 .. 162 163 164 165 166 167 < 168 > 169 170 171 172 173 174 .. 241 >> Next