15.10 - Load Isolation - Teradata Database

Teradata provides a table isolation property that enables you to read committed rows from tables while the tables are being loaded with data. Even if the data is changing while the table is being loaded, load isolation ensures that the most recent committed data is retrieved.

You can use the WITH CONCURRENT ISOLATED LOADING option of a CREATE/ALTER TABLE request to define tables as load isolated. Load-isolated tables record the most recent committed load ID values in the data dictionary. Read operations on load-isolated tables use the most recent committed load ID to isolate the rows that are committed from those that are not yet committed.

See SQL Data Definition Language Syntax and Examples.

To enable committed reads in concurrent sessions, rows that are being modified are logically deleted from load-isolated tables and new rows with the modified values are inserted when rows are updated. If a row is not yet committed, then the data that is read is the data that was true and final in the table prior to the subsequent data change. To enable concurrent index-based reads, indexes (including join indexes) also maintain the commit property of the row. A join index table is marked as a load-isolated table if any of the referenced base tables is a load-isolated table.

You can use the LOAD COMMITTED locking modifier to read committed rows in load-isolated tables without blocking load-isolated modifications.

See SQL Data Manipulation Language.

Modifications on load-isolated tables are classified as either concurrent or nonconcurrent. Concurrent load-isolated modifications (CLDIs) are modifications that permit concurrent reader sessions to select load-committed rows. Nonconcurrent load-isolated modifications (NCLDIs) are modifications that block the reader sessions from selecting committed rows. NCLDI modifications performed using SQL use EXCLUSIVE locks instead of WRITE locks. NCLDI modifications that are performed using a utility such as Fastload or Multiload, or a load operator using TPT or the TPT update operator that does not use MLOADX may use an EXCLUSIVE lock or a WRITE lock. An acquired WRITE lock during the utility-based load operation may be upgraded to an EXCLUSIVE lock for load-isolated target tables, which can result in blocking concurrent read sessions even if they are being performed using an ACCESS lock.

Load operations are started implicitly, in a transaction. A load operation is committed at the time the transaction is committed. The session performing this transaction is the load session.

For load-isolated tables, a load operation is limited to a single transaction.

All rows modified during a load operation are considered load-uncommitted rows and the table is considered to be in load state until the load operation is committed.

The first committed load-isolated DML SQL that you issue on a table begins the load on the load-isolated table. The table is marked for load, associated with both the session and the transaction. When the transaction commits, the load operation is committed. If transaction is rolled back, the load operation is rolled back.

If an underlying join index on the load-isolated table is modified, then the join index is also marked as being loaded. As part of the transaction commit, such tables are load committed with the following actions:

DBC.TVM rows for the tables in load are updated with the corresponding CurrentLoadID property value, incremented by 1.

During the commit step processing of the transaction commit, the following events take place:

The table header row for the tables is updated to mark load completion.

Load-isolated spoil steps are generated for the TVM and the table header of the load-isolated table to update the dictionary cache with the updated CurrentLoadID value.

The following load state information is recorded in the table header of the load-isolated table when the load is an implicit transaction-based load:

Logical HostID and Session number of the session performing the load.

NewLoadID value used in the load operation

The LOCKING request modifier permits a user to override the default lock that the system places on a database, table, view, or a row-hash. When users want to read load-isolated tables, they can use LOAD COMMITTED locking.

Note: The LOAD COMMITTED lock mode is not a new type of lock. The lock displayed or reported by the Lock Manager for this clause is the existing ACCESS lock.

A LOAD COMMITTED lock mode causes the system to place an ACCESS lock if you specify it by using a NULL SQL request or if the referenced object is not used in the SQL request. If you specify a LOAD COMMITTED lock mode on a nonload-isolated table, this action also results in the system applying an ACCESS lock.

LOAD COMMITTED lock mode behavior is similar to the ACCESS lock locking behavior. The primary difference is in the data that it selects from a load-isolated table when the table is referenced in an SQL request.

If the table is a load-isolated table and it is not being loaded by the session, then load-committed data is returned from the table. This indicates that the system has applied a load-committed condition to the table being read.

If the table is a load-isolated table and it is being loaded by the session, then the uncommitted data is also returned from the table. This indicates that the system has applied a load-uncommitted condition to the table being read.

If a lock cannot be upgraded, the locking modifier is ignored. A LOCKING FOR ACCESS modifier on a view that defines a LOAD COMMITTED lock mode is ignored.

