pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
how to find transaction associated with a lock
6+ messages / 2 participants
[nested] [flat]

* how to find transaction associated with a lock
@ 2004-07-22 19:16 Si Chen <schen@graciousstyle.com>
  2004-07-22 20:45 ` Re: how to find transaction associated with a lock Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 6+ messages in thread

From: Si Chen @ 2004-07-22 19:16 UTC (permalink / raw)
  To: pgsql-general@postgresql.org; pgsql-novice@postgresql.org; pgsql-admin

First of all, my apologies for posting it to 3 lists: I'm not sure if 
this is an admin question, or I only have this question because I'm a 
novice, or some combination thereof.

I have a table which appears to be locked on an INSERT.  Looking in 
pg_locks, I get the result (shown below).  I can use the relation.oid 
and the database.oid to get to the pg_class and pg_database and find out 
which tables are locked.  But the transactions that have exclusive locks 
marked against them, they have transaction identifiers only.  How do I 
use these transactions identifiers to find which tables are currently 
under exclusive lock?  It seems that there is a transaction_id 
associated with tables, but that involves going table by table through 
(500+) tables and does not seem too practical.

Does anyone know of a way to go from transactions identifiers to the 
actual transaction--which tables, which statements, etc. etc.?

Thanks in advance,

Si Chen


>  select * from pg_locks

relation database transaction pid mode granted
NULL NULL 3422568 21440 ExclusiveLock TRUE
125703 123502 NULL 2188 AccessShareLock TRUE
124863 123502 NULL 2188 AccessShareLock TRUE
125435 123502 NULL 2188 AccessShareLock TRUE
125435 123502 NULL 2188 RowShareLock TRUE
124205 123502 NULL 27149 AccessShareLock TRUE
250389 123502 NULL 27149 AccessShareLock TRUE
250389 123502 NULL 27149 RowShareLock TRUE
125684 123502 NULL 21440 AccessShareLock TRUE
125684 123502 NULL 21440 RowShareLock TRUE
125089 123502 NULL 2188 AccessShareLock TRUE
125273 123502 NULL 27149 AccessShareLock TRUE
250381 123502 NULL 27149 AccessShareLock TRUE
250381 123502 NULL 27149 RowExclusiveLock TRUE
124518 123502 NULL 5510 AccessShareLock TRUE
124518 123502 NULL 5510 RowExclusiveLock TRUE
250389 123502 NULL 1069 AccessShareLock TRUE
124066 123502 NULL 27149 AccessShareLock TRUE
124066 123502 NULL 27149 RowExclusiveLock TRUE
124300 123502 NULL 5510 AccessShareLock TRUE
125081 123502 NULL 1069 AccessShareLock TRUE
NULL NULL 3377982 21292 ShareLock FALSE
NULL NULL 3419769 12638 ShareLock FALSE
NULL NULL 3440992 23620 ExclusiveLock TRUE
125081 123502 NULL 27149 AccessShareLock TRUE
250409 123502 NULL 27149 AccessShareLock TRUE
125341 123502 NULL 21292 AccessShareLock TRUE
124879 123502 NULL 2188 AccessShareLock TRUE
124818 123502 NULL 21440 AccessShareLock TRUE
NULL NULL 3387552 21292 ExclusiveLock TRUE
124518 123502 NULL 27149 AccessShareLock TRUE
124518 123502 NULL 27149 RowExclusiveLock TRUE
125643 123502 NULL 8649 AccessShareLock TRUE
125443 123502 NULL 27149 AccessShareLock TRUE
250395 123502 NULL 1069 AccessShareLock TRUE
124820 123502 NULL 27149 AccessShareLock TRUE
125317 123502 NULL 21292 AccessShareLock TRUE
125038 123502 NULL 27149 AccessShareLock TRUE
125643 123502 NULL 1069 AccessShareLock TRUE
125317 123502 NULL 5510 AccessShareLock TRUE
NULL NULL 3366868 8649 ShareLock FALSE
124209 123502 NULL 27149 AccessShareLock TRUE
NULL NULL 3366868 5510 ShareLock FALSE
250381 123502 NULL 2188 AccessShareLock TRUE
250381 123502 NULL 2188 RowExclusiveLock TRUE
125353 123502 NULL 27149 AccessShareLock TRUE
125435 123502 NULL 27149 AccessShareLock TRUE
125435 123502 NULL 27149 RowShareLock TRUE
123916 123502 NULL 2188 AccessShareLock TRUE
250395 123502 NULL 8649 AccessShareLock TRUE
125337 123502 NULL 27149 AccessShareLock TRUE
250413 123502 NULL 2188 AccessShareLock TRUE
250413 123502 NULL 2188 RowExclusiveLock TRUE
250393 123502 NULL 27149 AccessShareLock TRUE
250393 123502 NULL 27149 RowExclusiveLock TRUE
124462 123502 NULL 27149 AccessShareLock TRUE
124462 123502 NULL 27149 RowShareLock TRUE
125038 123502 NULL 8649 AccessShareLock TRUE
123781 123502 NULL 2188 AccessShareLock TRUE
NULL NULL 3366868 2188 ShareLock FALSE
NULL NULL 3389227 8649 ExclusiveLock TRUE
125341 123502 NULL 5510 AccessShareLock TRUE
NULL NULL 3419769 21440 ShareLock FALSE
NULL NULL 3366868 21307 ShareLock FALSE
124277 123502 NULL 2188 AccessShareLock TRUE
124813 123502 NULL 2188 AccessShareLock TRUE
124813 123502 NULL 2188 RowExclusiveLock TRUE
124935 123502 NULL 2188 AccessShareLock TRUE
250413 123502 NULL 27149 AccessShareLock TRUE
250413 123502 NULL 27149 RowExclusiveLock TRUE
123701 123502 NULL 21440 AccessShareLock TRUE
123701 123502 NULL 21440 RowExclusiveLock TRUE
NULL NULL 3366868 27149 ExclusiveLock TRUE
124883 123502 NULL 27149 AccessShareLock TRUE
124300 123502 NULL 27149 AccessShareLock TRUE
124300 123502 NULL 27149 RowExclusiveLock TRUE
125273 123502 NULL 8649 AccessShareLock TRUE
NULL NULL 3419769 2188 ExclusiveLock TRUE
131092 123502 NULL 5510 AccessShareLock TRUE
124462 123502 NULL 2188 AccessShareLock TRUE
124462 123502 NULL 2188 RowShareLock TRUE
250381 123502 NULL 21307 AccessShareLock TRUE
NULL NULL 3366868 1069 ShareLock FALSE
125643 123502 NULL 27149 AccessShareLock TRUE
16757 123502 NULL 23620 AccessShareLock TRUE
250389 123502 NULL 2188 AccessShareLock TRUE
250389 123502 NULL 2188 RowShareLock TRUE
250389 123502 NULL 2188 RowExclusiveLock TRUE
124818 123502 NULL 12638 AccessShareLock TRUE
125081 123502 NULL 8649 AccessShareLock TRUE
NULL NULL 3366872 21307 ExclusiveLock TRUE
123745 123502 NULL 27149 AccessShareLock TRUE
124578 123502 NULL 2188 AccessShareLock TRUE
124578 123502 NULL 2188 RowShareLock TRUE
124828 123502 NULL 2188 AccessShareLock TRUE
124813 123502 NULL 12638 AccessShareLock TRUE
124813 123502 NULL 12638 RowExclusiveLock TRUE
125113 123502 NULL 27149 AccessShareLock TRUE
124813 123502 NULL 27149 AccessShareLock TRUE
250389 123502 NULL 21307 AccessShareLock TRUE
250389 123502 NULL 21307 RowExclusiveLock TRUE
250393 123502 NULL 21307 AccessShareLock TRUE
250391 123502 NULL 21307 AccessShareLock TRUE
124813 123502 NULL 21440 AccessShareLock TRUE
124813 123502 NULL 21440 RowShareLock TRUE
131092 123502 NULL 21292 AccessShareLock TRUE
124518 123502 NULL 21292 AccessShareLock TRUE
124518 123502 NULL 21292 RowExclusiveLock TRUE
125273 123502 NULL 1069 AccessShareLock TRUE
124883 123502 NULL 2188 AccessShareLock TRUE
123697 123502 NULL 27149 AccessShareLock TRUE
124781 123502 NULL 27149 AccessShareLock TRUE
NULL NULL 3419997 12638 ExclusiveLock TRUE
250393 123502 NULL 8649 AccessShareLock TRUE
250393 123502 NULL 8649 RowExclusiveLock TRUE
NULL NULL 3379382 1069 ExclusiveLock TRUE
250393 123502 NULL 1069 AccessShareLock TRUE
250393 123502 NULL 1069 RowExclusiveLock TRUE
125038 123502 NULL 1069 AccessShareLock TRUE
NULL NULL 3377982 5510 ExclusiveLock TRUE
124300 123502 NULL 21292 AccessShareLock TRUE
125419 123502 NULL 27149 AccessShareLock TRUE
250385 123502 NULL 27149 AccessShareLock TRUE
250389 123502 NULL 8649 AccessShareLock TRUE

124 row(s)





^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: how to find transaction associated with a lock
  2004-07-22 19:16 how to find transaction associated with a lock Si Chen <schen@graciousstyle.com>
@ 2004-07-22 20:45 ` Tom Lane <tgl@sss.pgh.pa.us>
  2004-07-22 21:44   ` Re: [ADMIN] how to find transaction associated with a lock Si Chen <schen@graciousstyle.com>
  0 siblings, 1 reply; 6+ messages in thread

From: Tom Lane @ 2004-07-22 20:45 UTC (permalink / raw)
  To: schen@graciousstyle.com; +Cc: pgsql-general@postgresql.org; pgsql-novice@postgresql.org; pgsql-admin

Si Chen <schen@graciousstyle.com> writes:
> Does anyone know of a way to go from transactions identifiers to the 
> actual transaction--which tables, which statements, etc. etc.?

See pg_stat_activity.

The pg_locks entries you are looking at do *not* represent table locks
of any kind.  This:
> NULL NULL 3366868 27149 ExclusiveLock TRUE
simply says that transaction 3366868 is still running.  This:
> NULL NULL 3366868 1069 ShareLock FALSE
says that process 1069 has elected to wait until transaction 3366868
finishes.  Normally the reason for doing that would be that transaction
3366868 holds a row lock (SELECT FOR UPDATE lock) on some row that
process 1069's transaction wants to acquire row lock on.

However, I suspect what you are really wishing is to find out the
individual row that is being contended for, and there is not enough
information in the system views to do that (mainly because we do not
keep per-row locking information in shared memory).

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: [ADMIN] how to find transaction associated with a lock
  2004-07-22 19:16 how to find transaction associated with a lock Si Chen <schen@graciousstyle.com>
  2004-07-22 20:45 ` Re: how to find transaction associated with a lock Tom Lane <tgl@sss.pgh.pa.us>
