Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Tue, 24 May 2005 @ 09:25:37 GMT


     
  <Prev Next>  
<<First
<Prev
Next> Last>>  


Subj:   How to avoid Deadlock?
 
From:   LEE,JAE HYUK

Hi,

I'm trying to access a queue table from two sessions (BTET) at the same time. One session stores data into queue table and the other session retrieves data from the queue table.

Even if I use locking mechanism before inserting and selecting data from queue table in both sessions, I still get the deadlock problem. Although the number of deadlock problem gets reduced but I'd like to avoid it completely.

I'll illustrate how my BTEQs look like. Please correct me if you find anything wrong.

Thank you,


     ------------------------------------------------
     Session 1 (BTEQ)
     ------------------------------------------------
     .set session transaction BTET

     /* ommit declaration */

     .Repeat *

     LOCKING TABLE QueueTable2 FOR WRITE
     ;INSERT INTO QueueTable2
     SELECT AND CONSUME TOP 1 x,y,z FROM QueueTable1;

     .logoff;


     ------------------------------------------------
     Session 2 (BTEQ)
     ------------------------------------------------
     .set session transaction BTET

     .Repeat *
     Call stored_procedure_1();


     ------------------------------------------------
     stored_procedure_1
     ------------------------------------------------
     CREATE PROCEDURE ........

     LOCKING TABLE QueueTable2 FOR WRITE;

     SELECT AND CONSUME TOP 1 x, y, z
     FROM QueueTable2;



     ------------------------------------------------
     Error Message:
     ------------------------------------------------
     Failure 2631 stored_procedure:Transaction ABORTed due to Deadlock.


     
  <Prev Next>  
<<First
<Prev
Next> Last>>  
 
 
 
 
 
 
 
 
  
  Top Home Privacy Feedback  
 
 
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 15 Jun 2023