Load Isolation Can Make Your Table Less Concurrent, Not More

Load isolation sounds like a feature that should improve concurrency. After all, it’s one of the primary reasons for enabling it: data should be loaded while applications and users continue to query the table without being unnecessarily disturbed by the load.

In one system, however, we observed exactly the opposite behavior.

A monitoring query periodically executed the following statement:

LOCKING ROW FOR ACCESS
SELECT COUNT(*) FROM ORDERS;

Every so often, instead of returning the row count, the query failed with Teradata error 2631:

Transaction ABORTed due to Deadlock

At first this was difficult to reconcile with the workload. The target table was insert-only, and the monitoring query explicitly requested an ACCESS lock. ACCESS locking is normally chosen precisely because it allows a query to read through concurrent modifications that would block a normal READ lock.

The table was also defined with:

WITH CONCURRENT ISOLATED LOADING FOR ALL

The intention behind this definition was straightforward: loading should not interfere with readers.

Yet the table was experiencing reader/writer deadlocks.

The explanation turned out not to be a defect in ACCESS locking or in Teradata’s deadlock detection. The problem was the combination of load isolation and how the application performed the load.

The important rule is this:

A load-isolated table that is loaded row by row can provide less concurrency than the same table without load isolation.

To understand why, we need to look at the lock level Teradata chooses, the distinction between concurrent and nonconcurrent isolated loading, and the role of proxy locks in global deadlock prevention.

All measurements in this article can be reproduced with a small test table. A reproduction script is included at the end.

Two Obvious Fixes That Do Not Fix the Problem

Before looking at the internal mechanism, it is useful to eliminate two explanations that appear reasonable at first.

Using LOAD COMMITTED

If a table is defined for concurrent isolated loading, the natural assumption is that readers should use the corresponding read mode:

LOCKING TABLE ORDERS FOR LOAD COMMITTED
SELECT COUNT(*) FROM ORDERS;

This is the supported read form for load-isolated tables, so one might expect it to remove the deadlocks.

The tests did not show that.

Twenty executions were performed, alternating between an ordinary ACCESS lock and LOAD COMMITTED.

Read form	Runs	Deadlocks
ordinary ACCESS lock	10	2
LOCKING TABLE ... FOR LOAD COMMITTED	10	4

The difference between two and four deadlocks is not statistically meaningful with such a small sample. The important result is that LOAD COMMITTED did not remove the problem.

The reason becomes clear when looking at the lock that Teradata actually requests.

LOAD COMMITTED is not an additional lock severity that can pass an EXCLUSIVE lock. At the Lock Manager level, the reader is still operating with ACCESS semantics, and ACCESS is incompatible with EXCLUSIVE.

Changing the SQL syntax therefore does not change the conflict that is causing the reader to wait.

Increasing the JDBC Batch Size

The second obvious change is to increase the application’s batch size.

This can improve throughput, but it does not change the locking mechanism if the JDBC batch still consists of individual statements such as:

INSERT INTO ORDERS VALUES (...);
INSERT INTO ORDERS VALUES (...);
INSERT INTO ORDERS VALUES (...);

A JDBC batch containing 50 single-row INSERT statements is still composed of 50 individual single-row modifications. Teradata does not transform them into one set-based request.

A controlled test makes the difference visible:

Writer	Reader waited
JDBC batch of 50 single-row INSERTs	4.33 s
one INSERT ... SELECT from staging	0.04 s

The batch size may reduce network round trips and improve the loader’s throughput, but it does not change the lock level of the individual INSERT statements.

If the locking problem is caused by single-row DML, a larger JDBC batch simply performs more of the same DML.

The Important Distinction: CLDI and NCLDI

Teradata distinguishes between two forms of modification on a load-isolated table:

• CLDI, concurrent load isolated
• NCLDI, nonconcurrent load isolated

This distinction is fundamental to understanding the behavior.

An operation that requires an all-AMP table-level WRITE lock can be treated as concurrent isolated loading. An operation that does not require such a lock is treated as nonconcurrent.

Consider a single-row INSERT:

INSERT INTO ORDERS
VALUES (...);

If the row can be located by its hash value, the request needs only the AMP responsible for that rowhash. There is no reason for Teradata to obtain an all-AMP table WRITE lock.

