We have a long-running stored proc that duplicates a particular row in table called tb_location_map and also duplicates all related rows in several other tables.
The new records created contain foreign keys to our user data table, tb_entity. However there is no other reference to tb_entity in the cloning function.
We are seeing in SOME cases, but not all, that during the execution of this function, that the corresponding row in tb_entity is locked with a ROW SHARE lock (according to tb_locks, searching by the user's PID and tb_entity's relation ID), which is causing updates to the user's data to block while waiting for the map clone to finish.
What could be causing this ROW SHARE lock to be in place for some cloning operations and not others? Perhaps there is a way to see which specific statement is causing the lock to occur, but I'm not sure how to look this up. Any help would be appreciated.
Thanks!
--
Moshe Jacobson Nead Werx, Inc. | Manager of Systems Engineering