Updates on one row causing ExclusiveLock on PostgreSQL 8.3.5

From: PostgreSQL <postgresql(at)vorio(dot)com(dot)br>
To: pgsql-performance(at)postgresql(dot)org
Subject: Updates on one row causing ExclusiveLock on PostgreSQL 8.3.5
Date: 2013-01-10 18:01:58
Message-ID: 50EF0216.2050309@vorio.com.br
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

My best regards for all...

Please. I need for an advice.
I'm having a trouble, that puting others queries in wait state, becouse
of ExclusiveLock granted by an Update that only update one row at each
time. This update occurs into a function and this function are
executed several times and concurrently.
Below, query plan (explain):

Nested Loop (cost=16.91..36.32 rows=1 width=75)
-> HashAggregate (cost=16.91..16.92 rows=1 width=4)
-> Index Scan using unq_customer_idx_msisdn on customer
(cost=0.00..16.90 rows=1 width=4)
Index Cond: ((msisdn)::text = '558796013980'::text)
-> Index Scan using pk_customer_rel_channel on customer_rel_channel
(cost=0.00..19.39 rows=1 width=75)
Index Cond: ((customer_rel_channel.id_customer =
customer.id_customer) AND (customer_rel_channel.id_channel = 282))

But, the pg_locs shows:

PID Relation User Transaction Access Mode Granted Query
Start Query
22569 customer_rel_channel postgres ExclusiveLock False
2013-01-10 15:54:09.308056-02 UPDATE news.customer_rel_channel SET
status = $1, source = $2
WHERE news.customer_rel_channel.id_channel = $3 AND
news.customer_rel_channel.id_customer IN
(SELECT id_customer FROM public.customer WHERE
public.customer.msisdn = $4)

I can't understand what happens here... This query can't be lock
granted becouse another instance of this query already granted it.
I can't understand why an update that modify one row only need an
ExclusiveLock.

Thanks a lot!!

Browse pgsql-performance by date

  From Date Subject
Next Message PostgreSQL 2013-01-10 18:04:02 Updates on one row causing ExclusiveLock on PostgreSQL 8.3.5
Previous Message Shaun Thomas 2013-01-10 15:53:25 Re: Two Necessary Kernel Tweaks for Linux Systems