Обсуждение: table locking on creating FK

Hi everyone,

Why does a table lock up if I want to create a FK to it?  e.g.  I have a
separate schema for my own mods to the database but if I want to
reference anything in the public schema on a customer table, that table
will be locked up.

Why does a table lockup when disabling a trigger on it?

I just tried this on a live database, and ended up restarting the
postgres service because the whole table was locked and no users were
able to do anything.

I guess I'm dumb (or stupid) to try it in production, but I wanted to
create an index on an audit table, so I knew enough that I would have to
disable the audit trigger before I could create an index on a 1.8
million row table.  Then the main gltx table locked up on disabling the
trigger.  I found the pid of the process by doing this:

select * from pg_stat_activity where query ilike '%trigger%';

Then tried to cancel the query using this:

select pg_cancel_backend(17069);

But that did not happen within 1 min, and with 90 sales people all
waiting on this server, I did a kill -9 on that pid to get everyone back
as soon as possible.  This caused a bunch of "terminating connection
because of crash of another server process" errors in pg_log, but I
don't see anything serious after that.

Is there any way to recover from a locked situation like this?

Thanks,

Mark

Mark,

* M. D. (lists@turnkey.bz) wrote:
> Why does a table lock up if I want to create a FK to it?  e.g.  I
> have a separate schema for my own mods to the database but if I want
> to reference anything in the public schema on a customer table, that
> table will be locked up.

That's correct, creating a foreign key to a table requires an
AccessExclusiveLock on the referred-to table.

> Why does a table lockup when disabling a trigger on it?

For both of these, the issue is that we have to make sure every backend
has the same view of the table and all triggers, etc, which exist on the
table.  There is ongoing work to reduce lock levels where possible, now
that PG accesses the catalogs using MVCC semantics (which was not true
previously), but I wouldn't get your hopes up on these changing.

> I just tried this on a live database, and ended up restarting the
> postgres service because the whole table was locked and no users
> were able to do anything.

You would need to simply kill the transaction which held the locks,
using pg_terminate_backend().

> I guess I'm dumb (or stupid) to try it in production, but I wanted
> to create an index on an audit table, so I knew enough that I would
> have to disable the audit trigger before I could create an index on
> a 1.8 million row table.  Then the main gltx table locked up on
> disabling the trigger.  I found the pid of the process by doing
> this:

You might want to investigate the 'CONCURRENTLY' option of CREATE INDEX.

> select * from pg_stat_activity where query ilike '%trigger%';
>
> Then tried to cancel the query using this:
>
> select pg_cancel_backend(17069);

pg_cancel_backend() will cancel a running *query* but it does not
terminate the transaction.  Locks are held until the end of a
transaction.  You likely wanted 'pg_terminate_backend()', as mentioned
above, which would have both rolled back the transaction and termianted
the database connection.

> But that did not happen within 1 min, and with 90 sales people all
> waiting on this server, I did a kill -9 on that pid to get everyone
> back as soon as possible.  This caused a bunch of "terminating
> connection because of crash of another server process" errors in
> pg_log, but I don't see anything serious after that.

Doing a -9 against a PG server is a very bad idea- don't do it.  Use
pg_terminate_backend().

> Is there any way to recover from a locked situation like this?

In general, I'd suggest you avoid trying to do DDL without a proper
outage window or at least only during non-peak times and only once you
have a good understanding of what locks will be taken out, and for how
long, during your DDL work.

Note also that the way locking is done in PG, once someone wants a
higher lock on a table, everyone else wanting locks on the table have to
wait (even if the table is only currently locked at the lower level).
This avoids the higher-level lock process being stalled forever but does
mean those locks have a high impact on the running system.

    Thanks,

        Stephen

Mark,
You can also read this article
http://momjian.us/main/writings/pgsql/locking.pdf, it article help me
a lot with understanding postgresql locking mechanism.

