Examples - Teradata Database

SQL Data Manipulation Language

Product
Teradata Database
Release Number
16.10
Published
June 2017
Language
English (United States)
Last Update
2018-04-25
dita:mapPath
psg1480972718197.ditamap
dita:ditavalPath
changebar_rev_16_10_exclude_audience_ie.ditaval
dita:id
B035-1146
lifecycle
previous
Product Category
Teradata® Database

Example: LOCKING Request Modifier

The following LOCKING clause can be used to select data from the employee table while it is being modified:

     LOCKING TABLE personnel.employee FOR ACCESS
     SELECT name, salary
     FROM employee
     WHERE salary < 25000 ;
The query results may:
  • Return rows whose data can 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 not permanently inserted in the base table, because a transaction inserting the new rows was aborted and the new rows were backed out.

Example: LOCKING Request Modifier and Secondary Indexes

The system synchronizes base data rows and index subtable rows. However, an ACCESS lock can allow inconsistent results even when secondary indexes are used in conditional expressions because index constraints are not always rechecked against the data row.

For example, a column named qualify_accnt is defined as a secondary index for a base table named accnt_rec, as in the following request:

     LOCKING TABLE accnt_rec FOR ACCESS
     SELECT accnt_no, qualify_accnt
     FROM accnt_rec
     WHERE qualify_accnt = 1587;

The request could return:

     accnt_no     qualify_accnt
     --------     -------------
         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 another user while the retrieval was in process. This is referred to as a dirty read. See SQL Request and Transaction Processing .

Anomalous results like these are possible even if the data is changed only momentarily by a transaction that is ultimately aborted. The ACCESS lock is most useful to those who simply want an overview of data and are not concerned with consistency.

Example: LOCKING ROW

This example shows the proper use of a rowhash lock.

     CREATE TABLE customer (
       cust_id INTEGER,
       phone   INTEGER,
       fax     INTEGER,
       telex   INTEGER)
     PRIMARY INDEX (cust_id),
     UNIQUE INDEX(fax),
     INDEX(telex);

     CREATE TABLE sales (
       custcode INTEGER,
       zip      INTEGER,
       salesvol INTEGER);

User A:

     BEGIN TRANSACTION;

     LOCKING ROW EXCLUSIVE
     SELECT phone
     FROM customer
     WHERE cust_id=12345;

     UPDATE customer
     SET phone=3108292488
     WHERE cust_id=12345;

The User A EXCLUSIVE rowhash lock prevents another user from accessing the same row.

In the following, the user A rowhash WRITE lock, in conjunction with LOCKING TABLE, prevents user B from accessing the same row:

User A:

     BEGIN TRANSACTION;

     LOCKING TABLE sales FOR READ,
     LOCKING ROW FOR WRITE
     SELECT telex
     FROM customer
     WHERE fax=0;

     UPDATE customer
     SET telex=0
     WHERE fax=0;

     SELECT zip
     FROM sales
     WHERE custcode=111;

     SELECT salesvol
     FROM sales
     WHERE custcode=222;

     ...

     END TRANSACTION;

User B:

     BEGIN TRANSACTION;

     LOCKING ROW FOR WRITE
     SELECT *
     FROM customer
     WHERE cust_id=12345
     INSERT INTO customer (12345, 3108284422, 3108684231, 5555);

     END TRANSACTION;

The User B LOCKING ROW FOR WRITE modifier waits until the User A transaction ends before it can be completed.

Example: NOWAIT Option

You have a request that you do not want to be placed in the lock queue if it cannot be serviced immediately. In this case, use the NOWAIT option. For example:

     LOCKING employee FOR READ NOWAIT
     SELECT employee_number,  last_name
     FROM employee
     WHERE department_number=401;

     *** Failure 7423 Object already locked and NOWAIT.
            Transaction Aborted. Statement# 1, Info =0

Another request had employee locked, so you must resubmit the request.

     LOCKING employee FOR READ NOWAIT
     SELECT employee_number, last_name
     FROM employee
     WHERE department_number=401;

      *** Query completed. 7 rows found. 2 columns returned.
      *** Total elapsed time was 1 second.
     employee_number  last_name
     ---------------  --------------------
                1003  Trader
                1004  Johnson
                1013  Phillips
                1002  Brown
                1010  Rogers
                1022  Machado
                1001  Hoover

This time, no locks were being held on employee, so the request completed successfully.