Rowhash deadlock during BTEQ insert

Tools
Enthusiast

Rowhash deadlock during BTEQ insert

Hi there,

I am experiencing a deadlock situation while performing a simple insert using a BTEQ script,and am wondering if anyone else has experienced the same problem and knows of a workaround?

It is a simple one column table:

****************************

CREATE MULTISET TABLE DB.Table1 ,

     NO FALLBACK ,

     NO BEFORE JOURNAL,

     NO AFTER JOURNAL,

     CHECKSUM = DEFAULT

     (

      ACC_ID_NO CHAR(12) CHARACTER SET LATIN NOT CASESPECIFIC)

PRIMARY INDEX ( ACC_ID_NO );

*****************************

I am inserting 18,000 rows from a text file.

During the insert I can see my 5 BTEQ sessions being blocked from Viewpoint and then moves into a deadlock situation:

e.g.

BLOCKED BY

USERNAME DBA00001

HOST 1

SESSION ID 5066231

LOCK TYPE Write

STATUS   Granted

LOCKED  RowHash DB.Table1

**************************

Also the output from BTEQ is the same:

Teradata BTEQ 13.10.00.03 for WIN32.

 Copyright 1984-2010, Teradata Corporation. ALL RIGHTS RESERVED.

 Enter your logon or BTEQ command:

....................

..................

..............

 *** Logon successfully completed.

 *** 5 Sessions logged on.

 *** Teradata Database Release is 12.00.03.12

 *** Teradata Database Version is 12.00.03.14

 *** Transaction Semantics are BTET.

 *** Session Character Set Name is 'ASCII'.

 *** Total elapsed time was 11 seconds.

 BTEQ -- Enter your SQL request or BTEQ command:

.......................

.......................

.................................

USING ACC_ID_NO (varchar(15))

INSERT INTO DB.Table1 values(:ACC_ID_NO);

 *** Starting Row 0 at Wed Mar 14 09:47:19 2012

 *** Failure 2631 Transaction ABORTed due to deadlock.

                Statement# 1, Info =0

 *** Warning: Attempting to resubmit last request.

 *** Failure 2631 Transaction ABORTed due to deadlock.

                Statement# 1, Info =0

 *** Warning: Attempting to resubmit last request.

*****************************

Is this because it is a one column table? I've tried with both Multiset and Set tables and its the same outcome.

It works fine when using Fastload,I'm only seeing this problem using BTEQ.

Thanks in advance,

Marcus

9 REPLIES
Supporter

Re: Rowhash deadlock during BTEQ insert

in the bteq you defined acc_id_no as varchar(15) in the table as varch(12).

Which length has the acc_id_no?

If 15 - only the frist 12 would be used and can result in many equal values.

And as you have only one column - is acc_id_no unique or non-unique?


Enthusiast

Re: Rowhash deadlock during BTEQ insert

Hi Ulrich,

Thanks for the reply.

The values in the text file are all unique.

The ACC_ID_NO values are all 12 characters long.

I have changed the BTEQ script to make this 12 too

e.g.

USING ACC_ID_NO (varchar(12))

INSERT INTO DB.Table1 values(:ACC_ID_NO);

However I am still experiencing the blocking > deadlock issue.

A certain number of rows get inserted into the table each time,but the blocking happens while it is running.

Any other suggestions?

Thanks,

Marcus

Junior Contributor

Re: Rowhash deadlock during BTEQ insert

Hi Marcus,

you probably use PACK in your script, which results in multiple inserts through a session. When multiple rows with the same PI exist this might result in deadlocks.

When using PACK in BTEQ it's recommended to switch to a single session.

Dieter

Enthusiast

Re: Rowhash deadlock during BTEQ insert

Hi Deiter,

Yes I use ".PACK 250" in the script,and 5 sessions.

This is to speed up the insert and also to have fewer rows being logged in DBQL.

If find it strange in this case however as all the values are unique in the text file.

And it only seems to happen in this one column table,all other BTEQ inserts into mult-column tables don't seem to be affected.

Regards,

Marcus

Supporter

Re: Rowhash deadlock during BTEQ insert

At least I can't reproduce this. 

I created a some 12000 ids and loaded them with bteq - see attached data, script and log. Load was quite fast.

Can you run this in your environment and check what happens there?

Supporter

Re: Rowhash deadlock during BTEQ insert

And just saw that this ids are 15 and the table is 12 :-)...

Here an set of ids with length 12

Enthusiast

Re: Rowhash deadlock during BTEQ insert

Hi Ulrich,

Thanks for the help but I made some changes based on Deiter's advice above by only using 1 session if using PACK. The BTEQ insert ran successfully and was still very quick so I will take this approach in future.

Thank you both for your help!

Regards,

Marcus

Enthusiast

Re: Rowhash deadlock during BTEQ insert

Re: Rowhash deadlock during BTEQ insert

Please any one can tell me that, which is better in PPI and Si , also give the difference between these two?.