Transaction Deadlock issue in BTEQ - Failure 3510 Too many END TRANSACTION statements

Tools

Transaction Deadlock issue in BTEQ - Failure 3510 Too many END TRANSACTION statements

I have 2 BTEQs having following type of Statements. Both are submitted at the same time and both are trying to insert in the same table.

BTEQ1
---------------------------------
BT;
INSERT INTO T1 SEL * FROM GTT;
.IF ERRORCODE <> 0 THEN .GOTO LABELS
ET;
.IF ERRORCODE <> 0 THEN .GOTO LABELS
.QUIT
.LABEL LABELS
.QUIT ERRORCODE

BTEQ2
---------------------------------
BT;
INSERT INTO T1 SEL * FROM GTT;
.IF ERRORCODE <> 0 THEN .GOTO LABELS
ET;
.IF ERRORCODE <> 0 THEN .GOTO LABELS
.QUIT
.LABEL LABELS
.QUIT ERRORCODE

-----------------------------
Now, it's causing the Deadlock and both got aborted by giving the following error in both the logs.

+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------
INSERT INTO T1 SEL * FROM GTT;
*** Failure 2631 Transaction ABORTed due to deadlock.
Statement# 1, Info =0
*** Total elapsed time was one minute and 7 seconds.

*** Warning: Attempting to resubmit last request.

*** Insert completed. 2 rows added.
*** Total elapsed time was 3 minutes and 57 seconds.

.IF ERRORCODE <> 0 THEN .GOTO LABELS

ET;
*** Failure 3510 Too many END TRANSACTION statements.
Statement# 1, Info =0
*** Total elapsed time was 1 second.

.IF ERRORCODE <> 0 THEN .GOTO LABELS
.GOTO ERRORS

.QUIT 0
*** Skipped.

.LABEL LABELS

.QUIT ERRORCODE
*** You are now logged off from the DBC.
*** Exiting BTEQ...
*** RC (return code) = 3510
+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------

Now, there is an Error Checking present after Insert, but that won't executed and it went to ET;
After Transaction ABORTed due to deadlock, why it's attempting to resubmit the last request and why after ET, too many End Transaction Failure came - confusing my mind.

Please share your thoughts by providing the proper example if possible.
1 REPLY
Enthusiast

Re: Transaction Deadlock issue in BTEQ - Failure 3510 Too many END TRANSACTION statements

When you get the deadlock, the transaction is aborted but the session retries - so you are in a new transaction!
I would do too things:
Before the BT in both sessions, tell Bteq not to retry.
After the BT, but before any DML statements, lock any tables being updated to avoid the deadlocks.

So your script will be:

BTEQ1
---------------------------------
.RETRY OFF
BT;
Locking T1 for Write;
INSERT INTO T1 SEL * FROM GTT;
.IF ERRORCODE <> 0 THEN .GOTO LABELS
ET;
.IF ERRORCODE <> 0 THEN .GOTO LABELS
.QUIT
.LABEL LABELS
.QUIT ERRORCODE

BTEQ2
---------------------------------
.RETRY OFF
BT;
Locking T1 for Write;
INSERT INTO T1 SEL * FROM GTT;
.IF ERRORCODE <> 0 THEN .GOTO LABELS
ET;
.IF ERRORCODE <> 0 THEN .GOTO LABELS
.QUIT
.LABEL LABELS
.QUIT ERRORCODE