Thread: 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.
--
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
Nead Werx, Inc. | Manager of Systems Engineering
2323 Cumberland Parkway, Suite 201 | Atlanta, GA 30339
moshe@neadwerx.com | www.neadwerx.com
moshe@neadwerx.com | www.neadwerx.com
"Quality is not an act, it is a habit." -- Aristotle
On Tue, May 21, 2013 at 2:39 PM, Moshe Jacobson <moshe@neadwerx.com> wrote:
Update:
I looked at pg_locks for one hung update to tb_entity, and it said it had an un-granted lock of type transactionid, for another transaction ID.
--
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.
Apparently the hanging updates to tb_entity are not only related to fn_clone_location_map(), because a hung update to tb_entity did not correspond to a running instance of fn_clone_location_map().
I looked at pg_locks for one hung update to tb_entity, and it said it had an un-granted lock of type transactionid, for another transaction ID.
I wanted to see what was happening in that transaction ID that might be causing this lock to be held, but I was unsure how. I know I can look in pg_locks for the other transaction, but that will not tell me what statement is executing in that transaction. pg_stat_activity does not have a transaction ID column.
How can I see what statement is executing in a transaction?
Moshe Jacobson
Nead Werx, Inc. | Manager of Systems Engineering
Nead Werx, Inc. | Manager of Systems Engineering
2323 Cumberland Parkway, Suite 201 | Atlanta, GA 30339
moshe@neadwerx.com | www.neadwerx.com
moshe@neadwerx.com | www.neadwerx.com
"Quality is not an act, it is a habit." -- Aristotle
On Tue, May 21, 2013 at 12:24 PM, Moshe Jacobson <moshe@neadwerx.com> wrote:
> I wanted to see what was happening in that transaction ID that might be
> causing this lock to be held, but I was unsure how. I know I can look in
> pg_locks for the other transaction, but that will not tell me what statement
> is executing in that transaction. pg_stat_activity does not have a
> transaction ID column.
>
> How can I see what statement is executing in a transaction?
You can join pg_locks and pg_stat_activity by pid (or procpid = pid if
your version <9.2).
SELECT
...
current_query
...
FROM pg_locks AS l
LEFT JOIN pg_stat_activity AS a ON
-- procpid = pid -- <9.2
a.pid = l.pid -- >=9.2
...
--
Kind regards,
Sergey Konoplev
PostgreSQL Consultant and DBA
Profile: http://www.linkedin.com/in/grayhemp
Phone: USA +1 (415) 867-9984, Russia +7 (901) 903-0499, +7 (988) 888-1979
Skype: gray-hemp
Jabber: gray.ru@gmail.com
Solution:
The inserts of the foreign key to tb_entity were blocking the updates to those rows of tb_entity.
The inserts of the foreign key to tb_entity were blocking the updates to those rows of tb_entity.
I solved the problem by making the foreign key constraints deferrable and deferring checking on them till the end of the transaction.
On Tue, May 21, 2013 at 3:24 PM, Moshe Jacobson <moshe@neadwerx.com> wrote:
On Tue, May 21, 2013 at 2:39 PM, Moshe Jacobson <moshe@neadwerx.com> wrote:Update: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.Apparently the hanging updates to tb_entity are not only related to fn_clone_location_map(), because a hung update to tb_entity did not correspond to a running instance of fn_clone_location_map().
I looked at pg_locks for one hung update to tb_entity, and it said it had an un-granted lock of type transactionid, for another transaction ID.I wanted to see what was happening in that transaction ID that might be causing this lock to be held, but I was unsure how. I know I can look in pg_locks for the other transaction, but that will not tell me what statement is executing in that transaction. pg_stat_activity does not have a transaction ID column.How can I see what statement is executing in a transaction?--Moshe Jacobson
Nead Werx, Inc. | Manager of Systems Engineering2323 Cumberland Parkway, Suite 201 | Atlanta, GA 30339
moshe@neadwerx.com | www.neadwerx.com"Quality is not an act, it is a habit." -- Aristotle
Moshe Jacobson
Nead Werx, Inc. | Manager of Systems Engineering
Nead Werx, Inc. | Manager of Systems Engineering
2323 Cumberland Parkway, Suite 201 | Atlanta, GA 30339
moshe@neadwerx.com | www.neadwerx.com
moshe@neadwerx.com | www.neadwerx.com
"Quality is not an act, it is a habit." -- Aristotle