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;
Result:
*** 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.
- 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.