What will occur when a row level lock is requested in a macro and the Optimizer determines a table level lock is required?

Database

What will occur when a row level lock is requested in a macro and the Optimizer determines a table level lock is required?

can you also explain how a row level lock can be requested in macro? and some situations where Optimizer determines for a table level lock?

Thanks forum.

3 REPLIES
Enthusiast

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.

Some examples:

TableA has NUPI of (Key1, Key2) plus Val3.
TableB has UPI of (Key1, Key2 plus Val4.

So:

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.

HTH.

Enthusiast

Re: What will occur when a row level lock is requested in a macro and the Optimizer determines a table level lock is required?

Does that mean if table level lock is required, optimizer will automatically upgrade row level lock to Table level lock?

Enthusiast

Re: What will occur when a row level lock is requested in a macro and the Optimizer determines a table level lock is required?

Will the lock obtained by a Macro ignore a "Lock for access" inside of a view?