@ 2004-07-22 21:44   ` Si Chen <schen@graciousstyle.com>
  2004-07-22 22:37     ` Re: [ADMIN] how to find transaction associated with a lock Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 6+ messages in thread

From: Si Chen @ 2004-07-22 21:44 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-general@postgresql.org

Hi Tom,

Thanks for the reply.  I looked in pg_stat_activity and it shows 
database tables and process ids.  So I should be using the pid next to 
the ExclusiveLock to see what database it is working on, right?

Now, the problem that I had was this: I have a java application that 
access postgresql (7.3.2) via jdbc.  It was running fine for a while, 
then of all sudden, against one table, the INSERT would simply hang.  It 
would INSERT fine on many other tables and the application could still 
run, except for INSERTs involving this particular table (unfortunately, 
an important table.)

Someone else had suggested that it was perhaps a lock on that particular 
table.  So I looked up view locking and then thought that this was where 
the bug was occurring.  Do you think I'm looking down the wrong path?  
Might it be something else (like the lack of a recent vacuuming or fsync 
not being set to true in postgresql.conf) that was causing this bug?

Thanks so much for your help!

Si Chen

Tom Lane wrote:

>Si Chen <schen@graciousstyle.com> writes:
>  
>
>>Does anyone know of a way to go from transactions identifiers to the 
>>actual transaction--which tables, which statements, etc. etc.?
>>    
>>
>
>See pg_stat_activity.
>
>The pg_locks entries you are looking at do *not* represent table locks
>of any kind.  This:
>  
>
>>NULL NULL 3366868 27149 ExclusiveLock TRUE
>>    
>>
>simply says that transaction 3366868 is still running.  This:
>  
>
>>NULL NULL 3366868 1069 ShareLock FALSE
>>    
>>
>says that process 1069 has elected to wait until transaction 3366868
>finishes.  Normally the reason for doing that would be that transaction
>3366868 holds a row lock (SELECT FOR UPDATE lock) on some row that
>process 1069's transaction wants to acquire row lock on.
>
>However, I suspect what you are really wishing is to find out the
>individual row that is being contended for, and there is not enough
>information in the system views to do that (mainly because we do not
>keep per-row locking information in shared memory).
>
>			regards, tom lane
>
>  
>




