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.hun...@gmail.com>
wrote:
> On Thu, Jul 17, 2014 at 3:34 PM, AI Rumman <rumman...@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.hun...@gmail.com)
>
