Обсуждение: Exhaustive list of what takes what locks
Dear list,
Is there an exhaustive list of what takes what locks and how long they last? I'm asking because we just had some trouble doing a hot db change to an 8.3.6 system. I know it is an old version but it is what I have to work with. You can reproduce it like so:
First:
DROP TABLE IF EXISTS foo;
DROP TABLE IF EXISTS account;
CREATE TABLE account (account_id SERIAL PRIMARY KEY, name CHARACTER VARYING NOT NULL);
CREATE TABLE foo (account_id INTEGER NOT NULL REFERENCES account (account_id), stuff CHARACTER VARYING);
In one connection:
INSERT INTO account (name) SELECT generate_series FROM GENERATE_SERIES(0, 10000000);
In another connection while that last one is running:
DROP TABLE foo;
And in another connection if you are feeling frisky:
select
pg_stat_activity.datname,pg_class.relname,pg_locks.transactionid, pg_locks.mode, pg_locks.granted,
pg_stat_activity.usename,pg_stat_activity.current_query, pg_stat_activity.query_start,
age(now(),pg_stat_activity.query_start) as "age", pg_stat_activity.procpid
from pg_stat_activity,pg_locks left
outer join pg_class on (pg_locks.relation = pg_class.oid)
where pg_locks.pid=pg_stat_activity.procpid order by query_start;
That query shows that the DROP takes an AccessExclusiveLock on account. This isn't totally unexpected but it is unfortunate because it means we have to wait for a downtime window to maintain constraints even if they are not really in use.
This isn't exactly how our workload actually works. Ours is more deadlock prone. We have many connections all querying account and we do the migration in a transaction. It looks as though the AccessExclusiveLock is held until the transaction terminates.
Nik Everett
Nikolas Everett wrote: > Is there an exhaustive list of what takes what locks and how long they > last? I'm asking because we just had some trouble doing a hot db > change to an 8.3.6 system. I know it is an old version but it is what > I have to work with. There haven't been any major changes in this area since then, it wouldn't really matter if you were on a newer version. The short answer to your question is that no, there is no such list. The documentation at http://www.postgresql.org/docs/current/interactive/explicit-locking.html and http://www.postgresql.org/docs/current/interactive/view-pg-locks.html are unfortunately as good as it gets right now. The subject is a bit more complicated even than it appears at first, given that you don't just need to take into account what statement is executing. You need to know things like whether any foreign keys are involved as well as what index type is used (see http://www.postgresql.org/docs/current/interactive/locking-indexes.html ) to fully predict what the locking situation for your SQL is going to become. It's a fairly big grid of things to take into account. -- Greg Smith 2ndQuadrant US greg@2ndQuadrant.com Baltimore, MD PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us "PostgreSQL 9.0 High Performance": http://www.2ndQuadrant.com/books
Given that the a list would be difficult to maintain, is there some way I can make Postgres spit out the list of what locks are taken?
--Nik
On Wed, Feb 2, 2011 at 1:58 PM, Greg Smith <greg@2ndquadrant.com> wrote:
Nikolas Everett wrote:There haven't been any major changes in this area since then, it wouldn't really matter if you were on a newer version. The short answer to your question is that no, there is no such list. The documentation at http://www.postgresql.org/docs/current/interactive/explicit-locking.html and http://www.postgresql.org/docs/current/interactive/view-pg-locks.html are unfortunately as good as it gets right now. The subject is a bit more complicated even than it appears at first, given that you don't just need to take into account what statement is executing. You need to know things like whether any foreign keys are involved as well as what index type is used (see http://www.postgresql.org/docs/current/interactive/locking-indexes.html ) to fully predict what the locking situation for your SQL is going to become. It's a fairly big grid of things to take into account.Is there an exhaustive list of what takes what locks and how long they last? I'm asking because we just had some trouble doing a hot db change to an 8.3.6 system. I know it is an old version but it is what I have to work with.
--
Greg Smith 2ndQuadrant US greg@2ndQuadrant.com Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us
"PostgreSQL 9.0 High Performance": http://www.2ndQuadrant.com/books
On Wed, Feb 2, 2011 at 2:53 PM, Nikolas Everett <nik9000@gmail.com> wrote:
Given that the a list would be difficult to maintain, is there some way I can make Postgres spit out the list of what locks are taken?--Nik
I just answered my own question -
compile with -DLOCK_DEBUG in your src/Makefile.custom and then SET TRACK_LOCKS=true when you want it.
--Nik
On Wed, Feb 2, 2011 at 3:29 PM, Nikolas Everett <nik9000@gmail.com> wrote:
On Wed, Feb 2, 2011 at 2:53 PM, Nikolas Everett <nik9000@gmail.com> wrote:Given that the a list would be difficult to maintain, is there some way I can make Postgres spit out the list of what locks are taken?--NikI just answered my own question -compile with -DLOCK_DEBUG in your src/Makefile.custom and then SET TRACK_LOCKS=true when you want it.--Nik
I just wrote a script to parse the output of postgres' log file into something more useful to me. I'm not sure that it is right but it certainly seems to be working.
I shoved the script here in case it is useful to anyone: https://github.com/nik9000/Postgres-Tools
On Tue, Feb 1, 2011 at 2:18 PM, Nikolas Everett <nik9000@gmail.com> wrote: > This isn't exactly how our workload actually works. Ours is more deadlock > prone. We have many connections all querying account and we do the > migration in a transaction. It looks as though the AccessExclusiveLock is > held until the transaction terminates. Unfortunately, that's necessary for correctness. :-( I'd really like to figure out some way to make these cases work with less locking. 9.1 will have some improvements in this area, as regards ALTER TABLE, but dropping a constraint will still require AccessExclusiveLock. There are even workloads where competition for AccessShareLock on the target table is a performance bottleneck (try pgbench -S -c 36 -j 36 or so). I've been idly mulling over whether there's any way to eliminate that locking or at least make it uncontended in the common case, but so far haven't thought of a solution that I'm entirely happy with. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
On Tue, Feb 01, 2011 at 02:18:37PM -0500, Nikolas Everett wrote: > Is there an exhaustive list of what takes what locks and how long they last? This documents which commands take each lock type, but it is not exhaustive: http://www.postgresql.org/docs/current/interactive/explicit-locking.html All locks on user-created database objects last until the transaction ends. This does not apply to advisory locks. Also, many commands internally take locks on system catalogs and release those locks as soon as possible. > CREATE TABLE account (account_id SERIAL PRIMARY KEY, name CHARACTER VARYING > NOT NULL); > CREATE TABLE foo (account_id INTEGER NOT NULL REFERENCES account > (account_id), stuff CHARACTER VARYING); > DROP TABLE foo; > That query shows that the DROP takes an AccessExclusiveLock on account. > This isn't totally unexpected but it is unfortunate because it means we > have to wait for a downtime window to maintain constraints even if they are > not really in use. PostgreSQL 9.1 will contain changes to make similar operations, though not that one, take ShareRowExclusiveLock instead of AccessExclusiveLock. Offhand, the same optimization probably could be arranged for it with minimal fuss. If "account" is heavily queried but seldom changed, that might be enough for you. The internal implementation of a FOREIGN KEY constraint takes the form of triggers on both tables. Each INSERT or UPDATE needs to know definitively whether to fire a given trigger, so adding or removing an arbitrary trigger will continue to require at least ShareRowExclusiveLock. In the abstract, the special case of a FOREIGN KEY constraint could be looser still, but that would be tricky to implement. nm
On Wed, Feb 2, 2011 at 12:20 AM, Noah Misch <noah@leadboat.com> wrote: >> CREATE TABLE account (account_id SERIAL PRIMARY KEY, name CHARACTER VARYING >> NOT NULL); >> CREATE TABLE foo (account_id INTEGER NOT NULL REFERENCES account >> (account_id), stuff CHARACTER VARYING); > >> DROP TABLE foo; > >> That query shows that the DROP takes an AccessExclusiveLock on account. >> This isn't totally unexpected but it is unfortunate because it means we >> have to wait for a downtime window to maintain constraints even if they are >> not really in use. > > PostgreSQL 9.1 will contain changes to make similar operations, though not that > one, take ShareRowExclusiveLock instead of AccessExclusiveLock. Offhand, the > same optimization probably could be arranged for it with minimal fuss. If > "account" is heavily queried but seldom changed, that might be enough for you. The problem is that constraints can affect the query plan. If a transaction sees the constraint in the system catalogs (under SnapshotNow) but the table data doesn't conform (under some earlier snapshot) and if the chosen plan depends on the validity of the constraint, then we've got trouble. At least when running at READ COMMITTED, taking an AccessExclusiveLock protects us against that hazard (I'm not exactly sure what if anything protects us at higher isolation levels... but I hope there is something). Now, it's true that in the specific case of a foreign key constraint, we don't currently have anything in the planner that depends on that. But I'm hoping to get around to working on inner join removal again one of these days. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
Robert Haas <robertmhaas@gmail.com> writes:
> The problem is that constraints can affect the query plan. If a
> transaction sees the constraint in the system catalogs (under
> SnapshotNow) but the table data doesn't conform (under some earlier
> snapshot) and if the chosen plan depends on the validity of the
> constraint, then we've got trouble. At least when running at READ
> COMMITTED, taking an AccessExclusiveLock protects us against that
> hazard (I'm not exactly sure what if anything protects us at higher
> isolation levels... but I hope there is something).
Interesting point. If we really wanted to make that work "right",
we might have to do something like the hack that's in place for CREATE
INDEX CONCURRENTLY, wherein there's a notion that an index can't be used
by a transaction with xmin before some horizon. Not entirely convinced
it's worth the trouble, but ...
> Now, it's true that in the specific case of a foreign key constraint,
> we don't currently have anything in the planner that depends on that.
> But I'm hoping to get around to working on inner join removal again
> one of these days.
Yeah, that sort of thing will certainly be there eventually.
regards, tom lane
On Tue, Feb 22, 2011 at 10:18:36PM -0500, Robert Haas wrote:
> On Wed, Feb 2, 2011 at 12:20 AM, Noah Misch <noah@leadboat.com> wrote:
> >> CREATE TABLE account (account_id SERIAL PRIMARY KEY, name CHARACTER VARYING
> >> NOT NULL);
> >> CREATE TABLE foo (account_id INTEGER NOT NULL REFERENCES account
> >> (account_id), stuff CHARACTER VARYING);
> >
> >> DROP TABLE foo;
> >
> >> That query shows that the DROP takes an AccessExclusiveLock on account.
> >> ?This isn't totally unexpected but it is unfortunate because it means we
> >> have to wait for a downtime window to maintain constraints even if they are
> >> not really in use.
> >
> > PostgreSQL 9.1 will contain changes to make similar operations, though not that
> > one, take ShareRowExclusiveLock instead of AccessExclusiveLock. ?Offhand, the
> > same optimization probably could be arranged for it with minimal fuss. ?If
> > "account" is heavily queried but seldom changed, that might be enough for you.
>
> The problem is that constraints can affect the query plan. If a
> transaction sees the constraint in the system catalogs (under
> SnapshotNow) but the table data doesn't conform (under some earlier
> snapshot) and if the chosen plan depends on the validity of the
> constraint, then we've got trouble. At least when running at READ
> COMMITTED, taking an AccessExclusiveLock protects us against that
> hazard (I'm not exactly sure what if anything protects us at higher
> isolation levels... but I hope there is something).
AccessExclusiveLock does not prevent that problem. We're already on thin ice in
this regard:
-- session 1
CREATE TABLE t (x) AS SELECT NULL::int;
BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT 1;
-- session 2
DELETE FROM t;
ALTER TABLE t ALTER x SET NOT NULL;
-- session 1
TABLE t;
With contortions, we can coax the same from READ COMMITTED:
-- session 1
CREATE TABLE t (x) AS SELECT NULL::int;
CREATE FUNCTION pg_temp.f() RETURNS int LANGUAGE sql
STABLE -- reuse snapshot
AS 'SELECT 1; TABLE t'; -- extra statement to avoid inlining
VALUES (pg_sleep(15), pg_temp.f());
-- session 2
DELETE FROM t;
ALTER TABLE t ALTER x SET NOT NULL;
The catalogs say x is NOT NULL, but we read a NULL value just the same. I'm not
sure what anomalies this permits today, if any, but it's in the same vein.
On Tue, Feb 22, 2011 at 11:21 PM, Noah Misch <noah@leadboat.com> wrote:
> On Tue, Feb 22, 2011 at 10:18:36PM -0500, Robert Haas wrote:
>> On Wed, Feb 2, 2011 at 12:20 AM, Noah Misch <noah@leadboat.com> wrote:
>> >> CREATE TABLE account (account_id SERIAL PRIMARY KEY, name CHARACTER VARYING
>> >> NOT NULL);
>> >> CREATE TABLE foo (account_id INTEGER NOT NULL REFERENCES account
>> >> (account_id), stuff CHARACTER VARYING);
>> >
>> >> DROP TABLE foo;
>> >
>> >> That query shows that the DROP takes an AccessExclusiveLock on account.
>> >> ?This isn't totally unexpected but it is unfortunate because it means we
>> >> have to wait for a downtime window to maintain constraints even if they are
>> >> not really in use.
>> >
>> > PostgreSQL 9.1 will contain changes to make similar operations, though not that
>> > one, take ShareRowExclusiveLock instead of AccessExclusiveLock. ?Offhand, the
>> > same optimization probably could be arranged for it with minimal fuss. ?If
>> > "account" is heavily queried but seldom changed, that might be enough for you.
>>
>> The problem is that constraints can affect the query plan. If a
>> transaction sees the constraint in the system catalogs (under
>> SnapshotNow) but the table data doesn't conform (under some earlier
>> snapshot) and if the chosen plan depends on the validity of the
>> constraint, then we've got trouble. At least when running at READ
>> COMMITTED, taking an AccessExclusiveLock protects us against that
>> hazard (I'm not exactly sure what if anything protects us at higher
>> isolation levels... but I hope there is something).
>
> AccessExclusiveLock does not prevent that problem. We're already on thin ice in
> this regard:
>
> -- session 1
> CREATE TABLE t (x) AS SELECT NULL::int;
> BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
> SELECT 1;
> -- session 2
> DELETE FROM t;
> ALTER TABLE t ALTER x SET NOT NULL;
> -- session 1
> TABLE t;
>
> With contortions, we can coax the same from READ COMMITTED:
>
> -- session 1
> CREATE TABLE t (x) AS SELECT NULL::int;
> CREATE FUNCTION pg_temp.f() RETURNS int LANGUAGE sql
> STABLE -- reuse snapshot
> AS 'SELECT 1; TABLE t'; -- extra statement to avoid inlining
> VALUES (pg_sleep(15), pg_temp.f());
> -- session 2
> DELETE FROM t;
> ALTER TABLE t ALTER x SET NOT NULL;
>
> The catalogs say x is NOT NULL, but we read a NULL value just the same. I'm not
> sure what anomalies this permits today, if any, but it's in the same vein.
Ugh. Well, I guess if we want to fix that we need the conxmin bit Tom
was just musing about. That sucks.
I wonder if it'd be safe to reduce the locking strength for *dropping*
a constraint, though. The comment just says:
case AT_DropConstraint: /* as DROP INDEX */
...but that begs the question of why DROP INDEX needs an
AccessExclusiveLock. It probably needs such a lock *on the index* but
I don't see why we'd need it on the table.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
Robert Haas <robertmhaas@gmail.com> writes:
> ...but that begs the question of why DROP INDEX needs an
> AccessExclusiveLock. It probably needs such a lock *on the index* but
> I don't see why we'd need it on the table.
Some other session might be in process of planning a query on the table.
It would be sad if the index it had chosen turned out to have vanished
meanwhile. You could perhaps confine DROP INDEX's ex-lock to the index,
but only at the price of making the planner take out a lock on every
index it considers even transiently. Which isn't going to be a net
improvement.
(While we're on the subject, I have strong suspicions that most of what
Simon did this cycle on ALTER TABLE lock strength reduction is
hopelessly broken and will have to be reverted. It's on my to-do list
to try to break that patch during beta, and I expect to succeed.)
regards, tom lane
On Wed, Feb 23, 2011 at 12:31 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Robert Haas <robertmhaas@gmail.com> writes: >> ...but that begs the question of why DROP INDEX needs an >> AccessExclusiveLock. It probably needs such a lock *on the index* but >> I don't see why we'd need it on the table. > > Some other session might be in process of planning a query on the table. > It would be sad if the index it had chosen turned out to have vanished > meanwhile. You could perhaps confine DROP INDEX's ex-lock to the index, > but only at the price of making the planner take out a lock on every > index it considers even transiently. Which isn't going to be a net > improvement. Oh. I assumed we were doing that anyway. If not, yeah. > (While we're on the subject, I have strong suspicions that most of what > Simon did this cycle on ALTER TABLE lock strength reduction is > hopelessly broken and will have to be reverted. It's on my to-do list > to try to break that patch during beta, and I expect to succeed.) It wouldn't surprise me if there are some holes there. But I'd like to try to preserve as much of it as we can, and I think there's probably a good chunk of it that is OK. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company