Consequently, the operation becomes NCLDI.

EXPLAIN makes this explicit:

1) First, we do an INSERT step into
   (nonconcurrent load isolated) DWHPRO.ORDERS.

The important consequence is not merely the classification itself. It is the lock severity Teradata uses for NCLDI modifications.

For this type of modification, Teradata takes an EXCLUSIVE RowHash lock.

That is very different from the lock normally used by a single-row INSERT on an ordinary table.

Without load isolation, the same INSERT would normally use a WRITE RowHash lock.

The distinction matters because ACCESS and WRITE are compatible, whereas ACCESS and EXCLUSIVE are not.

Conceptually:

Ordinary table

Writer:  WRITE RowHash
Reader:  ACCESS

        compatible

but:

Load-isolated table, NCLDI

Writer:  EXCLUSIVE RowHash
Reader:  ACCESS

        incompatible

The modification has not become larger. It still affects only a rowhash on one AMP.

What changed is the severity of the lock.

This is why the behavior initially appears counterintuitive. Load isolation was enabled to improve reader/load concurrency, but when the table is loaded through individual INSERT statements, those statements become NCLDI operations and acquire locks that are less compatible with ACCESS readers than the locks on an ordinary table.

The reader is therefore blocked by a writer that it would have passed if load isolation had not been enabled.

Blocking Alone Does Not Explain the Deadlock

An incompatible lock explains why the query waits. It does not yet explain error 2631.

For a deadlock to exist, at least two transactions must form a dependency cycle.

Teradata contains mechanisms intended to prevent certain distributed lock cycles from forming, which is where proxy locks become relevant.

It is important here not to confuse proxy locks with pseudo table locks. They solve different problems.

Proxy Locks

Before requesting certain all-AMP locks, Teradata first obtains a proxy lock on a single AMP.

This is the operation commonly visible in an execution plan as:

lock ... on a reserved RowHash to prevent global deadlock

The reserved RowHash acts as a serialization point. Requests that need the corresponding all-AMP lock first pass through this gatekeeper rather than independently acquiring conflicting locks across different AMPs in arbitrary order.

The Teradata documentation describes this mechanism for all-AMP READ, WRITE, and EXCLUSIVE requests, as well as ACCESS locking on load-isolated tables.

Pseudo Table Locks

Pseudo table locks are different.

They are rowhash-level locking mechanisms associated with particular data dictionary operations. They should not be confused with the reserved-RowHash proxy mechanism used to serialize all-AMP locking.

This distinction matters in our case because a single-row INSERT into a user table belongs to neither category.

It is a single-AMP RowHash operation.

It does not require an all-AMP table lock, and therefore it does not participate in the proxy-lock serialization used by the all-AMP request.

The Proxy Lock Follows the Lock Level

This can be demonstrated by changing the request while keeping everything else as similar as possible.

Case	Lock level	Reserved RowHash step
single-row INSERT VALUES, ordinary table	RowHash	no
single-row INSERT VALUES, load-isolated table	RowHash	no
UPDATE restricted by PI	RowHash	no
UPDATE all rows	Table	yes
INSERT ... SELECT	Table	yes
same single-row INSERT with LOCKING TABLE ... FOR WRITE	Table	yes

The final test is particularly useful because the DML itself has not changed.

The statement is still a single-row INSERT.

Only the requested lock level is changed.

As soon as the request becomes table-level and all-AMP, the reserved RowHash step appears.

The conclusion is therefore not that INSERT statements somehow avoid proxy locking while SELECT statements use it. Nor is the existence of the proxy determined directly by whether load isolation is enabled.

The decisive factor is the lock scope.

The ordinary data RowHash locks used by the single-row writer are not the reserved-RowHash proxy locks used to serialize all-AMP locking.

That difference is what allows the reader and writer to become involved in a distributed wait cycle.

Verifying the Execution Instead of Trusting EXPLAIN Alone

EXPLAIN tells us what Teradata intends to execute, but when diagnosing locking behavior, it is useful to verify what actually happened.

Step logging can be enabled with:

REPLACE QUERY LOGGING
WITH LOCK = 100, OBJECTS, SQL, STEPINFO
ON ALL;

Teradata identifies lock steps as MLK.

