avoid exclusive lock during inserts on a table

Database
Enthusiast

avoid exclusive lock during inserts on a table

Hi,
While I am loading data into a table , using simple inserts but of a large volume (in millions), then I am faced with a problem that TD won't allow me to fire an insert from another session into the same table, until the first insert goes through (or even errors out).

Are there ways to avoid this deadlock by suitably defining partitions and primary indexes ? Or any other method to overcome this challenge.

Any pointers will be highly appreciated.
9 REPLIES
Enthusiast

Re: avoid exclusive lock during inserts on a table

I assume you are using BTEQ to load the data into a table that has a Non Unique Primary Index. If the Primary Index column has a large number of repeating values

AND

If your insert statements are within an explicit transaction (ie BEGIN TRANSACTION and END TRANSACTION) its likely that your one of your sessions is experiencing the problem you described.

For an insert statement the LOCK will be a ROWHASH level write lock and not an EXCLUSIVE LOCK (Unless you have explicitly requested this after the BEGIN TRANSACTION)
Enthusiast

Re: avoid exclusive lock during inserts on a table

To avoid the problem either run the inserts as implicit transactions (without BEGIN TRANSACTION and END TRANSACTION) or use a load utility (FASTLOAD/ MULTILOAD)
Enthusiast

Re: avoid exclusive lock during inserts on a table

Thanks for the response.
What I am trying to do is:
From two or more sessions, I am going to fire following queries:

INSERT INTO tab1 AS SELECT * FROM tab2 WHERE <>;

So unless one insert goes through , I assume , there is no way to let other inserts process.

I want to avoid MLOAD altogether .

Please let me know if more elaboration is required.
regards
Devopam
Enthusiast

Re: avoid exclusive lock during inserts on a table

Some of us may have assumed that when you mentioned "simple insert" you meant a single-row insert. We tend to use the term "bulk insert" when referring to an insert that is sourced from a query expression. That kind of insert must be executed serially at the table level.
Enthusiast

Re: avoid exclusive lock during inserts on a table

The multiple Insert Select statements execute serially as they contend for table level Write Locks on the destination table.

It will help if you run the multiple inserts as a Multi Statement Request in a single session. But you may run out of spool space considering the volume of data you are trying to load.

example: Insert into Tab1 Select * from Source1;Insert into Tab1 Select * from Source2;

Enthusiast

Re: avoid exclusive lock during inserts on a table

Thanks for the detailed explanation everyone.
Trust it means that we better change the app design to accommodate this expected behavior of Teradata.

regards
Devopam
Enthusiast

Re: avoid exclusive lock during inserts on a table

Hi all,

I need to build summary at various levels and on various conditions on the detail data. I would need to insert each summary result in my summary table. There would be near about 100 inserts to perform from detail to summary table.Each insert would be independent of the data already inserted in the summary table.My detail table data would not be changed/accessed by any other db/user while these inserts are acting. I believe, the select from the detail table would just cause a read lock on the detail which should allow another read lock cause by another insert to act simultaneously.

Currently, these are being performed serially. However, I am looking for ways such that I can perform it parallelly. I know a insert would perform a write lock on my summary table. I plan to dowgrade the lock by creating a view over the summary table and downgrading the lock to read/access. No other user/query accesses my summary table while the loading occurs. Each insert would insert around a 1000 rows. Please suggest if this sounds feasible or there are some demerits that I might have overlooked.

The logon id for all the inserts will be the same each insert shall create a diff session for insertion.(I know there is a limit of 120 sessions per ID).

Regards,
Ayush Jain
Junior Contributor

Re: avoid exclusive lock during inserts on a table

There's no way to downgrade a WRITE lock.

If you want parallel execution of your queries you should put multiple SELECTs into a MultiStatement Request.

Dieter

Enthusiast

Re: avoid exclusive lock during inserts on a table

Thanks Dieter.