On 10/28/2016 10:25 AM, Tom Lane wrote:
> Steve Clark <steve.clark@netwolves.com> writes:
>> On 10/28/2016 09:48 AM, Tom Lane wrote:
>>> Retrying might be a usable band-aid, but really this is an application
>>> logic error. The code that is trying to do "lock table t_unit in
>>> exclusive mode" must already hold some lower-level lock on t_unit, which
>>> is blocking whatever the "update t_unit_status_log" command wants to do
>>> with t_unit. Looks like a classic lock-strength-upgrade mistake to me.
>> Oops - I forgot there is another process that runs every minute and
>> takes about 1 second to run that does an exclusive lock on t_unit and
>> t_unit_status_log.
> The problem here doesn't seem to be that; it's that whatever transaction
> is doing the "lock table" has *already* got a non-exclusive lock on
> t_unit. That's just bad programming. Take the strongest lock you need
> earliest in the transaction.
>
> regards, tom lane
>
I want to thank all the people that took the time to provide some elucidation on my problem.
The original code that was doing the exclusive locks was written in 2003 on ver 7.x which according to
comments in the code did not provide declaring a cursor for update in ecpg, so the programmer at that
time opted to lock the two tables.
I just changed to code to remove the two exclusive locks and use "for update" on the cursor and haven't
seen a lock issue**in the**pg_log file since.
Regards,
--
Stephen Clark
