|
On 10/28/2016 10:25 AM, Tom Lane wrote:
I want to thank all the people that took the time to provide some elucidation on my problem.Steve Clark <steve.clark@xxxxxxxxxxxxx> 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 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 |
