DDL and DCL Requests, Dictionary Access, and Locks | Teradata Vantage - DDL and DCL Requests, Dictionary Access, and Locks - Advanced SQL Engine - Teradata Database

SQL Request and Transaction Processing

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
17.00
Published
June 2020
Language
English (United States)
Last Update
2021-01-24
dita:mapPath
ykx1561500561173.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1142
lifecycle
previous
Product Category
Teradata Vantage™

The execution of a DDL or DCL request causes the data dictionary to be updated and appropriate locks to be placed on system tables while that request is processing.

Optimizing the Locking Granularity for Data Dictionary Access

To improve concurrency, DDL and DCL processing use the finest locking granularity that is practical and delay placing their locks for as long as possible. Depending on the dictionary table, the system sometimes downgrades rowhash READ lock requests made on the dictionary to ACCESS locks if the query would otherwise be blocked by WRITE locks placed on those tables by ongoing DDL operations.

If these rowhash READs are not blocked, then they use the standard READ locks.

The following dictionary views and tables are affected by this locking downgrade on a blocked READ lock request:
  • DBC.AccLogRuleTbl
  • DBC.ConstraintNames
  • DBC.Indexes
  • DBC.TableConstraints
  • DBC.TextTbl
  • DBC.TriggersV
  • DBC.TVFields
  • DBC.TVM
  • DBC.UDFInfo
The only SQL statements eligible for a dictionary access READ lock-to-ACCESS lock downgrade upon being otherwise blocked are the following:
  • SELECT
  • HELP COLUMN
  • HELP CONSTRAINT
  • HELP INDEX
  • HELP STATISTICS
  • SHOW FUNCTION/HASH INDEX/JOIN INDEX/MACRO/METHOD/PROCEDURE/TABLE/TRIGGER/TYPE/VIEW

These are system-initiated lock downgrades: you cannot specify them using the LOCKING request modifier. For more information, see “LOCKING Request Modifier” in Teradata Vantage™ - SQL Data Manipulation Language, B035-1146.