15.00 - Locks and Concurrency When Accessing a Column-Partitioned Table - Teradata Database

Teradata Database Design

Teradata Database
User Guide

Locks and Concurrency When Accessing a Column-Partitioned Table

There is normally only one hash bucket per AMP that is used for the rows in a NoPI table or column‑partitioned table. Teradata Database selects this hash bucket from the NoPI hash map that the AMP owns. The first row going into the NoPI table or column‑partitioned table (or combined partition) begins with a uniqueness value of 1. The uniqueness value increases as more rows are inserted into the table.

The Reconfig and Restore/Copy client utilities can increase the number of hash buckets per AMP in a NoPI or column‑partitioned table, but the number of hash buckets is generally low. A NoPI or column‑partitioned table can be viewed as a very highly nonunique NUPI table in term of hash buckets. Because of this property, you need to be aware that a row‑hash lock on a NoPI or column‑partitioned table on an AMP can lock many, and usually all, of the rows in that table on that AMP.

Single-row INSERT requests and multirow INSERT requests such as Teradata Parallel Data Pump array INSERT operations use row‑hash locks that can block any other reader and writer of the table until the request completes.

You can specify a LOCKING FOR ACCESS request modifier for a reader to avoid a lock conflict with a writer. For multiple writers into the same NoPI table or column‑partitioned table, the Lock Manager grants the lock to one writer at a time.Because of this, you should not have multiple writers running concurrently on the same AMP. This is something that can happen, for example, when the number of sessions in a Teradata Parallel Data Pump array INSERT job is greater than the number of AMPs in your system.