Help needed on Global Deadlock situation

Database

Help needed on Global Deadlock situation

Hi - we are running into a Global Deadlock situation and need some help on this.

Background: We have multiple concurrent access to Teradata database, these could be insert/update/delete (via java jdbc application). Our session isolation level is set at RU, so no read locks occur and also we access all our tables via the base views (which also contains select row for access), in effect all selects should only use access locks.

we do not use exclusive lock for any situation and based on the above I am assuming read lock also does not happen, that brings us down only to the write lock as a possible contention. 

we use DML operations for insert/update/delete and we use unique primary index values in our DML queries, so hopefully these work as single-AMP operations. I even tried executing these queries with explain and found the single-AMP in the plan as below. I have given a general example to avoid privay issue, but the situation and kind of queries are the same.

explain

insert into LLVDF_3.PRSN (MMBR_ID, ACTV, CRTD_BY, CRTD_ON, DT_TM_OF_JNNG, LST_MDFD_BY, LST_MDFD_ON, NM, VRSN) values (3, 'Y', 'appusr',current_time,current_time,'appusr',current_time,'Jack Jones',1);

"  1) First, we do an INSERT into LLVDF_3.PRSN.  The estimated time for"

     this step is 0.34 seconds. 

  -> No rows are returned to the user as the result of statement 1. 

     The total estimated time is 0.34 seconds. 

explain

delete from LLVDF_3.PRSN where mmbr_id = 5004;

"  1) First, we do a single-AMP DELETE from LLVDF_3.PRSN by way of the"

     unique primary index "LLVDF_3.PRSN.MMBR_ID = 5004" with no

     residual conditions.  The size is estimated with high confidence

     to be 1 row.  The estimated time for this step is 0.01 seconds. 

  -> No rows are returned to the user as the result of statement 1. 

     The total estimated time is 0.01 seconds. 

Based on the above, I assumed that we are doing single-AMP operations. one additional info is that tables are not partitioned so far, but I think this should not affect the locking mechanism hopefully. 

Situation/TestScenario: we are doing a performance test and we have lock logger (dumplocklog) turned on, dbql, also view point with the query monitor etc.. we are running 6 types of transactions insert/update/delete on 2 different sets of tables in the same database. Basically insert into table1 (parent), then insert into table 2 (child) is part of one java transaction and delete from table2 (child), then delete from table 1 (parent) is a seperate java transaction. we run these java transaction in a concurrent access performance test using JMeter/LoadRunner. Our range of values used for the primary index are as follows:

Table1, insert --> 1 to 1000,    Table1, update --> 1001 to 2000,    Table1, delete --> 2001 to 3000

Table2, insert --> 1 to 1000,    Table2, update --> 1001 to 2000,    Table2, delete --> 2001 to 3000

when the test runs, deletes generally run faster, updates next, insert seems to be the slowest and roughly around 70 or so transactions, we are noticing Global Deadlocks 'G' in the ViewPoint LockViewer portlet, then everything becomes very slow, because we could see many contentions between different sessions.

when looking through the details in the query monitor portlet, we could see circular waiting on the locks between few sessions, also the query shows one session has insert into table2(child) which is waiting on delete from table1(parent) and when we see the same session ids in the lock viewer portlet, we could see the locks are rowhash-write locks waiting on rowhash-write locks between 2 differet sessions.

Question: Based on our teradata understanding of how primary index helps in using single-AMP operations, we should NOT have a deadlock at all, because we are not doing any all-AMP or table level access and also am assuming rowhash is unique and are record level locks, so these should not be conflicting locks (due to the UPI values being distinct).

why are we running into these Global Deadlock situation, are we missing something?

what other parameters should we monitor or look into to configure to avoid this situation?

appreciate your response/directions.

Thanks and Regards  -  Vimal