During one loading session, 23,640 of the loader’s INSERT requests executed as single-AMP requests with zero MLK steps.

The all-AMP operations executed in the same period, including READ-lock scans and DELETE operations, did contain the expected MLK steps.

This confirms that the behavior seen in EXPLAIN was also the behavior actually executed by the database.

There is an interesting asymmetry here.

The writer’s single-row INSERT is a single-AMP operation and therefore does not enter the all-AMP proxy-lock queue.

The reader’s:

SELECT COUNT(*)

is a full-table scan.

It requires all AMPs and, on a load-isolated table, participates in the corresponding proxy-lock mechanism.

One request therefore enters the serialization mechanism while the other does not.

That is sufficient for the two requests to collide outside the protection the proxy mechanism is intended to provide.

How the Global Deadlock Forms

Assume the loader has autocommit disabled and commits after a batch of up to 1,000 rows.

The writer inserts its first row.

Suppose the row hashes to AMP 1.

Because the table is load isolated and the statement is NCLDI, the transaction obtains an EXCLUSIVE RowHash lock on AMP 1.

The transaction remains open, so that lock remains held.

The monitoring query now starts:

LOCKING ROW FOR ACCESS
SELECT COUNT(*) FROM ORDERS;

Because the query must scan the complete table, it begins acquiring the locks required across the AMPs.

Suppose it successfully obtains ACCESS on AMP 2.

It subsequently reaches AMP 1, where the writer already holds its EXCLUSIVE RowHash lock.

ACCESS cannot pass EXCLUSIVE, so the reader waits.

At this point, there is still no deadlock.

We have only:

Reader -> waiting for Writer

The writer now processes another row.

Suppose that row hashes to AMP 2.

AMP 2 is currently protected by the reader’s ACCESS lock.

The writer therefore waits.

The dependency graph has become:

Reader
  |
  | waits for
  v
Writer on AMP 1
  ^
  | waits for
  |
Reader on AMP 2

The cycle is complete.

Neither transaction can continue.

The proxy-lock mechanism did not prevent it because the writer was acquiring ordinary data RowHash locks rather than passing through the reserved-RowHash proxy used for all-AMP locking.

At this point, prevention has failed, so Teradata’s deadlock detection must eventually identify and resolve the cycle.

This also explains the unusually long waits.

During testing, requests tended either to complete in approximately 15 seconds or to remain blocked for between 139 and 263 seconds.

There was very little in between.

The long delays correspond to the global deadlock detection cycle rather than normal short-term blocking.

The Lock Log Shows the Conflict Directly

DBQL alone normally tells us which query was aborted. To understand the actual conflict, lock logging is required:

REPLACE QUERY LOGGING
WITH LOCK = 100, OBJECTS, SQL
ON ALL;

There are two practical details worth remembering when using this information.

First, lock-log rows are buffered. During testing, I used:

FLUSH QUERY LOGGING WITH ALL;

to make the records visible immediately rather than waiting for the normal flush interval.

Second, some timestamp columns, such as ParentReqStartTime, may contain:

0000-00-00 00:00:00.000000

Teradata accepts this value, but strict client libraries may not. When analysing the lock log, casting timestamp columns explicitly is safer than selecting every column with SELECT *.

For the deadlock described above, the lock log showed:

Blocker:   Exclusive / RowHash
Blocked:   Access / Table
GlobalDeadLock = true

The wait time was recorded in centiseconds.

This corresponds directly to the mechanism predicted from the execution plans: an NCLDI writer holding an EXCLUSIVE RowHash lock and an all-AMP ACCESS reader waiting for it.

Do Not Check Only One Deadlock Flag

One of the more useful lessons from this investigation had nothing to do with load isolation itself.

Teradata does not have a single deadlock indicator in the lock log.

There are three:

LocalDeadLock
GlobalDeadLock
FallBackDeadLock

Initially, I looked at the global deadlock information and concluded that the observed deadlocks were all global.

That conclusion was wrong.

Across 46 captured lock rows, the distribution was:

Rows	Kind	Aborted	Delay
18	normal blocking	no	~4.3 s
20	fallback deadlock	yes	0.00 s
8	global deadlock	yes	139-263 s
0	local deadlock	–	–

This distinction is important because the underlying mechanisms are completely different.

A wait of several minutes pointed to the global deadlock mechanism.

A fallback deadlock was detected immediately.

The same error number therefore does not necessarily imply the same root cause.

Another useful precaution is to verify which transaction was actually aborted.

Do not simply assume that the blocked request shown in a lock-log row was the deadlock victim. Join the lock record’s QueryID back to DBQL and confirm it.

In this test, the blocked query was indeed the victim in all 28 captured deadlock cases, but that should be established from the data rather than assumed.

The same applies to the often-repeated rule that the youngest transaction in the cycle is always aborted.

For the fallback deadlocks, the victim transaction had actually started earlier than the blocker in 16 of the 20 cases.

Deadlock diagnosis becomes much more reliable once these details are measured instead of inferred.

A Second Deadlock Appeared After Fixing the First

The obvious solution to the NCLDI problem is to stop loading the target table one row at a time.

Instead, I staged each batch and moved the complete batch into the target with one set-based statement:

INSERT INTO target
SELECT ...
FROM staging;

This changes the shape of the operation.

The target load becomes an all-AMP operation and can operate as CLDI rather than NCLDI.

The reader deadlocks disappeared immediately.

However, another class of deadlock appeared on the staging table.

The reason was FALLBACK.

With FALLBACK enabled, Teradata stores a second copy of every row on another AMP. This provides resilience against AMP failure and is normally invisible to the application, but locking must account for both copies.

A modification of a row may therefore involve locks on two AMPs:

Primary AMP
Fallback AMP

Two requests that target the same RowHash can reach those AMPs in opposite order.

For example:

Request A:
locks primary
waits for fallback

Request B:
locks fallback
waits for primary

The result is another cycle, but it is a different type of deadlock from the reader/writer global deadlock described earlier.

Teradata’s documentation describes this fallback race and the mechanisms used to protect certain dictionary operations from it.

User tables do not receive the same general pseudo-table-lock protection.

The lock log makes the difference easy to see.

The fallback deadlocks were recorded with a delay of:

0.00 s

whereas the global deadlocks remained blocked for minutes.

This is a useful diagnostic clue.

If every deadlock you observe appears immediately, there may be no global detection interval involved at all. You may instead be looking at a fallback deadlock.

Why WITH ISOLATED LOADING FOR NONE Is Not a Neutral Setting

Another tempting approach is:

WITH ISOLATED LOADING FOR NONE

At first glance, this looks like a way to keep the table declaration while disabling isolated-loading behavior.

That is not what happens.

The table remains a load-isolated table, and the single-row modification remains NCLDI.

In a controlled test, a concurrent reader waited:

4.31 s    WITH ISOLATED LOADING FOR NONE
4.30 s    isolated loading fully enabled
0.04 s    load-isolation declaration removed

The important distinction is between disabling the supported isolated-loading operation types and removing the load-isolation property from the table.

Those are not equivalent operations.

Why INSERT WITH CONCURRENT ISOLATED LOADING Is Not a Per-Row Solution

Teradata also allows a statement to request concurrent isolated loading explicitly:

INSERT WITH CONCURRENT ISOLATED LOADING INTO ...

This does turn the operation into CLDI.

The execution plan also shows why applying it to every individual row would be unattractive:

1) lock the table for write on a reserved RowHash
   to prevent global deadlock

2) lock the table for write

3) Begin Isolated Load

4) INSERT step (concurrent load isolated)
   + lock DBC.TVM ...
   + UPDATE step against DBC.TVM

5) End Isolated Load

For one inserted row, Teradata has now turned a small single-AMP modification into an operation involving a table-level WRITE lock, isolated-load handling, and dictionary activity.

That is not what the feature was designed for.

The clause effectively says:

Treat this statement as an isolated load.

That is reasonable when the statement loads a meaningful set of rows.

It is an expensive way to insert one row millions of times.

Load isolation works best when there are relatively few, relatively large load operations. It is not a replacement for normal row-level DML concurrency.

What Removing Load Isolation Changed

After understanding the mechanism, I tested the simplest alternative: remove the load-isolation declaration and leave the existing row-by-row loader unchanged.

Two controlled comparisons were performed with identical source data.

In the first test, ten runs were executed for each table definition, draining the pipeline before switching between the two configurations.

	Isolated	Not isolated
