Bteq Import, Delete Statement on same table at once causing deadlock

Tools & Utilities
Enthusiast

Bteq Import, Delete Statement on same table at once causing deadlock

Hi Experts,

We have an application in which we load tables by using bteq import from small files. It also has a delete statement on the same table before bteq import starts. We made sure that the same script will not be called at once. But due to some issue in the calling mechanism, the same script was called twice at the same time.

So the flow was like this....
->1st instance of the script logged in first and completed delete statement on the table1.
->2nd instance of the script logged in second and tried to start deleting the table1.
->At the same time when the 2nd instance is trying to delete, 1st instance started to bteq import the records from a file into table1.

This situation caused a deadlock. After checking the explain plans, we observed that the delete statement is trying for a write lock and bteq import was using a row hash lock. So this caused dead lock. In this case what can be done to avoid this situation of deadlock. As bteq by default resubmits requests in case of deadlocks, we thought this would never occur. But it happened. Can any body please help with your suggestions?
4 REPLIES
Enthusiast

Re: Bteq Import, Delete Statement on same table at once causing deadlock

You need to ensure your delete and inserts all occur within a BT/ ET block and that the table is locked throughout the process.

So:

.Retry Off
BT ;
Locking Tbl1 for Write ;
Delete From Tbl1 ;
.If ErrorStatus <> 0 Then .Goto Rback
.Repeat *
.Import ....
Insert Into Tbl1 ...;
.If ErrorStatus <> 0 Then .Goto Rback
ET ;
.If ErrorStatus <> 0 Then .Goto Rback
.Quit 0

.Label RBack
Rollback 'Operation Aborted';
.Quit 4

If you are using explicit transaction in BTEQ, it is safest to turn retry off. Otherwise, if get a DBC restart during the process, the transaction so far can be terminated but remaining operations picked up, giving very confusing results.
Locking the table for Write at the start of the process will leave it locked for Write throughout the transaction.
Enthusiast

Re: Bteq Import, Delete Statement on same table at once causing deadlock

Hi Jimm, Thank you very much for your response.

I will definitely have your locking and BT ET applied. Thanks for the info. Retry off may help. And rollback 'Operation oborted' in the label is interesting. Can you please explain what it is actually doing at system level.

I havent seen ERRORSTATUS check, but i think its a typo error for ERRORCODE.
Enthusiast

Re: Bteq Import, Delete Statement on same table at once causing deadlock

Sorry, that should have been ERRORCODE.

Rollback will cause anything from the BT to be backed out of the database and restored to a state as it was whe the BT was issued. The message 'Operation Aborted' will be printed in the bteq log. I would put it there so that anyone investigating the log is reminded that it is not just the last statement which is aborted - it is the whole process.

BTW. You will find that the Delete takes longer because it deletes row by row if it is in a transaction, rather than a fastpath delete.

If you are using transactions, you should always set retry off. If there is a system failure during a transaction, everything up to the point of failure is rolled back during system recovery. If Retry is on, bteq will reissue the last request - the last SQL statement (not the complete transaction) and continue from there. So the delete and some inserts will be rolled back and some of them could potentially run on - giving you incorrect contents in your table.
Enthusiast

Re: Bteq Import, Delete Statement on same table at once causing deadlock

Thanks Jimm. I think .Retry off will take care of most of the things in these cases as I am using .REPEAT * .SET REPEATSTOP ON and .PACK 5000 for the bteq import. And delete is not part of the import sequence.

Rollback 'Operation oborted' was a new learning for me. We dint know that we can write out a statement to log with Rollback. I will try it out. Any ways we have now made sure that same script on same table for delete and import will not be called at the same time more than once.