Archives of the TeradataForum
Message Posted: Wed, 23 Apr 2014 @ 20:48:29 GMT
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 ?
|Copyright 2016 - All Rights Reserved|
|Last Modified: 28 Jun 2020|