Identity Column Inserts and Transaction-Bound Table Locks on DBC.IdCol - Analytics Database - Teradata Vantage

SQL Data Definition Language Detailed Topics

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
2023-07-11
dita:mapPath
vuk1628111288877.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
B035-1184
lifecycle
latest
Product Category
Teradata Vantage™
An identity column cache is populated by a range of column numbers whenever an INSERT operation into an identity column table is processed. The values for the range are determined by the following factors.
  • The next available value stored in DBC.IdCol.AvailValue
  • The defined incremental identity column batch size for the system

    This quantity is specified by the DBS Control parameter IdCol batch size. For more information, see Teradata Vantage™ - Database Utilities, B035-1102 and Optimizing Bulk Inserts Into an Identity Column.

When the pool of available identity column numbers in the cache is exhausted, Vantage must update DBC.IdCol.AvailValue and obtain a new range of numbers.

If an ongoing transaction has DBC.IdCol locked, as it would during a SELECT statement against the table, then system access to the column number pool is temporarily blocked and all inserts into the relevant identity column table are suspended until the transaction either commits or aborts. A SELECT statement against DBC.IdCol places an ACCESS lock on the table.

The SELECT privilege on DBC.IdCol is restricted to user DBC and any user explicitly granted the privilege by DBC.

When performing a SELECT statement against DBC.IdCol in Teradata session mode, you run the risk of creating a deadlock if you upgrade the default lock from ACCESS to READ or higher severity when both of the following conditions are true.
  • The SELECT statement is run within the boundaries of an explicit transaction.
  • Concurrent INSERT operations are being performed on an identity column table.