Postgres Pro
Downloads
Home   >   mailing lists

Thread: virtualxid,relation lock

virtualxid,relation lock

From
shanmugavel muthuvel
Date:
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

From
Kevin Grittner
Date:
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