Message-ID: <50EF0216.2050309@vorio.com.br>
Date: Thu, 10 Jan 2013 16:01:58 -0200
From: PostgreSQL <postgresql@vorio.com.br>
User-Agent: Mozilla/5.0 (Windows NT 6.1; WOW64;
	rv:17.0) Gecko/20130107 Thunderbird/17.0.2
MIME-Version: 1.0
To: pgsql-performance@postgresql.org
Subject: Updates on one row causing ExclusiveLock on PostgreSQL 8.3.5
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

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!!


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance