Обсуждение: 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.