Thread: autovacuum and locks
Hi all, recently our application was locked, and postgres shows several processes in "LOCK TABLE waiting" state. Even the autovacuum process is stalled. The pg_locks table shows the following: > SELECT pg_class.relname AS table, transaction, pid, mode, granted FROM pg_locks, pg_class, pg_database WHERE pg_locks.relation = pg_class.oid AND pg_locks.database = pg_database.oid; pg_class 3389057 | 26130 | AccessShareLock | t pg_class_oid_index 3389057 | 26130 | AccessShareLock | t pg_locks 3389057 | 26130 | AccessShareLock | t cgreylist 3368984 | 10979 | ShareUpdateExclusiveLock | t cgreylist_pkey 3368984 | 10979 | ShareUpdateExclusiveLock | t cgreylist_extime_index 3368984 | 10979 | ShareUpdateExclusiveLock | t cgreylist_instance_sender_index 3368984 | 10979 | ShareUpdateExclusiveLock | t cgreylist_mtime_index 3368984 | 10979 | ShareUpdateExclusiveLock | t cgreylist 3368998 | 10980 | ExclusiveLock | f cgreylist 3369000 | 10984 | AccessShareLock | t cgreylist 3369000 | 10984 | RowExclusiveLock | f # WHY? cgreylist_extime_index 3369000 | 10984 | AccessShareLock | t cgreylist 3388458 | 10023 | ExclusiveLock | f ? cgreylist 3388420 | 10021 | ExclusiveLock | f ? Why cant postgres get the RowExclusiveLock in transaction 3369000? I do not see any confliction lock types here - or do i miss something? (we use postgresql-8.1 (8.1.8-1) on debian 4.0) - Dietmar
Dietmar Maurer wrote: > The pg_locks table shows the following: > > > SELECT pg_class.relname AS table, transaction, pid, mode, granted FROM > pg_locks, pg_class, pg_database WHERE pg_locks.relation = pg_class.oid > AND pg_locks.database = pg_database.oid; > > pg_class 3389057 | 26130 | AccessShareLock > | t > pg_class_oid_index 3389057 | 26130 | AccessShareLock > | t > pg_locks 3389057 | 26130 | AccessShareLock > | t > > > cgreylist 3368984 | 10979 | > ShareUpdateExclusiveLock | t > cgreylist_pkey 3368984 | 10979 | > ShareUpdateExclusiveLock | t > cgreylist_extime_index 3368984 | 10979 | > ShareUpdateExclusiveLock | t > cgreylist_instance_sender_index 3368984 | 10979 | > ShareUpdateExclusiveLock | t > cgreylist_mtime_index 3368984 | 10979 | > ShareUpdateExclusiveLock | t > > cgreylist 3368998 | 10980 | ExclusiveLock > | f > > cgreylist 3369000 | 10984 | AccessShareLock > | t > cgreylist 3369000 | 10984 | RowExclusiveLock > | f # WHY? > cgreylist_extime_index 3369000 | 10984 | AccessShareLock > | t > > cgreylist 3388458 | 10023 | ExclusiveLock > | f ? > cgreylist 3388420 | 10021 | ExclusiveLock > | f ? > > > Why cant postgres get the RowExclusiveLock in transaction 3369000? Probably because the ExclusiveLock'ers are waiting in front of RowExclusiveLock. Locks are granted in order. It would help if you didn't mangle the pg_locks output so badly. -- Alvaro Herrera http://www.amazon.com/gp/registry/5ZYLFMCVHXC "You're _really_ hosed if the person doing the hiring doesn't understand relational systems: you end up with a whole raft of programmers, none of whom has had a Date with the clue stick." (Andrew Sullivan)
Dietmar Maurer wrote: > > > > > > Why cant postgres get the RowExclusiveLock in transaction 3369000? > > > > Probably because the ExclusiveLock'ers are waiting in front > > of RowExclusiveLock. Locks are granted in order. > > > > It would help if you didn't mangle the pg_locks output so badly. > > Yes, sorry about that. > > I was able to reproduce the problem, and the problem is that locks are > granted in order (wonder why?). Because doing otherwise would cause starvation for some lockers. > Anyways, i am trying to avoid locks now, by using my own merge > function to avoid update/insert race condition. > > Or what is the suggested way to avoid the update/insert race condition?. What update/insert race condition? Maybe you are talking about the subject of example 37-1 here: http://www.postgresql.org/docs/current/static/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING -- Alvaro Herrera http://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Dietmar Maurer wrote: > > > Anyways, i am trying to avoid locks now, by using my own merge > > > function to avoid update/insert race condition. > > > > > > Or what is the suggested way to avoid the update/insert > > race condition?. > > > > What update/insert race condition? Maybe you are talking > > about the subject of example 37-1 here: > > Yes, i talk about Example 37-1 So are you having problems with it? -- Alvaro Herrera http://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support
> > > > Why cant postgres get the RowExclusiveLock in transaction 3369000? > > Probably because the ExclusiveLock'ers are waiting in front > of RowExclusiveLock. Locks are granted in order. > > It would help if you didn't mangle the pg_locks output so badly. Yes, sorry about that. I was able to reproduce the problem, and the problem is that locks are granted in order (wonder why?). Anyways, i am trying to avoid locks now, by using my own merge function to avoid update/insert race condition. Or what is the suggested way to avoid the update/insert race condition?. - Dietmar
> > Anyways, i am trying to avoid locks now, by using my own merge > > function to avoid update/insert race condition. > > > > Or what is the suggested way to avoid the update/insert > race condition?. > > What update/insert race condition? Maybe you are talking > about the subject of example 37-1 here: Yes, i talk about Example 37-1 - Dietmar