The following table describes the behavior with and without an explicit LOCKING modifier being specified for the transaction isolation level of a table. If the session isolation level is SERIALIZABLE, then read operations on load-isolated tables block concurrent write operations. The recommended method for load-isolated tables is to specify the LOAD COMMITTED locking modifier so that the session does not block concurrent writes but still selects load-committed rows from the table.

Isolation Level	Locking Modifier	DBSControl Field 54 setting (AccessLockForUncomRead)	Read Table Source in Modification statement	Read Table Non-Modification statement
SERIALIZABLE	Not specified	Not applicable	READ load committed condition	READ load committed condition
SERIALIZABLE	Locking src for access	Not applicable	ACCESS load uncommitted condition	ACCESS load uncommitted condition
SERIALIZABLE	Locking src for load committed	Not applicable	ACCESS load committed condition	ACCESS load committed condition
READ UNCOMMITTED	Not specified	FALSE (Default)	READ load committed condition	ACCESS load uncommitted condition
READ UNCOMMITTED	Locking src for load committed	Not applicable	ACCESS load uncommitted condition	ACCESS load uncommitted condition
READ UNCOMMITTED	Not specified	TRUE	ACCESS load uncommitted condition	ACCESS load uncommitted condition
READ UNCOMMITTED	Locking src for load committed	Not applicable	ACCESS load committed condition	ACCESS load committed condition
READ UNCOMMITTED	Locking src for read	Not applicable	READ load committed condition	READ load committed condition

For information about the LOCKING LOAD COMMITTED request modifier, see SQL Data Manipulation Language.

You can use INSERT, UPDATE, MERGE, and DELETE statements on load-isolated tables with the WITH CONCURRENT ISOLATED LOADING or WITH NO CONCURRENT ISOLATED LOADING syntax. If you specify WITH NO CONCURRENT ISOLATED LOADING, then the modification is performed as a nonconcurrent, load-isolated modification (NCLDI).

A CLDI modification is a modification on a load-isolated table that is isolated from a concurrent load-committed-based read operation. The modification is performed while isolating the change by saving the version of the row being modified or deleted.

A NCLDI modification is a modification on a load-isolated table that does not permit a concurrent read operation. It follows the same modification method that is used for an equivalent nonload-isolated table, but the lock acquired is an EXCLUSIVE lock and concurrent read operations are blocked.

If you explicitly use WITH CONCURRENT ISOLATED LOADING, then the SQL operation is a CLDI and is treated as load operation, which is useful if you want to override a session setting of FOR NO ISOLATED LOADING.

All DML statements in a session that disable load operations default to NCLDI modifications.

The following table describes how DML statement operations that do not contain explicit WITH CONCURRENT ISOLATED LOADING or WITH NO CONCURRENT ISOLATED LOADING syntax are handled by the system.

Description of Situation or Syntax Used	Operation Type
The table is part of a load operation.	CLDI DML
The DML operation requires an all-AMP table-level/partition-level write lock.	CLDI DML
The session disables concurrent load-isolated operations.	NCLDI DML
The DML operation is from a load utility such as Fastload, Multi-load, Teradata PT load operator, or Teradata PT update operator that does not use the MLOADX protocol.	NCLDI DML
The table is set to FOR NONE as its load-isolated DML level.	NCLDIDML
The table is set to FOR INSERT as DML level and the DML is not INSERT or MERGE-INTO with an INSERT portion.	NCLDI DML
The DML operation does not require an all-AMP table- or partition-level WRITE lock.	NCLDI DML

The system always uses an EXCLUSIVE lock for NCLDI DML on a load-isolated table. A concurrent read operation on the table at the same lock level and involved AMPs is always blocked until the associated transaction releases the EXCLUSIVE lock.

For instances where loading is not disabled in the session, the following rules apply:

The first CLDI DML on a table or join index sets the table or join index to an implicit transaction-based load operation.

If the first CLDI DML on a load-isolated table or join index in the transaction is not all-AMP or is all AMP but not a table-level lock-based operation, then the operation is forced to acquire an all-AMP and table-level lock.

You cannot use both CLDI and NCLDI DML on the same table in the same transaction. This rule means that all subsequent load-isolated types for the DMLs must be same as the load-isolated type of the first DML.

An implicit load on a table or join index prevents any concurrent write operation on the table.

Example : An Explicit NCLDI Modification

This example illustrates an explicit NCLDI modification.

EXPLAIN UPDATE WITH NO ISOLATED LOADING t1 SET c2 = c2 + 1;

*** Help information returned. 12 rows.

*** Total elapsed time was 1 second.

Explanation

