Archives of the TeradataForum

Message Posted: Thu, 20 Mar 2003 @ 05:47:31 GMT

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

A few thooughts on this procedure - since this is a common function to perform. Performance, scalability and locking can be an issue if there is a high volume of executions of this procedure and especially if many concurrent sessions are used on a large system.

The LOCKING, SELECT and UPDATE operations will all be all AMP operations when this procedure is executed. A simple trick changes this from many all amp steps to two single amp steps which will allow this to avoid the potential issues.

Define an additional column in the table T_GENID (let's call it keycolumn) and make it the primary index. On the single row in that table, set the value of keycolumn to 1 and the value of GenIDInc to whatever the initial value should be.

Then change the SQL to:

UPDATE PRCOREREFRESH.T_GenID SET GenIDInc = GenIDInc + :incrnumb WHERE
keycolumn = 1;
SELECT (GenIDInc - :incrnumb) INTO :GID FROM PRCOREREFRESH.T_GenID WHERE
keycolumn = 1;

Note that the update is done first which will also place the row level write lock on the single row protecting from any other concurrent executions of the procedure. No separate locking is required or desirable. The result of these changes will be two single amp steps instead of at least 8 all amp steps and will eliminate all the opportunities for deadlock.

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