Thread: how to find transaction associated with a lock
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)
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
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 > > >
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
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 > > 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)
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