About Reading Committed Data While Loading to the Same Table - Teradata Database

Users can concurrently read committed data in a table while the table is being loaded when all of these conditions are met:

The table must be a load-isolated table.
You identify a table as load isolated using the ISOLATED LOADING option with CREATE TABLE or ALTER TABLE requests. For example:

CREATE TABLE ldi_table01, WITH CONCURRENT ISOLATED LOADING FOR ALL (col_a INT, col_b VARCHAR(32));

The load is performed using isolated loading.
As described later in this section, isolated loading can occur even when you don’t explicitly specify ISOLATED LOADING. If you want to ensure isolated loading use the ISOLATED LOADING option with your SQL request (INSERT, UPDATE, MERGE, and DELETE). For example:

INSERT WITH CONCURRENT ISOLATED LOADING INTO ldi_table01 SELECT * FROM src_Tab;

The SELECT request must use the LOAD COMMITTED locking option to read committed data from the table. For example:

LOCKING TABLE ldi_table01 FOR LOAD COMMITTED SELECT * FROM ldi_table01;

You can use Teradata PT with the SQL Inserter and SQL Selector operators for load isolation because these operators perform SQL requests. (The Load and Update operators perform SQL requests when the MLOADX protocol is used; however, Teradata Database determines the protocol that is used for each job. If the Load or Update operator uses the MULTILOAD protocol, then concurrent reads will not be allowed.)

You can determine whether the ISOLATED LOADING option is being used for a load by using the DBQL step level logging facility. See DMLLoadID in “Query Step Information Log Table: DBQLStepTbl” on page 314.

If a load is performed on a load-isolated table using a SQL request without the ISOLATED LOADING option then the default is to:

Allow concurrent reads on committed rows while the table is being all-AMP updated

Disallow concurrent reads while the table is modified with less than an all-AMP operation

Note: All SQL requests in a transaction must use the same type of concurrency as the first request in the transaction or the transaction will fail.

For details on CREATE TABLE and ALTER TABLE syntax, see SQL Data Definition Language Syntax and Examples. For details on the SELECT, INSERT, UPDATE, MERGE, DELETE, and LOCKING request modifier syntax, see SQL Data Manipulation Language.

For information on locking and load isolation, see SQL Request and Transaction Processing.