Example: LOCKING Request Modifier and Secondary Indexes - Analytics Database - Teradata Vantage

SQL Data Manipulation Language

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2024-04-05
dita:mapPath
pon1628111750298.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
esx1472246586715
lifecycle
latest
Product Category
Teradata Vantage™

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

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 called a dirty read. See Teradata Vantage™ - SQL Request and Transaction Processing, B035-1142.

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.