Archives of the TeradataForum

Message Posted: Mon, 06 Apr 2015 @ 16:43:04 GMT

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

You can use Locking-modifiers to accomplish this task - discussed in the DML statement portion of the Teradata SQL reference manuals. However, Teradata locking modifiers (i.e. LOCKING ROW FOR WRITE) does not actually lock just a row for write, but the entire hash block. For tables defined with Non-unique primary indexes (NUPI) this will be multiple rows within the same hash block if they exists.

For this to work properly at truly a row level you will need two things:

1) UPI (unique primary index) to ensure this is a single-amp row hash lock (and no secondary indexes attached - or it will escalate to all-amp lock)

2) Macro for the update process to ensure it is a single amp row hash lock (or control unit of work with appropriate placed BT/ET statements)

A simple insert will insert a new row based on the row-key generated and only need a row-key write lock to insert. Update/deletes sometimes need a bit of prodding to get the locking mechanisms to accommodate and not interfere with other processes. A macro is a great vehicle to isolate the unit of work for the locking modifier to be applied and exist accordingly.

Everything in the macro is a unit of work (or you can issue your own BT/ET commands to control unit of work). The first query uses the locking modifier to lock the row hash for write based on the C1 value even though it's a select DML statement. The update occurs as the select statement's locking modifier along with the UPI of the table ensures this is a row-key write lock since this table is defined with a UPI vs. a multi-row hash lock if it happened to be a NUPI defined table. Either case is a single-amp write lock vs. an all-amp write lock.

     CREATE TABLE 
      (C1 INTEGER NOT NULL,
         C2 CHAR(50)
     ) UNIQUE PRIMARY INDEX PK_YOUR_TABLE(C1);


     Replace macro Update_IT (Existing_Key INTEGER, New_VALUE CHAR(50))

     As

     LOCKING ROW FOR WRITE
     SELECT C1, C2
     >From 
     WHERE C1 = :Existing_Key;

     UPDATE 
     SET C2 = :NEW_VALUE
     WHERE C1 = :Existing_Key;

     ;


     Insert into update_IT values (1,'This is a Test');


     Exec update_IT (1,'This is a revised Value');

Explain step 1.1 shows the WRITE LOCK being applied to the row, but not released until STEP 2.0 with the END TRANSACTION statement - implied end of macro assuming there is no outside BT/ET logic controlling the unit of work outside of a single macro.

Kyle Prescott
DBA Manager
Unum-GHDS
Chattanooga, TN

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