Strange locking problem

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 <http://pastebin.com/uU0S9x71>.

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
2323 Cumberland Parkway, Suite 201 | Atlanta, GA 30339
moshe(at)neadwerx(dot)com | www.neadwerx.com

"Quality is not an act, it is a habit." -- Aristotle