throughput	139.9 rows/s	211.1 rows/s
drain phase	7.60 s	2.52 s

Removing load isolation increased throughput by approximately 51%, while the final drain phase became roughly three times shorter.

The improvement was not simply the consequence of eliminating deadlock aborts. The additional load-isolation bookkeeping and waiting disappeared as well.

A second test continued execution until the first deadlock occurred.

	Declaration removed	Declaration present
runs	100	stopped at run 9
deadlocks	0	1 reader aborted

One hundred consecutive runs completed without a deadlock after the declaration was removed.

With load isolation enabled, a monitoring reader was aborted during the ninth run.

It would be incorrect to interpret “run 9” as a statistical deadlock frequency. It is only one observation.

The useful result is that 100 consecutive runs completed cleanly without the declaration, while the throughput improvement was also visible in runs where no deadlock occurred at all.

In other words, the performance difference was not dependent on waiting for a deadlock to happen.

Removing Load Isolation Is Not Automatically the Right Answer

At this point it would be easy to conclude that load isolation should simply be removed.

That would be the wrong conclusion.

The correct choice depends just as much on the read workload as on the load workload.

Consider a reporting query that runs for 30 minutes while the table continues to receive data.

The outcome differs considerably depending on the table definition and the query’s lock.

Configuration	Query lock	Result
no load isolation	ACCESS	reader and writer continue
no load isolation	default READ	load can wait for the query
load isolation + row-by-row load	ACCESS / LOAD COMMITTED	reader/writer blocking can occur
load isolation + set-based load	LOAD COMMITTED	load continues and reader sees a consistent load state

The second case is particularly important.

A normal query typically requests a READ lock.

READ and WRITE are incompatible.

If a long-running report obtains a table READ lock, modifications requiring conflicting WRITE locks must wait.

A thirty-minute report can therefore stop a continuous ingestion pipeline for thirty minutes.

For a nightly batch load, this may be manageable.

For a continuously running ingestion process, it may constitute an outage.

If load isolation is removed, consuming views should therefore explicitly request ACCESS locking when dirty-read semantics are acceptable:

LOCKING ROW FOR ACCESS
SELECT ...

There is another subtlety worth checking before changing the table.

If a query contains:

LOCKING TABLE ... FOR LOAD COMMITTED

against a table that is not load isolated, the clause does not necessarily fail.

Its behavior degrades to ACCESS semantics.

That may be acceptable, but it is no longer the same consistency guarantee that the query received from a properly used load-isolated table.

This is why removing the declaration must be treated as an architectural decision rather than merely a tuning change.

The Case Where Load Isolation Is Doing Exactly What You Want

There is one scenario in which the argument for keeping load isolation becomes much stronger: multiple concurrent writers.

Everything measured above assumes one writer per target table.

During testing of the staging solution, I accidentally ran two writers against the same table and quickly produced another series of deadlocks.

Twenty occurred.

The mechanism was again related to FALLBACK locking across AMPs.

With concurrent writers, CLDI can provide important protection.

A concurrent isolated load uses an all-AMP table lock. Because that request passes through the proxy-lock mechanism, concurrent all-AMP load requests are serialized through the gatekeeper rather than independently constructing conflicting lock paths across the AMPs.

In that workload, load isolation is solving a real concurrency problem.

This also explains why apparently contradictory experiences with the feature can both be correct.

One system may see fewer deadlocks after enabling concurrent isolated loading.

Another may see more.

The result depends on the shape of the workload.

A system with multiple large concurrent loaders is not equivalent to a system with one application issuing thousands of individual single-row INSERT statements.

Three Practical Ways Out

Once the mechanism is understood, there are three realistic designs.

1. Make the Load Set-Based

Stage each batch and move it to the target with one operation:

INSERT INTO target
SELECT ...
FROM staging;

This changes the load from thousands of NCLDI single-row modifications into a set-based operation that can use CLDI.

It preserves the benefits of load isolation and avoids the EXCLUSIVE RowHash behavior that caused the reader deadlocks.

The cost is additional infrastructure.

A staging table is required for each target or load stream, and the physical arrival order of individual rows is no longer represented by the order in which they reached the target.

For most warehouse loads, this is normally the cleanest design.

