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

Teradata Database Administration

Product
Teradata Database
Release Number
16.10
Release Date
April 2018
Content Type
Administration
Publication ID
B035-1093-161K
Language
English (United States)

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 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.

If a load is performed on a load-isolated table using an SQL request without the ISOLATED LOADING option or without the BEGIN ISOLATED LOADING request, 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
    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 or the isolated loading statements BEGIN/END ISOLATED LOADING and CHECKPOINT ISOLATED LOADING, 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.