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. For more information, see Teradata Vantage™ - SQL Data Definition Language Syntax and Examples, B035-1144.
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.
For more information, see Teradata Vantage™ - SQL Data Manipulation Language, B035-1146.
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.
Load Processing for Load-isolated Tables
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.
- 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.
Load State Parameters for an Implicit Transaction-based Load-isolated Load
- Logical HostID and Session number of the session performing the load.
- NewLoadID value used in the load operation.
The LOCKING FOR LOAD COMMITTED Request Modifier
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.
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.
- 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 | DBS Control 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 Teradata Vantage™ - SQL Data Manipulation Language, B035-1146.
Modifying Load-isolated Tables
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, MultiLoad, 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. | NCLDI DML |
| 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 |
Locking Rules
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.
- 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 of 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 of 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 Loadon 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 of 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.
CLDI and NCLDI Modifications
- 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.
- 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.