2. Use an Explicit Isolated Load Group

Another option is to group many individual statements into one explicit isolated-load operation using constructs such as:

BEGIN ISOLATED LOADING
...
USING QUERY_BAND
...
IN MULTIPLE SESSION

This allows the application to continue issuing row-oriented modifications while treating them as part of one coordinated isolated load.

The operational complexity is higher.

Something must reliably close the load, including after application failure.

Readers also see data according to the checkpoint behavior of the load group.

For a load process that effectively never stops, the group may remain open indefinitely, which means the checkpoint interval becomes part of the application’s data-freshness design.

3. Remove Load Isolation

For a workload with:

• one writer per table,
• row-by-row ingestion,
• no requirement for LOAD COMMITTED consistency,
• and readers that explicitly use ACCESS locking,

removing load isolation can be the simplest and fastest solution.

This returns the writer to normal RowHash WRITE locking, which ACCESS readers can pass.

Before making that change, however, inspect the consuming SQL.

In particular, determine whether applications or views depend on:

LOCKING ... FOR LOAD COMMITTED

If the table stops being load isolated, those consumers may silently receive ACCESS-style semantics instead of the consistency model they previously expected.

That is something to discover before changing the table definition, not after.

The Real Lesson

Load isolation is not simply a property that makes a Teradata table “safer to load.”

It assumes a particular workload.

When used with large, set-based load operations, it can provide exactly the concurrency model for which it was designed: loaders proceed while readers continue to see a consistent committed state.

When the same table is fed through thousands or millions of individual single-row INSERT statements, the result can be completely different.

Those statements become NCLDI modifications.

Instead of ordinary RowHash WRITE locks, they use RowHash EXCLUSIVE locks.

An ACCESS reader that would pass the writer on an ordinary table can therefore be forced to wait.

Because the single-row writer does not participate in the all-AMP proxy-lock serialization used by the reader, the two requests can also form a distributed lock cycle across AMPs.

The consequence is counterintuitive but important:

Enabling load isolation does not guarantee more concurrency. The load must have the shape for which load isolation was designed.

The right question is therefore not:

Should this table have load isolation enabled?

The better questions are:

How is this table loaded?

and:

How is this table read?

Only after both are known does the table property have a correct answer.

Reproducing the Behavior

The basic behavior can be reproduced with a small table:

CREATE MULTISET TABLE DWHPRO.ISO_TEST, FALLBACK,
  WITH CONCURRENT ISOLATED LOADING FOR ALL
(
    id  INTEGER NOT NULL,
    txt VARCHAR(50)
)
PRIMARY INDEX (id);

In session 1, begin a transaction and insert several rows individually without committing.

For example:

BT;

INSERT INTO DWHPRO.ISO_TEST VALUES (1, 'A');
INSERT INTO DWHPRO.ISO_TEST VALUES (2, 'B');
INSERT INTO DWHPRO.ISO_TEST VALUES (3, 'C');

-- Do not commit yet.

In session 2, execute:

LOCKING ROW FOR ACCESS
SELECT COUNT(*)
FROM DWHPRO.ISO_TEST;

The reader waits for the writer.

Now remove the load-isolation property and repeat the same test.

Before changing the property, release deleted rows:

ALTER TABLE DWHPRO.ISO_TEST RELEASE DELETED ROWS;

Then remove load isolation:

ALTER TABLE DWHPRO.ISO_TEST,
WITH NO ISOLATED LOADING;

In the controlled test, the reader wait changed from approximately:

4.30 s

to:

0.04 s

with no change to the reader or to the application’s single-row INSERT pattern.

There are two syntax details worth remembering.

RELEASE DELETED ROWS does not use a comma after the table name:

ALTER TABLE DWHPRO.ISO_TEST RELEASE DELETED ROWS;

The WITH clause does:

ALTER TABLE DWHPRO.ISO_TEST,
WITH NO ISOLATED LOADING;

A load-isolated table can also reject the ALTER with error 9899 if deleted rows have not first been released.

Finally, do not confuse:

WITH NO ISOLATED LOADING

with:

WITH ISOLATED LOADING FOR NONE

The first removes the load-isolation property.

The second leaves the table load isolated while disabling the listed isolated-loading operations.

As the locking tests demonstrate, those are very different things.