Archives of the TeradataForum

Message Posted: Wed, 23 Apr 2014 @ 20:48:29 GMT

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

An Insert statement, taking the data from a VALUES clause (perhaps controlled through a Stored Procedure), thus :

     INSERT INTO  REPORT_SUBSCRIPTION
                   (EDW_SUBSCRIPTION_ID
                     ,EDW_CUSTOMER_TYPE_CD
                     ,COU_ID_ACC
                  )
                     VALUES (
                        1
                         ,'IMP' --sP_CustomerType
                         ,'GB' --sP_CountryCode
                  );

       1) First, we do an INSERT into REPORT_SUBSCRIPTION
          in view REPORT_SUBSCRIPTION.  The estimated
          time for this step is 0.89 seconds.
       -> No rows are returned to the user as the result of statement 1.
          The total estimated time is 0.89 seconds.

     .
      (Table is a Multiset Table with a Unique Primary Index on
      EDW_SUBSCRIPTION_ID)

So, whilst it will be a Single Amp type operation, no doubt it would need an EXCLUSIVE lock against the Table, right ? No one else, therefore, can write a row until that particular row has been processed ?.

Does this then also go for QUEUE Tables as well ? Because if so, I'm now wondering whether there is actually any performance benefit to Queue Tables - in terms of concurrency - based upon any reduced locking consideration when writing to a Queue Table versus any other Table, I wonder?

You see, we've opened up a degree of write back to Teradata (no high rate, you'll be pleased to hear), and I've chosen the path of using QUEUE Tables to manage this updates, in all cases.

But, I was under the impression that Queue Tables worked at the ROW Locking Level, rather than Exclusive TABLE level, but maybe I'm mistaken. I was under the impression that concurrency would be better writing to a Queue Table than an ordinary Table.

When I look in the manual, however, I can't find anything to distinguish the difference between Inserting into an 'ordinary' Table versus a Queue Table, in terms of Locking. Indeed the Select And Consume process itself applies a Write/Exclusive Lock to the Queue Table !

Is that right ? Is all the Queue Table giving me is a Select and Consume piece of functionality ?

Regards

David Clough
Senior BI Database Designer
BI Competency Centre

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