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
