Locks and Concurrency
When you perform a COLLECT STATISTICS request, the system places an ACCESS lock on the table from which the demographic data is being collected. Teradata Database places a row‑hash‑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.
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).
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.