lock problem

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



I'm seeing connection hang issue these days. many concurrent connections are hanging on db. They basically do the same thing: update different rows in same table. The sql itself should run very fast as it's updating just one row based on an unique key. I though it might be lock problem.  The I list the locks ordered by query start time(see list below).  I'm confused why the oldest connection are still waiting for the lock? anything else can hold that lock?
                            |    1580056836 | ShareLock        | f       | update article set tm_update=$ | 2011-12-21 13:28:56.643105+08 | 00:24:51.599424 |   21020

select
     pg_class.relname,pg_locks.transactionid, pg_locks.mode, pg_locks.granted,                         
     substr(pg_stat_activity.current_query,1,30), pg_stat_activity.query_start,                          
     age(now(),pg_stat_activity.query_start) as "age", pg_stat_activity.procpid
   from pg_stat_activity,pg_locks left
     outer join pg_class on (pg_locks.relation = pg_class.oid)  
   where pg_locks.pid=pg_stat_activity.procpid order by query_start limit 50;
          relname           | transactionid |       mode       | granted |             substr             |          query_start          |       age       | procpid
----------------------------+---------------+------------------+---------+--------------------------------+-------------------------------+-----------------+---------
 article_title_hash_idx     |               | RowExclusiveLock | t       | update article set tm_update=$ | 2011-12-21 13:28:56.643105+08 | 00:24:51.599424 |   21020
 article                    |               | RowExclusiveLock | t       | update article set tm_update=$ | 2011-12-21 13:28:56.643105+08 | 00:24:51.599424 |   21020
                            |    1580056836 | ShareLock        | f       | update article set tm_update=$ | 2011-12-21 13:28:56.643105+08 | 00:24:51.599424 |   21020
                            |    1579897513 | ExclusiveLock    | t       | update article set tm_update=$ | 2011-12-21 13:28:56.643105+08 | 00:24:51.599424 |   21020
 article_fid_author_idx     |               | RowExclusiveLock | t       | update article set tm_update=$ | 2011-12-21 13:28:56.643105+08 | 00:24:51.599424 |   21020
 article_stage_idx          |               | RowExclusiveLock | t       | update article set tm_update=$ | 2011-12-21 13:28:56.643105+08 | 00:24:51.599424 |   21020
 article_fid_idx            |               | RowExclusiveLock | t       | update article set tm_update=$ | 2011-12-21 13:28:56.643105+08 | 00:24:51.599424 |   21020
 article_cid_time_style_idx |               | RowExclusiveLock | t       | update article set tm_update=$ | 2011-12-21 13:28:56.643105+08 | 00:24:51.599424 |   21020
                            |               | ExclusiveLock    | t       | update article set tm_update=$ | 2011-12-21 13:28:56.643105+08 | 00:24:51.599424 |   21020
 article_tm_spider_idx      |               | RowExclusiveLock | t       | update article set tm_update=$ | 2011-12-21 13:28:56.643105+08 | 00:24:51.599424 |   21020
 article_tm_update_idx      |               | RowExclusiveLock | t       | update article set tm_update=$ | 2011-12-21 13:28:56.643105+08 | 00:24:51.599424 |   21020
 article_guid_idx           |               | RowExclusiveLock | t       | update article set tm_update=$ | 2011-12-21 13:28:56.643105+08 | 00:24:51.599424 |   21020
 article_url_hash           |               | RowExclusiveLock | t       | update article set tm_update=$ | 2011-12-21 13:28:56.643105+08 | 00:24:51.599424 |   21020
 article_rfid_idx           |               | RowExclusiveLock | t       | update article set tm_update=$ | 2011-12-21 13:28:56.643105+08 | 00:24:51.599424 |   21020
 article_url_idx            |               | RowExclusiveLock | t       | update article set tm_update=$ | 2011-12-21 13:28:56.643105+08 | 00:24:51.599424 |   21020
 article_pkey               |               | RowExclusiveLock | t       | update article set tm_update=$ | 2011-12-21 13:28:56.643105+08 | 00:24:51.599424 |   21020
 article                    |               | RowExclusiveLock | t       | update article set tm_update=$ | 2011-12-21 13:30:01.947787+08 | 00:23:46.294742 |     706
 article_cid_time_style_idx |               | RowExclusiveLock | t       | update article set tm_update=$ | 2011-12-21 13:30:01.947787+08 | 00:23:46.294742 |     706
 article_fid_idx            |               | RowExclusiveLock | t       | update article set tm_update=$ | 2011-12-21 13:30:01.947787+08 | 00:23:46.294742 |     706
 article_rfid_idx           |               | RowExclusiveLock | t       | update article set tm_update=$ | 2011-12-21 13:30:01.947787+08 | 00:23:46.294742 |     706
 article_pkey               |               | RowExclusiveLock | t       | update article set tm_update=$ | 2011-12-21 13:30:01.947787+08 | 00:23:46.294742 |     706
                            |    1579921995 | ExclusiveLock    | t       | update article set tm_update=$ | 2011-12-21 13:30:01.947787+08 | 00:23:46.294742 |     706
 article_url_idx            |               | RowExclusiveLock | t       | update article set tm_update=$ | 2011-12-21 13:30:01.947787+08 | 00:23:46.294742 |     706
 article_title_hash_idx     |               | RowExclusiveLock | t       | update article set tm_update=$ | 2011-12-21 13:30:01.947787+08 | 00:23:46.294742 |     706
 article_guid_idx           |               | RowExclusiveLock | t       | update article set tm_update=$ | 2011-12-21 13:30:01.947787+08 | 00:23:46.294742 |     706
 article_tm_update_idx      |               | RowExclusiveLock | t       | update article set tm_update=$ | 2011-12-21 13:30:01.947787+08 | 00:23:46.294742 |     706
                            |    1580056836 | ShareLock        | f       | update article set tm_update=$ | 2011-12-21 13:30:01.947787+08 | 00:23:46.294742 |     706
 article_fid_author_idx     |               | RowExclusiveLock | t       | update article set tm_update=$ | 2011-12-21 13:30:01.947787+08 | 00:23:46.294742 |     706
 article_tm_spider_idx      |               | RowExclusiveLock | t       | update article set tm_update=$ | 2011-12-21 13:30:01.947787+08 | 00:23:46.294742 |     706
 article_stage_idx          |               | RowExclusiveLock | t       | update article set tm_update=$ | 2011-12-21 13:30:01.947787+08 | 00:23:46.294742 |     706
 article_url_hash           |               | RowExclusiveLock | t       | update article set tm_update=$ | 2011-12-21 13:30:01.947787+08 | 00:23:46.294742 |     706
                            |               | ExclusiveLock    | t       | update article set tm_update=$ | 2011-12-21 13:30:01.947787+08 | 00:23:46.294742 |     706
 article_title_hash_idx     |               | RowExclusiveLock | t       | update article set tm_update=$ | 2011-12-21 13:30:11.735228+08 | 00:23:36.507301 |   22892
                            |    1580056836 | ShareLock        | f       | update article set tm_update=$ | 2011-12-21 13:30:11.735228+08 | 00:23:36.507301 |   22892
 article_stage_idx          |               | RowExclusiveLock | t       | update article set tm_update=$ | 2011-12-21 13:30:11.735228+08 | 00:23:36.507301 |   22892
 article_fid_idx            |               | RowExclusiveLock | t       | update article set tm_update=$ | 2011-12-21 13:30:11.735228+08 | 00:23:36.507301 |   22892
 article_pkey               |               | RowExclusiveLock | t       | update article set tm_update=$ | 2011-12-21 13:30:11.735228+08 | 00:23:36.507301 |   22892
 article_url_hash           |               | RowExclusiveLock | t       | update article set tm_update=$ | 2011-12-21 13:30:11.735228+08 | 00:23:36.507301 |   22892
 article_cid_time_style_idx |               | RowExclusiveLock | t       | update article set tm_update=$ | 2011-12-21 13:30:11.735228+08 | 00:23:36.507301 |   22892
 article                    |               | RowExclusiveLock | t       | update article set tm_update=$ | 2011-12-21 13:30:11.735228+08 | 00:23:36.507301 |   22892
 article_fid_author_idx     |               | RowExclusiveLock | t       | update article set tm_update=$ | 2011-12-21 13:30:11.735228+08 | 00:23:36.507301 |   22892
 article_tm_update_idx      |               | RowExclusiveLock | t       | update article set tm_update=$ | 2011-12-21 13:30:11.735228+08 | 00:23:36.507301 |   22892
 article_rfid_idx           |               | RowExclusiveLock | t       | update article set tm_update=$ | 2011-12-21 13:30:11.735228+08 | 00:23:36.507301 |   22892
 article_url_idx            |               | RowExclusiveLock | t       | update article set tm_update=$ | 2011-12-21 13:30:11.735228+08 | 00:23:36.507301 |   22892
                            |               | ExclusiveLock    | t       | update article set tm_update=$ | 2011-12-21 13:30:11.735228+08 | 00:23:36.507301 |   22892
 article_guid_idx           |               | RowExclusiveLock | t       | update article set tm_update=$ | 2011-12-21 13:30:11.735228+08 | 00:23:36.507301 |   22892
 article_tm_spider_idx      |               | RowExclusiveLock | t       | update article set tm_update=$ | 2011-12-21 13:30:11.735228+08 | 00:23:36.507301 |   22892
                            |    1579925267 | ExclusiveLock    | t       | update article set tm_update=$ | 2011-12-21 13:30:11.735228+08 | 00:23:36.507301 |   22892
 article_title_hash_idx     |               | RowExclusiveLock | t       | update article set tm_update=$ | 2011-12-21 13:30:26.843451+08 | 00:23:21.399078 |   32700
 article_fid_author_idx     |               | RowExclusiveLock | t       | update article set tm_update=$ | 2011-12-21 13:30:26.843451+08 | 00:23:21.399078 |   32700
(50 rows)

[Index of Archives]     [KVM ARM]     [KVM ia64]     [KVM ppc]     [Virtualization Tools]     [Spice Development]     [Libvirt]     [Libvirt Users]     [Linux USB Devel]     [Linux Audio Users]     [Yosemite Questions]     [Linux Kernel]     [Linux SCSI]     [XFree86]

  Powered by Linux