Home Page for the TeradataForum

Archives of the TeradataForum

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

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

Subj:   Re: Inserting/Updating same target table simultaneously by multiple sessions without locking
From:   Prescott, Kyle

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.

         C2 CHAR(50)

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


     SELECT C1, C2
     WHERE C1 = :Existing_Key;

     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.

  1)First, we execute the following steps in parallel.  
  1) We do a single-AMP RETRIEVE step from SYSDBA.Table_A by way of the unique primary index "SYSDBA.Table_A.C1 = 1" with no residual conditions locking row for write. The estimated time for this step is 0.00 seconds.
  2) We do a single-AMP UPDATE from SYSDBA.Table_A by way of the unique primary index "SYSDBA.Table_A.C1 = 1" with no residual conditions.
  2)Finally, we send out an END TRANSACTION step to all AMPs involved in processing the request.  
  ->The row is sent directly back to the user as the result of statement 1. No rows are returned to the user as the result of statement 2.  

Kyle Prescott
DBA Manager
Chattanooga, TN

  <Prev Next>   <<First <Prev Next> Last>>  
  Top Home Privacy Feedback  
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 24 Jul 2020