^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: [ADMIN] how to find transaction associated with a lock
  2004-07-22 19:16 how to find transaction associated with a lock Si Chen <schen@graciousstyle.com>
  2004-07-22 20:45 ` Re: how to find transaction associated with a lock Tom Lane <tgl@sss.pgh.pa.us>
  2004-07-22 21:44   ` Re: [ADMIN] how to find transaction associated with a lock Si Chen <schen@graciousstyle.com>
@ 2004-07-22 22:37     ` Tom Lane <tgl@sss.pgh.pa.us>
  2004-07-26 16:38       ` Re: [ADMIN] how to find transaction associated with a lock Si Chen <schen@graciousstyle.com>
  0 siblings, 1 reply; 6+ messages in thread

From: Tom Lane @ 2004-07-22 22:37 UTC (permalink / raw)
  To: schen@graciousstyle.com; +Cc: pgsql-general@postgresql.org

Si Chen <schen@graciousstyle.com> writes:
> Do you think I'm looking down the wrong path?  

I'd bet that the problem is contention for a row referenced by a foreign
key.  When you insert a row that has a foreign key reference to another
table, we need to lock the referenced row to ensure it doesn't get
deleted until the inserting transaction commits.  (Once you commit, your
inserted row is visible and it's then the responsibility of a deleting
transaction to notice the foreign-key violation.  But until then, a
deleting transaction can't even *see* your row so we need another way.)

The real problem here is that we only have exclusive locks at the row
level, so the only way to do this is to take an exclusive lock, and that
means that transactions inserting references to the same referenced row
block each other.  So I think your INSERT is waiting on commit of some
other transaction's INSERT that references the same foreign key row.

Fixing this is on the TODO list but I don't expect any solution in the
near future :-(

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: [ADMIN] how to find transaction associated with a lock
  2004-07-22 19:16 how to find transaction associated with a lock Si Chen <schen@graciousstyle.com>
  2004-07-22 20:45 ` Re: how to find transaction associated with a lock Tom Lane <tgl@sss.pgh.pa.us>
  2004-07-22 21:44   ` Re: [ADMIN] how to find transaction associated with a lock Si Chen <schen@graciousstyle.com>
  2004-07-22 22:37     ` Re: [ADMIN] how to find transaction associated with a lock Tom Lane <tgl@sss.pgh.pa.us>
@ 2004-07-26 16:38       ` Si Chen <schen@graciousstyle.com>
  2004-07-26 16:42         ` Re: [ADMIN] how to find transaction associated with a lock Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 6+ messages in thread

From: Si Chen @ 2004-07-26 16:38 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-general@postgresql.org

Hi Tom,

Thanks for your help last week.  I had the problem again where things 
got hung up during a transaction, and it was at the order_header table.  
Here is what the pg_locks show.  Does it confirm what you are saying, 
which is that multiple transactions have tried to insert into this table?

Thanks,

Si

>  
>

Attachments:

locks.txt (text/plain, 11.8K) download pid | datname | relname | mode -------+---------------+-----------------------------+------------------ 9861 | graciousstyle | billing_account | AccessShareLock 9862 | graciousstyle | billing_account | AccessShareLock 9931 | graciousstyle | billing_account | AccessShareLock 9932 | graciousstyle | billing_account | AccessShareLock 10060 | graciousstyle | billing_account | AccessShareLock 9862 | graciousstyle | carrier_shipment_method | AccessShareLock 9861 | graciousstyle | contact_mech | AccessShareLock 9862 | graciousstyle | contact_mech | AccessShareLock 9931 | graciousstyle | contact_mech | AccessShareLock 9932 | graciousstyle | contact_mech | AccessShareLock 10060 | graciousstyle | contact_mech | AccessShareLock 9861 | graciousstyle | credit_card | AccessShareLock 9931 | graciousstyle | credit_card | AccessShareLock 9932 | graciousstyle | credit_card | AccessShareLock 10060 | graciousstyle | credit_card | AccessShareLock 9862 | graciousstyle | entity_sync_remove | AccessShareLock 9862 | graciousstyle | entity_sync_remove | RowExclusiveLock 9862 | graciousstyle | facility | AccessShareLock 9862 | graciousstyle | facility | RowShareLock 9862 | graciousstyle | geo | AccessShareLock 9862 | graciousstyle | inventory_item | AccessShareLock 9862 | graciousstyle | inventory_item | RowExclusiveLock 9862 | graciousstyle | inventory_item_type | AccessShareLock 9862 | graciousstyle | inventory_item_type | RowShareLock 9861 | graciousstyle | item_issuance | AccessShareLock 9862 | graciousstyle | item_issuance | AccessShareLock 9931 | graciousstyle | item_issuance | AccessShareLock 9932 | graciousstyle | item_issuance | AccessShareLock 10060 | graciousstyle | item_issuance | AccessShareLock 9861 | graciousstyle | order_adjustment | AccessShareLock 9862 | graciousstyle | order_adjustment | AccessShareLock 9931 | graciousstyle | order_adjustment | AccessShareLock 9932 | graciousstyle | order_adjustment | AccessShareLock 9935 | graciousstyle | order_adjustment | AccessShareLock 10060 | graciousstyle | order_adjustment | AccessShareLock 9862 | graciousstyle | order_adjustment | RowExclusiveLock 9862 | graciousstyle | order_adjustment | RowShareLock 9862 | graciousstyle | order_adjustment_attribute | AccessShareLock 9862 | graciousstyle | order_adjustment_attribute | RowShareLock 9861 | graciousstyle | order_adjustment_type | AccessShareLock 9862 | graciousstyle | order_adjustment_type | AccessShareLock 9931 | graciousstyle | order_adjustment_type | AccessShareLock 9932 | graciousstyle | order_adjustment_type | AccessShareLock 10060 | graciousstyle | order_adjustment_type | AccessShareLock 9862 | graciousstyle | order_adjustment_type | RowShareLock 9861 | graciousstyle | order_contact_mech | AccessShareLock 9862 | graciousstyle | order_contact_mech | AccessShareLock 9931 | graciousstyle | order_contact_mech | AccessShareLock 9932 | graciousstyle | order_contact_mech | AccessShareLock 10060 | graciousstyle | order_contact_mech | AccessShareLock 9861 | graciousstyle | order_header | AccessShareLock 9862 | graciousstyle | order_header | AccessShareLock 9931 | graciousstyle | order_header | AccessShareLock 9932 | graciousstyle | order_header | AccessShareLock 9935 | graciousstyle | order_header | AccessShareLock 10060 | graciousstyle | order_header | AccessShareLock 27874 | graciousstyle | order_header | AccessShareLock 9862 | graciousstyle | order_header | RowExclusiveLock 9935 | graciousstyle | order_header | RowExclusiveLock 27874 | graciousstyle | order_header | RowExclusiveLock 9861 | graciousstyle | order_header | RowShareLock 9862 | graciousstyle | order_header | RowShareLock 9931 | graciousstyle | order_header | RowShareLock 9932 | graciousstyle | order_header | RowShareLock 10060 | graciousstyle | order_header | RowShareLock 9861 | graciousstyle | order_item | AccessShareLock 9862 | graciousstyle | order_item | AccessShareLock 9931 | graciousstyle | order_item | AccessShareLock 9932 | graciousstyle | order_item | AccessShareLock 9935 | graciousstyle | order_item | AccessShareLock 10060 | graciousstyle | order_item | AccessShareLock 9862 | graciousstyle | order_item | RowExclusiveLock 9862 | graciousstyle | order_item_inventory_res | AccessShareLock 9862 | graciousstyle | order_item_inventory_res | RowExclusiveLock 9862 | graciousstyle | order_item_type | AccessShareLock 9862 | graciousstyle | order_item_type | RowShareLock 9861 | graciousstyle | order_notification | AccessShareLock 9931 | graciousstyle | order_notification | AccessShareLock 9932 | graciousstyle | order_notification | AccessShareLock 10060 | graciousstyle | order_notification | AccessShareLock 9861 | graciousstyle | order_notification | RowExclusiveLock 9931 | graciousstyle | order_notification | RowExclusiveLock 9932 | graciousstyle | order_notification | RowExclusiveLock 10060 | graciousstyle | order_notification | RowExclusiveLock 9861 | graciousstyle | order_payment_preference | AccessShareLock 9862 | graciousstyle | order_payment_preference | AccessShareLock 9931 | graciousstyle | order_payment_preference | AccessShareLock 9932 | graciousstyle | order_payment_preference | AccessShareLock 10060 | graciousstyle | order_payment_preference | AccessShareLock 9861 | graciousstyle | order_role | AccessShareLock 9862 | graciousstyle | order_role | AccessShareLock 9931 | graciousstyle | order_role | AccessShareLock 9932 | graciousstyle | order_role | AccessShareLock 10060 | graciousstyle | order_role | AccessShareLock 9861 | graciousstyle | order_shipment_preference | AccessShareLock 9862 | graciousstyle | order_shipment_preference | AccessShareLock 9931 | graciousstyle | order_shipment_preference | AccessShareLock 9932 | graciousstyle | order_shipment_preference | AccessShareLock 10060 | graciousstyle | order_shipment_preference | AccessShareLock 9861 | graciousstyle | order_status | AccessShareLock 9862 | graciousstyle | order_status | AccessShareLock 9931 | graciousstyle | order_status | AccessShareLock 9932 | graciousstyle | order_status | AccessShareLock 10060 | graciousstyle | order_status | AccessShareLock 9862 | graciousstyle | order_status | RowExclusiveLock 9862 | graciousstyle | order_type | AccessShareLock 27874 | graciousstyle | order_type | AccessShareLock 9862 | graciousstyle | order_type | RowShareLock 27874 | graciousstyle | order_type | RowShareLock 9861 | graciousstyle | payment_method | AccessShareLock 9862 | graciousstyle | payment_method | AccessShareLock 9931 | graciousstyle | payment_method | AccessShareLock 9932 | graciousstyle | payment_method | AccessShareLock 10060 | graciousstyle | payment_method | AccessShareLock 9861 | graciousstyle | person | AccessShareLock 9862 | graciousstyle | person | AccessShareLock 9931 | graciousstyle | person | AccessShareLock 9932 | graciousstyle | person | AccessShareLock 10060 | graciousstyle | person | AccessShareLock 28124 | graciousstyle | pg_class | AccessShareLock 28124 | graciousstyle | pg_class_oid_index | AccessShareLock 28124 | graciousstyle | pg_locks | AccessShareLock 9861 | graciousstyle | pk_order_header | AccessShareLock 9931 | graciousstyle | pk_order_header | AccessShareLock 9932 | graciousstyle | pk_order_header | AccessShareLock 9935 | graciousstyle | pk_order_header | AccessShareLock 10060 | graciousstyle | pk_order_header | AccessShareLock 9861 | graciousstyle | postal_address | AccessShareLock 9862 | graciousstyle | postal_address | AccessShareLock 9931 | graciousstyle | postal_address | AccessShareLock 9932 | graciousstyle | postal_address | AccessShareLock 10060 | graciousstyle | postal_address | AccessShareLock 9862 | graciousstyle | product | AccessShareLock 9862 | graciousstyle | product | RowShareLock 9862 | graciousstyle | product_assoc | AccessShareLock 9862 | graciousstyle | product_feature_appl | AccessShareLock 9861 | graciousstyle | product_store_email_setting | AccessShareLock 9862 | graciousstyle | product_store_email_setting | AccessShareLock 9931 | graciousstyle | product_store_email_setting | AccessShareLock 9932 | graciousstyle | product_store_email_setting | AccessShareLock 10060 | graciousstyle | product_store_email_setting | AccessShareLock 9862 | graciousstyle | quantity_break | AccessShareLock 9862 | graciousstyle | security_group_permission | AccessShareLock 9862 | graciousstyle | shipment_cost_estimate | AccessShareLock 9861 | graciousstyle | shipment_method_type | AccessShareLock 9862 | graciousstyle | shipment_method_type | AccessShareLock 9931 | graciousstyle | shipment_method_type | AccessShareLock 9932 | graciousstyle | shipment_method_type | AccessShareLock 10060 | graciousstyle | shipment_method_type | AccessShareLock 9861 | graciousstyle | shipment_package_route_seg | AccessShareLock 9862 | graciousstyle | shipment_package_route_seg | AccessShareLock 9931 | graciousstyle | shipment_package_route_seg | AccessShareLock 9932 | graciousstyle | shipment_package_route_seg | AccessShareLock 10060 | graciousstyle | shipment_package_route_seg | AccessShareLock 9861 | graciousstyle | shipment_route_segment | AccessShareLock 9862 | graciousstyle | shipment_route_segment | AccessShareLock 9931 | graciousstyle | shipment_route_segment | AccessShareLock 9932 | graciousstyle | shipment_route_segment | AccessShareLock 10060 | graciousstyle | shipment_route_segment | AccessShareLock 9862 | graciousstyle | simple_sales_tax_lookup | AccessShareLock 9862 | graciousstyle | status_item | AccessShareLock 9931 | graciousstyle | status_item | AccessShareLock 9932 | graciousstyle | status_item | AccessShareLock 9862 | graciousstyle | status_item | RowShareLock 9862 | graciousstyle | status_valid_change | AccessShareLock 9862 | graciousstyle | user_login | AccessShareLock 27874 | graciousstyle | user_login | AccessShareLock 9862 | graciousstyle | user_login_security_group | AccessShareLock 9862 | graciousstyle | work_effort | AccessShareLock (169 rows)
^ permalink raw reply [nested|flat] 6+ messages in thread

* Re: [ADMIN] how to find transaction associated with a lock
  2004-07-22 19:16 how to find transaction associated with a lock Si Chen <schen@graciousstyle.com>
  2004-07-22 20:45 ` Re: how to find transaction associated with a lock Tom Lane <tgl@sss.pgh.pa.us>
  2004-07-22 21:44   ` Re: [ADMIN] how to find transaction associated with a lock Si Chen <schen@graciousstyle.com>
  2004-07-22 22:37     ` Re: [ADMIN] how to find transaction associated with a lock Tom Lane <tgl@sss.pgh.pa.us>
  2004-07-26 16:38       ` Re: [ADMIN] how to find transaction associated with a lock Si Chen <schen@graciousstyle.com>
@ 2004-07-26 16:42         ` Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 0 replies; 6+ messages in thread

From: Tom Lane @ 2004-07-26 16:42 UTC (permalink / raw)
  To: schen@graciousstyle.com; +Cc: pgsql-general@postgresql.org

Si Chen <schen@graciousstyle.com> writes:
> Thanks for your help last week.  I had the problem again where things 
> got hung up during a transaction, and it was at the order_header table.  
> Here is what the pg_locks show.  Does it confirm what you are saying, 
> which is that multiple transactions have tried to insert into this table?

It's impossible to tell much of anything from a partial view of the
pg_locks table.  You didn't show us the "granted" flag, so we can't
tell which rows represent held locks and which represent waited-for
locks; and you didn't show transaction locks at all, which are probably
where the issue really is anyway.

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 6+ messages in thread


end of thread, other threads:[~2004-07-26 16:42 UTC | newest]

Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-07-22 19:16 how to find transaction associated with a lock Si Chen <schen@graciousstyle.com>
2004-07-22 20:45 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-07-22 21:44   ` Si Chen <schen@graciousstyle.com>
2004-07-22 22:37     ` Tom Lane <tgl@sss.pgh.pa.us>
2004-07-26 16:38       ` Si Chen <schen@graciousstyle.com>
2004-07-26 16:42         ` Tom Lane <tgl@sss.pgh.pa.us>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox