Archives of the TeradataForum

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

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

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