Locks and Concurrency | COLLECT STATISTICS | Teradata Vantage - Locks and Concurrency - Advanced SQL Engine - Teradata Database

SQL Data Definition Language Detailed Topics

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Published
July 2021
Language
English (United States)
Last Update
2021-07-27
dita:mapPath
imq1591724555718.ditamap
dita:ditavalPath
imq1591724555718.ditaval
dita:id
B035-1184
lifecycle
previous
Product Category
Teradata Vantage™

When you perform a COLLECT STATISTICS request, the system places an ACCESS lock on the table from which the demographic data is being collected. The database places a rowhash-level WRITE lock on DBC.StatsTbl after the statistics have been collected and holds the lock only long enough to update the dictionary rows. You can easily see this behavior documented in the boldface text in stage 4 of the following EXPLAIN of a COLLECT STATISTICS request on COLUMN x1.

EXPLAIN COLLECT STATISTICS ON t1 COLUMN x1;

*** Help information returned. 18 rows.
*** Total elapsed time was 1 second.

Explanation
----------------------------------------------------------------
1) First, we lock DF2.t1 for access.
2) Next, we do a COLLECT STATISTICS step from DF2.t1 by way
   of an all-rows scan into Spool 3 (all_amps), which is built
   locally on the AMPs.
3) Then we save the UPDATED STATISTICS from Spool 3 (Last Use) into
   Spool 4, which is built locally on the AMP derived from 
    DBC.StatsTbl  by way of the primary index.
 4) We lock DBC.StatsTbl for write on a RowHash. 
5) We do a single-AMP MERGE DELETE to  DBC.StatsTbl  from Spool 4 
   (Last Use) by way of a RowHash match scan.  New updated rows are
   built and the result goes into Spool 5 (one-amp), which is built
   locally on the AMPs.
6) We do a single-AMP MERGE into  DBC.StatsTbl  from Spool 5 (Last Use).
7) We spoil the parser's dictionary cache for the table.
8) Finally, we send out an END TRANSACTION step to all AMPs involved 
   in processing the request.
-> No rows are returned to the user as the result of statement 1.

These locks prevent parsing of new requests against the data table for which the statistics are being collected until the operation completes.

In general, COLLECT STATISTICS requests can run concurrently with DML requests, other COLLECT STATISTICS requests, DROP STATISTICS requests, and HELP STATISTICS requests against the same table.

You should not collect statistics for the first time concurrently on the same table. However, you can collect or recollect statistics from different tables and run other statistics-related requests such as DROP STATISTICS, HELP STATISTICS, and SHOW STATISTICS concurrently.

COLLECT STATISTICS requests can also run concurrently with a CREATE INDEX request against the same table as long as they do not specify the same index.

The following bullets list the exceptions to this rule. The exceptions assume that you do not specify a LOCKING modifier on the table with the COLLECT STATISTICS request (or if you do specify a LOCKING modifier, it requests only ACCESS-level locking).
  • If a transaction containing a DML request places an EXCLUSIVE lock on a table, the DML request blocks COLLECT STATISTICS requests, or a COLLECT STATISTICS request blocks the other DML request, depending on which request places its table lock first. Recall that COLLECT STATISTICS uses an ACCESS lock on the table by default. You should not normally specify an EXCLUSIVE lock on a table in conjunction with a DML request.
  • A COLLECT STATISTICS request is blocked from obtaining its row-hash WRITE locks on DBC.StatsTbl. These locks are requested near the completion of the COLLECT STATISTICS operation and are held only long enough to update the dictionary rows with the newly collected statistics.

    This can cause other requests to be blocked if the dictionary information for the table is not already cached. Note that once the WRITE lock is obtained, the COLLECT STATISTICS request should finish quickly, and parsing of the blocked requests should resume.