All Forums Tools
njvijay 19 posts Joined 08/09
18 Apr 2011
BTEQ .PACK and deadlock

I am trying to understand why BTEQ .PACK command creates dead lock on tables with NUPI.

I had a scenario where I needed to load nearly 2 million rows on mutiset table with NUPI. I had lot of duplicates on NUPI columns.

When sessions set to 4, PACK set to 2000, my bteq script's run time output displayed the following:

*** Growing Buffer to 399
*** 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

To avoid deadlock, I needed to

1. Remove duplicates and load (fast) or
2. Set session to 1 and Pack 2000 (Still it loaded with acceptable speed) or
3. Set multiple sessions and remove pack (Extremely slow)

Can anybody give me a hint why do I get deadlock when I tried to load NUPI table by setting .PACK 2000 with multiple sessions?

dnoeth 4628 posts Joined 11/04
19 Apr 2011

BTEQ INSERTs require a Write lock on a Row Hash level.
When you got 2 rows with the same PI in different sessions both try to place the same lock, if it's a single row insert one session has to wait until the other finishes.
Now you got 2000 rows per PACK, the probability to get that multiple times is quite high, in worst case session 1 waits for session 2 to be finished and vice versa -> deadlock.

In your case solution #2 is the best (or switch to TPump + ARRAYSUPPORT + SERIALIZE option)

Dieter

Dieter

njvijay 19 posts Joined 08/09
20 Apr 2011

Thanks Dieter.

I have not used TPUMP much. Once I had same deadlock behavior when I used TPUMP PACK without SERIALIZE option. SERIALIZE eliminated the deadlock.I will find out more information about ARRAYSUPPORT.

dnoeth 4628 posts Joined 11/04
20 Apr 2011

SERIALIZE sends rows with the same Row Hash to the same session, thus avoids different sessions locking the same value.

ARRAYSUPPORT is exactly the same as BTEQ's PACK. Based on the SQL in DML LABEL it may not be allowed, but if it's possible it should be used as it's much faster, especially if there are errors.

Dieter

Dieter

njvijay 19 posts Joined 08/09
21 Apr 2011

Learned few things new. Thanks Dieter.

ramjasmeenakshi 19 posts Joined 04/11
21 Apr 2011

Pack will overrite the bteq normal behave of row by row processing to width set by pack.So why we need fload and mload.Is same performance can be achived from bteq with pack.

Meenakshi Swami

njvijay 19 posts Joined 08/09
28 Apr 2011

FLOAD and MLOAd use data blocks to load tables. They are much faster than BTEQ multistatement requests (PACK). I prefer to use BTEQ for small set data load.

manoj.chakri 2 posts Joined 08/09
15 Mar 2012

We have a custom built ETL application which loads more than 30,000 files per day in near real time with number of rows in each file ranging from 100 to 89000 using Bteq imports with optimal pack factors and single sessions. They run like a charm.

But we cant use them as Fastload works. As I read it some where Fastload is the darling of Teradata's all utilities and systems. Because in another near real time system where we load 70 files per hour with rows ranging from 1,00,000 rows to 9,00,000 rows per file, the bteq import takes huge amount of time and shoots out of that hour to load. Where as when you use fastload it loads in 6-10 minutes for all 70 files. So tool for purpose.

vijaydf 16 posts Joined 06/12
09 Oct 2013

hi,
in TPT stream operator, i am getting a deadlock on SET UPI table, i have tried SERIALIZE on (UPI) , but still i am getting the message in qrylog table "Transaction ABORTed due to deadlock.  " , but the job completed sucessfully.                                                                                                                                                                                                                        

Vijay Mani

You must sign in to leave a comment.