Lock a table for entire TPT load

Database
Enthusiast

Lock a table for entire TPT load

We experienced a failure in our loads last night. We have a TPT that has a DELETE step and an INSERT step. The time between the DELETE steps firstResponse and the INSERT steps firstStep is 6.3 seconds. In that time a procedure ran that selected data from this empty table. 

 

The INSERT consumes data from an OPERATOR that contains a SELECT from a stage table. I assume the 6.3 seconds was the time it took to read that stage table but I'm not 100% sure on that.

 

Is there anyway in a multistep TPT to lock a table that is being loaded via TPT until the TPT is complete? 


Accepted Solutions
Junior Contributor

Re: Lock a table for entire TPT load

Assuming it's using the DDL operator you can do a Multi-Statement-Request:

APPLY (DELETE ...; INSERT ...) 
TO ...
1 ACCEPTED SOLUTION
4 REPLIES
Junior Contributor

Re: Lock a table for entire TPT load

Assuming it's using the DDL operator you can do a Multi-Statement-Request:

APPLY (DELETE ...; INSERT ...) 
TO ...
Enthusiast

Re: Lock a table for entire TPT load

Dieter, that is what I was hoping was possible. All of our TPT scripts are copy pasta so I wasn't sure if Multi-Statement would be supported. I will test this approach and implement to avoid the dreaded 6.3 second window from destroying other loads.

 

Thanks again!

Highlighted
Enthusiast

Re: Lock a table for entire TPT load

Now that I look at it, it's a DDL operator for the DELETE, but the INSERT is on an UPDATE operator selecting from an EXPORT operator. That seems a little silly. Surely that is more overhead then just an `INSERT... SELECT...` in a DDL operator, right?

Junior Contributor

Re: Lock a table for entire TPT load

There's no reason to use UPDATE, as the target table is empty a LOAD would be more efficient.

But when both source and target table are on the same Teradata box most efficient is a simple Insert/Select, of course.