Thread: Whats is lock type transactionid?
Hi,
I have been facing lock contention in my Postgresql 9.1 DB.
And when I am querying in the pg_locks table I found a lock type with transactionid.
Could someone please tell me what it means?
Thanks.
On Thu, Jul 17, 2014 at 3:34 PM, AI Rumman <rummandba@gmail.com> wrote:
Hi,I have been facing lock contention in my Postgresql 9.1 DB.And when I am querying in the pg_locks table I found a lock type with transactionid.Could someone please tell me what it means?Thanks.
from http://www.postgresql.org/docs/9.3/static/view-pg-locks.html :
Every transaction holds an exclusive lock on its virtual transaction ID for its entire duration. If a permanent ID is assigned to the transaction (which normally happens only if the transaction changes the state of the database), it also holds an exclusive lock on its permanent transaction ID until it ends. When one transaction finds it necessary to wait specifically for another transaction, it does so by attempting to acquire share lock on the other transaction ID (either virtual or permanent ID depending on the situation). That will succeed only when the other transaction terminates and releases its locks.
I believe that describes what you're seeing
Douglas J Hunley (doug.hunley@gmail.com)
I am experiencing lock contention on one single UPDATE statement at a certain time in whole day. This is a small table to UPDATE.
My suspect is we are facing it for one specific ID.
Could you please let me know how can I identify the tuple.
I got a log like follows:
blocker_target | blocker_pid | blocker_mode | depth | target | pid | mode | seq
-----------------------------------+-------------+---------------+-------+-----------------------------------+------+---------------+-----------
(tuple,475999,662775,1988,6,,,,,) | 3557 | ExclusiveLock | 1 | (tuple,475999,662775,1988,6,,,,,) | 3543 | ExclusiveLock | 3557,3543
(tuple,475999,662775,1988,6,,,,,) | 3557 | ExclusiveLock | 1 | (tuple,475999,662775,1988,6,,,,,) | 7387 | ExclusiveLock | 3557,7387
Any idea on it.
Thanks.
On Thu, Jul 17, 2014 at 12:40 PM, Douglas J Hunley <doug.hunley@gmail.com> wrote:
On Thu, Jul 17, 2014 at 3:34 PM, AI Rumman <rummandba@gmail.com> wrote:Hi,I have been facing lock contention in my Postgresql 9.1 DB.And when I am querying in the pg_locks table I found a lock type with transactionid.Could someone please tell me what it means?Thanks.
from http://www.postgresql.org/docs/9.3/static/view-pg-locks.html :
Every transaction holds an exclusive lock on its virtual transaction ID for its entire duration. If a permanent ID is assigned to the transaction (which normally happens only if the transaction changes the state of the database), it also holds an exclusive lock on its permanent transaction ID until it ends. When one transaction finds it necessary to wait specifically for another transaction, it does so by attempting to acquire share lock on the other transaction ID (either virtual or permanent ID depending on the situation). That will succeed only when the other transaction terminates and releases its locks.
I believe that describes what you're seeing--Douglas J Hunley (doug.hunley@gmail.com)
On Thu, Jul 17, 2014 at 12:54 PM, AI Rumman <rummandba@gmail.com> wrote:
I am experiencing lock contention on one single UPDATE statement at a certain time in whole day. This is a small table to UPDATE.My suspect is we are facing it for one specific ID.Could you please let me know how can I identify the tuple.
Have you tried the lock monitoring queries on http://wiki.postgresql.org/wiki/Lock_Monitoring yet by chance?
Douglas J Hunley (doug.hunley@gmail.com)
Yes. But as we are using bind variables, we are not able to get the ID of the tuple.
On Thu, Jul 17, 2014 at 2:08 PM, Douglas J Hunley <doug.hunley@gmail.com> wrote:
Have you tried the lock monitoring queries on http://wiki.postgresql.org/wiki/Lock_Monitoring yet by chance?On Thu, Jul 17, 2014 at 12:54 PM, AI Rumman <rummandba@gmail.com> wrote:I am experiencing lock contention on one single UPDATE statement at a certain time in whole day. This is a small table to UPDATE.My suspect is we are facing it for one specific ID.Could you please let me know how can I identify the tuple.