2014-05-08 4:54 GMT+04:00 Stephen Frost <sfrost@snowman.net>:
> Mark,
>
> * M. D. (lists@turnkey.bz) wrote:
>> Why does a table lock up if I want to create a FK to it?  e.g.  I
>> have a separate schema for my own mods to the database but if I want
>> to reference anything in the public schema on a customer table, that
>> table will be locked up.
>
> That's correct, creating a foreign key to a table requires an
> AccessExclusiveLock on the referred-to table.
>
>> Why does a table lockup when disabling a trigger on it?
>
> For both of these, the issue is that we have to make sure every backend
> has the same view of the table and all triggers, etc, which exist on the
> table.  There is ongoing work to reduce lock levels where possible, now
> that PG accesses the catalogs using MVCC semantics (which was not true
> previously), but I wouldn't get your hopes up on these changing.
>
>> I just tried this on a live database, and ended up restarting the
>> postgres service because the whole table was locked and no users
>> were able to do anything.
>
> You would need to simply kill the transaction which held the locks,
> using pg_terminate_backend().
>
>> I guess I'm dumb (or stupid) to try it in production, but I wanted
>> to create an index on an audit table, so I knew enough that I would
>> have to disable the audit trigger before I could create an index on
>> a 1.8 million row table.  Then the main gltx table locked up on
>> disabling the trigger.  I found the pid of the process by doing
>> this:
>
> You might want to investigate the 'CONCURRENTLY' option of CREATE INDEX.
>
>> select * from pg_stat_activity where query ilike '%trigger%';
>>
>> Then tried to cancel the query using this:
>>
>> select pg_cancel_backend(17069);
>
> pg_cancel_backend() will cancel a running *query* but it does not
> terminate the transaction.  Locks are held until the end of a
> transaction.  You likely wanted 'pg_terminate_backend()', as mentioned
> above, which would have both rolled back the transaction and termianted
> the database connection.
>
>> But that did not happen within 1 min, and with 90 sales people all
>> waiting on this server, I did a kill -9 on that pid to get everyone
>> back as soon as possible.  This caused a bunch of "terminating
>> connection because of crash of another server process" errors in
>> pg_log, but I don't see anything serious after that.
>
> Doing a -9 against a PG server is a very bad idea- don't do it.  Use
> pg_terminate_backend().
>
>> Is there any way to recover from a locked situation like this?
>
> In general, I'd suggest you avoid trying to do DDL without a proper
> outage window or at least only during non-peak times and only once you
> have a good understanding of what locks will be taken out, and for how
> long, during your DDL work.
>
> Note also that the way locking is done in PG, once someone wants a
> higher lock on a table, everyone else wanting locks on the table have to
> wait (even if the table is only currently locked at the lower level).
> This avoids the higher-level lock process being stalled forever but does
> mean those locks have a high impact on the running system.
>
>         Thanks,
>
>                 Stephen



--
Best Regards,
Seliavka Evgenii

On 05/07/2014 06:54 PM, Stephen Frost wrote:
> Mark,
>
> * M. D. (lists@turnkey.bz) wrote:
>> Why does a table lock up if I want to create a FK to it?  e.g.  I
>> have a separate schema for my own mods to the database but if I want
>> to reference anything in the public schema on a customer table, that
>> table will be locked up.
> That's correct, creating a foreign key to a table requires an
> AccessExclusiveLock on the referred-to table.
>
>> Why does a table lockup when disabling a trigger on it?
> For both of these, the issue is that we have to make sure every backend
> has the same view of the table and all triggers, etc, which exist on the
> table.  There is ongoing work to reduce lock levels where possible, now
> that PG accesses the catalogs using MVCC semantics (which was not true
> previously), but I wouldn't get your hopes up on these changing.
>
>> I just tried this on a live database, and ended up restarting the
>> postgres service because the whole table was locked and no users
>> were able to do anything.
> You would need to simply kill the transaction which held the locks,
> using pg_terminate_backend().
>
>> I guess I'm dumb (or stupid) to try it in production, but I wanted
>> to create an index on an audit table, so I knew enough that I would
>> have to disable the audit trigger before I could create an index on
>> a 1.8 million row table.  Then the main gltx table locked up on
>> disabling the trigger.  I found the pid of the process by doing
>> this:
> You might want to investigate the 'CONCURRENTLY' option of CREATE INDEX.
>
>> select * from pg_stat_activity where query ilike '%trigger%';
>>
>> Then tried to cancel the query using this:
>>
>> select pg_cancel_backend(17069);
> pg_cancel_backend() will cancel a running *query* but it does not
> terminate the transaction.  Locks are held until the end of a
> transaction.  You likely wanted 'pg_terminate_backend()', as mentioned
> above, which would have both rolled back the transaction and termianted
> the database connection.
>
>> But that did not happen within 1 min, and with 90 sales people all
>> waiting on this server, I did a kill -9 on that pid to get everyone
>> back as soon as possible.  This caused a bunch of "terminating
>> connection because of crash of another server process" errors in
>> pg_log, but I don't see anything serious after that.
> Doing a -9 against a PG server is a very bad idea- don't do it.  Use
> pg_terminate_backend().
>
>> Is there any way to recover from a locked situation like this?
> In general, I'd suggest you avoid trying to do DDL without a proper
> outage window or at least only during non-peak times and only once you
> have a good understanding of what locks will be taken out, and for how
> long, during your DDL work.
>
> Note also that the way locking is done in PG, once someone wants a
> higher lock on a table, everyone else wanting locks on the table have to
> wait (even if the table is only currently locked at the lower level).
> This avoids the higher-level lock process being stalled forever but does
> mean those locks have a high impact on the running system.
>
>     Thanks,
>
>         Stephen
Thank you ---  I'm very grateful for such a clear description and help.
The FK creation was something I had tried before and knew not to do
again, but I did not think that disabling the trigger would do the same.

Thanks again.   I'll tag this for myself as reference.