Reading Committed Data | Teradata Vantage - Reading Committed Data While Loading to the Same Table - Analytics Database - Teradata Vantage

Database Administration

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2023-11-03
dita:mapPath
pgf1628096104492.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
ujp1472240543947
lifecycle
latest
Product Category
Teradata Vantage™
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, Analytics 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 QryLogStepsV.

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 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 SQL Request and Transaction Processing, B035-1142.