------------------------------------------------------------------------

  1) First, we lock db1.t1 for exclusive use on a reserved rowHash to

     prevent global deadlock.

  2) Next, we lock db1.t1 for exclusive use.

  3) We do an all-AMPs UPDATE (nonconcurrent load isolated) from

     db1.t1 (Load Uncommitted) by way of an all-rows scan with a

     condition of ("(db1.t1.TD_ROWLOADID_DEL = 0) AND ((1=1))").  The

     size is estimated with low confidence to be 4 rows.  The estimated

     time for this step is 0.07 seconds.

  4) Finally, we send out an END TRANSACTION step to all AMPs involved

     in processing the request.

  -> No rows are returned to the user as the result of statement 1.

     The total estimated time is 0.07 seconds.

Example : An Implicit Load Operation

This example illustrates an implicit load operation in an implicit transaction. Notice the new explain element, concurrent load isolated, which indicates that UPDATE versions the rows during modifications. Notice also that the “Begin Isolated Load” starts the load operation, and the “End Isolated Load” step ends the load operation. The TVM is also updated to reflect the committed load ID.

EXPLAIN UPDATE t1 SET c2 = c2 + 1;

*** Help information returned. 18 rows.

*** Total elapsed time was 1 second.

Explanation

------------------------------------------------------------------------   1) First, we lock db1.t1 for write on a reserved rowHash to prevent

     global deadlock.

  2) Next, we lock db1.t1 for write.

  3) We Begin Isolated Load on buck.T1.

  4) We execute the following steps in parallel.

       1) We do an all-AMPs UPDATE (concurrent load isolated) from

          db1.t1 (Load Uncommitted) by way of an all-rows scan with a

          condition of ("(db1.t1.TD_ROWLOADID_DEL = 0) AND ((1=1))").

          The size is estimated with low confidence to be 4 rows.  The

          estimated time for this step is 0.07 seconds.

       2) We lock DBC.TVM for write on a RowHash, and then we do a

          single-AMP UPDATE from DBC.TVM by way of the unique primary

          index "Field_1 = '00000204'XB, Field_2 = 'T1'" with no

          residual conditions.

  5) We End Isolated Load.

  6) Finally, we send out an END TRANSACTION step to all AMPs involved

     in processing the request.

  -> No rows are returned to the user as the result of statement 1.

Example : An Implicit NCLDI Modification

This examples illustrates an implicit NCLDI modification and assumes that no other operation on t1 is taking place and t1 is not already in an explicit load state.

BEGIN TRANSACTION ;

EXPLAIN UPDATE t1 SET c2 = C2 +1 WHERE c1= 5;

 *** Help information returned. 6 rows.

 *** Total elapsed time was 1 second.

Explanation

------------------------------------------------------------------------   1) First, we do a single-AMP UPDATE (nonconcurrent load isolated)

     from db1.t1 (Load Uncommitted) by way of the primary index

     "db1.t1.c1 = 5" with a residual condition of (

     "(db1.t1.TD_ROWLOADID_DEL = 0) AND ((1=1))").  The size is

     estimated with low confidence to be 2 rows.  The estimated time

     for this step is 0.06 seconds.

  -> No rows are returned to the user as the result of statement 1.

     The total estimated time is 0.06 seconds.

All existing error logging rules apply to each of the qualified rows that the CLDI DML modifies, with the following exceptions:

The data row that results from a modification of a child non-load-isolated table using MLOADX update is rolled back when an RI violation occurs. The relevant information is logged in the error table. If the RI error results from an MLOADX update to a child load-isolated table, the error is logged, but the transaction is rolled back rather than the offending row.

The data row that results from a modification of a non-load-isolated table using MLOADX Update is rolled back when a uniqueness violation occurs. The relevant information is logged in the error table. If the uniqueness violation error results from an MLOADX update to a load-isolated table, the error continues to be logged, but the transaction is rolled back rather than the offending row.

In general, when errors occur during the modified row processing of CLDI updates that are performed by using MLOADX with error logging enabled, data rows are not rolled back. The associated transaction is rolled back instead.

All NCLDI modifications follow the rules for existing modifications on a non-load-isolated table.

For CLDI INSERTs, the row to be inserted is inserted. For CLDI DELETEs, if the row to be deleted is a row inserted in the same load operation, then the row is deleted. Otherwise, the row to be deleted is marked and then the row is updated in the table. A CLDI Update on the row is performed as follows:

If the row to be updated is not a newly inserted row in the load, then a CLDI DELETE is performed on the existing row and a CLDI INSERT is performed on the modified row with the updated values.

If the row to be updated is a row inserted in the current load operation, then the same row is modified with the updated values.