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