Re: What will occur when a row level lock is requested in a macro and the Optimizer determines a table level lock is required?
You can always request a row hash lock by putting "LOCKING ROW FOR ....". It will only be used if the SQL in the macro specifies the PI columns - ie you are updating some set of rows which have a common PI value and the PI value is specified in the macro. If the PI value is not specified completely, or there is any join then a table lock will be used whether or not you asked for a row lock.
TableA has NUPI of (Key1, Key2) plus Val3. TableB has UPI of (Key1, Key2 plus Val4.
Insert Into TableA Values (:Key1, :Key2, :Val3): -- uses row hash write lock because Key1, Key2, Val3 are constants specified as input to the macro.
Insert Into TableA Select Key1, Key2, Val4 From TableB ; -- uses a table lock because the PI is not specified - even though it is available in TableB!
The fact that it is in a macro is immaterial if it is a single statement macro. If there is more than one statement, locks can "accumulate" because a macro is also a transaction. So, if you do something like the Insert/ Select followed by Insert Values, the table is locked for the first statement and this lock is not released for the second statement.