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