Обсуждение: virtualxid,relation lock

Поиск
Список
Период
Сортировка

virtualxid,relation lock

От
shanmugavel muthuvel
Дата:
Hii,

I have an issue with of "idle transaction" and one select statement in backend.
what i noticed when i look the pg_lock, all are idle trans and one particlular select statement with virtualxid,relation lock.

the lock are held with diffrend objects.it utilise the whole cpu.How can fix the issue.

lock informations


 pid  |   vxid   | lock_type |    lock_mode    | granted | xid_lock |              relname               | page | tuple | classid | objid | objsubid
-------+----------+-----------+-----------------+---------+----------+------------------------------------+------+-------+---------+-------+----------
 38423 | 4/334285 | relation  | AccessShareLock | t       |          | admin                              |      |       |         |       |        
 38423 | 4/334285 | relation  | AccessShareLock | t       |          | admin_pkey                         |      |       |         |       |        
 38423 | 4/334285 | relation  | AccessShareLock | t       |          | cert_data                          |      |       |         |       |        
 38423 | 4/334285 | relation  | AccessShareLock | t       |          | cert_data_pkey                     |      |       |         |       |        
 38423 | 4/334285 | relation  | AccessShareLock | t       |          | client_admin                       |      |       |         |       |        
 38423 | 4/334285 | relation  | AccessShareLock | t       |          | client_admin_creater_client_id_inx |      |       |         |       |        
 38423 | 4/334285 | relation  | AccessShareLock | t       |          | client_admin_creds                 |      |       |         |       |        
 38423 | 4/334285 | relation  | AccessShareLock | t       |          | client_admin_creds_pkey            |      |       |         |       |        
 38423 | 4/334285 | relation  | AccessShareLock | t       |          | client_admin_customer_id_idx       |      |       |         |       |        
 38423 | 4/334285 | relation  | AccessShareLock | t       |          | client_admin_pkey                  |      |       |         |       |        
 38423 | 4/334285 | relation  | AccessShareLock | t       |          | cust_indx_name                     |      |       |         |       |        
 38423 | 4/334285 | relation  | AccessShareLock | t       |          | cust_indx_uri                      |      |       |         |       |        
 38423 | 4/334285 | relation  | AccessShareLock | t       |          | customer                           |      |       |         |       |        
 38423 | 4/334285 | relation  | AccessShareLock | t       |          | customer_pkey                      |      |       |         |       |        
 38423 | 4/334285 | relation  | AccessShareLock | t       |          | dom_org                            |      |       |         |       |        
 38423 | 4/334285 | relation  | AccessShareLock | t       |          | dom_org_approver                   |      |       |         |       |        
 38423 | 4/334285 | relation  | AccessShareLock | t       |          | dom_org_approver_idx               |      |       |         |       |        
 38423 | 4/334285 | relation  | AccessShareLock | t       |          | dom_org_approver_pkey              |      |       |         |       |        
 38423 | 4/334285 | relation  | AccessShareLock | t       |          | dom_org_pkey                       |      |       |         |       |        
 38423 | 4/334285 | relation  | AccessShareLock | t       |          | domain                             |      |       |         |       |        
 38423 | 4/334285 | relation  | AccessShareLock | t       |          | domain_name_customer_idx           |      |       |         |       |        
 38423 | 4/334285 | relation  | AccessShareLock | t       |          | domain_pkey                        |      |       |         |       |        
 38423 | 4/334285 | relation  | AccessShareLock | t       |          | domain_settings                    |      |       |         |       |        
 38423 | 4/334285 | relation  | AccessShareLock | t       |          | idp                                |      |       |         |       |        
 38423 | 4/334285 | relation  | AccessShareLock | t       |          | idp_pkey                           |      |       |         |       |        
 38423 | 4/334285 | relation  | AccessShareLock | t       |          | notification                       |      |       |         |       |        
 38423 | 4/334285 | relation  | AccessShareLock | t       |          | notification_customer_id_idx       |      |       |         |       |        
 38423 | 4/334285 | relation  | AccessShareLock | t       |          | notification_orgs                  |      |       |         |       |        
 38423 | 4/334285 | relation  | AccessShareLock | t       |          | notification_pkey                  |      |       |         |       |        
 38423 | 4/334285 | relation  | AccessShareLock | t       |          | notification_roles                 |      |       |         |       |        
 38423 | 4/334285 | relation  | AccessShareLock | t       |          | notify_task_seq                    |      |       |         |       |        
 38423 | 4/334285 | relation  | AccessShareLock | t       |          | organization                       |      |       |         |       |        
 38423 | 4/334285 | relation  | AccessShareLock | t       |          | organization_customer_id_idx       |      |       |         |       |        
 38423 | 4/334285 | relation  | AccessShareLock | t       |          | organization_pkey                  |      |       |         |       |        
 38423 | 4/334285 | relation  | AccessShareLock | t       |          | person                             |      |       |         |       |        
 38423 | 4/334285 | relation  | AccessShareLock | t       |          | person_customer_id_idx             |      |       |         |       |        
 38423 | 4/334285 | relation  | AccessShareLock | t       |          | person_org_idx                     |      |       |         |       |        
 38423 | 4/334285 | relation  | AccessShareLock | t       |          | person_pkey                        |      |       |         |       |        
 38423 | 4/334285 | relation  | AccessShareLock | t       |          | person_pn_lowcase_idx              |      |       |         |       |        
 38423 | 4/334285 | relation  | AccessShareLock | t       |          | setting_seq                        |      |       |         |       |        
 38423 | 4/334285 | relation  | AccessShareLock | t       |          | settings                           |      |       |         |       |        
 38423 | 4/334285 | relation  | AccessShareLock | t       |          | settings_pkey                      |      |       |         |       |        
 38423 | 4/334285 | relation  | AccessShareLock | t       |          | smime                              |      |       |         |       |        



          xact_start           | datid |       datname       | procpid | usesysid |                substring                | waiting
-------------------------------+-------+---------------------+---------+----------+-----------------------------------------+---------
 2013-08-28 10:04:28.126694+01 | 33086 | test_test |   38423 |    33087 | select clientadmi0_.id as id5_, clienta | f
 2013-08-28 11:04:13.652912+01 | 33086 | test_test |   39886 |    33087 | select clientadmi0_.id as id5_, clienta | f


Regards

shanmugavel M

 

Re: virtualxid,relation lock

От
Kevin Grittner
Дата:
shanmugavel muthuvel <shanmugavel.muthu@gmail.com> wrote:

> I have an issue with of "idle transaction" and one select
> statement in backend.

> How can fix the issue.

There wasn't enough detail in the problem statement to make a
detailed suggestion.  The general suggestion would be to make sure
that transactions are never left idle for any significant amount of
time.  (For example, applications should never wait for user input
with a transaction pending.)

For more detailed suggestions, please review this for ideas on what
people might need to see to provide more specific suggestions:

http://wiki.postgresql.org/wiki/Guide_to_reporting_problems

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company