About Reading Committed Data | Teradata Vantage - About Reading Committed Data While Loading to the Same Table - Advanced SQL Engine - Teradata Database

Users can 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, do one of the following things:
  • 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;

    This method works if the group of tables being loaded are all within one database transaction.

  • Use the BEGIN ISOLATED LOADING statement. For example:

    BEGIN ISOLATED LOADING ON ldi1, ldi2 USING QUERY_BAND
    'LDILoadGroup=Grp1' IN MULTIPLE SESSION;

    This method works well if the related group of tables are being loaded in different scripts, programs, or SQL transactions in one or more sessions. Before starting load operations, use a BEGIN ISOLATED LOADING request, and after the loads complete, use an END ISOLATED LOADING request.

• The SELECT request must use the LOAD COMMITTED locking option to concurrently 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 execute SQL requests. (The Load and Update operators execute SQL requests when the MLOADX protocol is used; however, Advanced SQL Engine 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 QryLogStepsV.

For details on CREATE TABLE and ALTER TABLE syntax or the isolated loading statements BEGIN/END ISOLATED LOADING and CHECKPOINT ISOLATED LOADING, see Teradata Vantage™ - SQL Data Definition Language Syntax and Examples, B035-1144. For details on the SELECT, INSERT, UPDATE, MERGE, DELETE, and LOCKING request modifier syntax, see Teradata Vantage™ - SQL Data Manipulation Language, B035-1146.

For information on locking and load isolation, see Teradata Vantage™ - SQL Request and Transaction Processing, B035-1142.