Archives of the TeradataForum
Message Posted: Mon, 06 Apr 2015 @ 16:43:04 GMT
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.
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.
|Copyright 2016 - All Rights Reserved|
|Last Modified: 24 Jul 2020|