BTEQ .PACK and deadlock

Tools
Enthusiast

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?
Tags (3)
8 REPLIES
Junior Contributor

Re: BTEQ .PACK and deadlock

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
Enthusiast

Re: BTEQ .PACK and deadlock

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.
Junior Contributor

Re: BTEQ .PACK and deadlock

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
Enthusiast

Re: BTEQ .PACK and deadlock

Learned few things new. Thanks Dieter.
Enthusiast

Re: BTEQ .PACK and deadlock

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.
Enthusiast

Re: BTEQ .PACK and deadlock

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.

Re: BTEQ .PACK and deadlock

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.

Enthusiast

Re: BTEQ .PACK and deadlock

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.