Higher chance of deadlock due to ANALYZE VERBOSE / SHARE UPDATE EXCLUSIVE?

[Dean Toader]

I’ve got a question on postgresql locking:

I managed to get a SHARE UPDATE EXCLUSIVE while running the following
/opt/pgsql/bin/vacuumdb -a --analyze-only --verbose -U postgres

While the above is running, I started another session and connected to the database
getting analyzed in the first terminal and ran the following query on and noticed the following

[06:05 PM|TEST|TEST-tstclone|~]# /opt/pgsql/bin/psql -U postgres mydb
psql (9.0.13)
Type "help" for help.

mirthmatch=# SELECT a.datname,
c.relname,
l.transactionid,
l.mode,
l.granted,
a.usename,
a.current_query,
a.query_start,
age(now(), a.query_start) AS "age",
a.procpid
FROM pg_stat_activity a
JOIN pg_locks l ON l.pid = a.procpid
JOIN pg_class c ON c.oid = l.relation
ORDER BY a.query_start;

datname | relname | transactionid | mode | granted | usename | current_query |
query_start | age | procpid
------------+-------------------------------+---------------+--------------------------+---------+----------+----------------------------------------------------+
-------------------------------+-----------------+---------
mydb | trait_inst_hist_trait_vers_fk | | AccessShareLock | t | postgres | ANALYZE VERBOSE; +|
2014-09-22 17:54:51.924328-04 | 00:11:01.319604 | 9979
| | | | | | |
| |
mydb | eis_trait_instance_history_pk | | AccessShareLock | t | postgres | ANALYZE VERBOSE; +|
2014-09-22 17:54:51.924328-04 | 00:11:01.319604 | 9979
| | | | | | |
| |
mydb | eis_trait_instance_history | | ShareUpdateExclusiveLock | t | postgres | ANALYZE VERBOSE; +|
2014-09-22 17:54:51.924328-04 | 00:11:01.319604 | 9979
| | | | | | |
| |
This is unexpected to me because looking at the chart at http://www.postgresql.org/docs/9.0/static/explicit-locking.html
I see that SHARE UPDATE EXCLUSIVE conflicts with SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE and
ACCESS EXCLUSIVE so that means that an ANALYZE VERBOSE on a large table that is running concurrently
with another PID having any of the conflicting lock modes (SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE and
ACCESS EXCLUSIVE) on the same large table may lead to a LOCK situation.

What I observed on a deadlocked system was a SHARE UPDATE EXCLUSIVE lock due to an ANALYZE VERBOSE initiated by
/opt/pgsql/bin/vacuumdb -a --analyze-only --verbose -U postgres
was LOCKing a VACUUM operation (SHARE UPDATE EXCLUSIVE) initiated by the autovacuum process on a very large table.

My conclusion in this case is that a user or cronjob-initiated
/opt/pgsql/bin/vacuumdb -a --analyze-only --verbose -U postgres
should really not happen while the autovacuum is active since this is a
scenario where locking (and deadlock) is possible due to
SHARE UPDATE EXCLUSIVE.

Can anyone see an increased possibility of deadlock occurring with
ANALYZE VERBOSE (initiated by “vacuumdb -a --analyze-only --verbose -U postgres”
command run once every 24 hrs on a cronjob schedule), VACUUM (initiated by autovacuum)
and say ... a long running UPDATE (initiated by a JEE application)
all happening concurrently on the same table?

Thanks in advance,
Dean Toader

[Tom Lane]

Post by Dean Toader
Can anyone see an increased possibility of deadlock occurring with
ANALYZE VERBOSE (initiated by “vacuumdb -a --analyze-only --verbose -U postgres”
command run once every 24 hrs on a cronjob schedule), VACUUM (initiated by autovacuum)
and say ... a long running UPDATE (initiated by a JEE application)
all happening concurrently on the same table?

If an autovacuum is part of a deadlock, the deadlock detector should
preferentially kill the autovacuum transaction. Is that not happening
for you? But in any case, whether that happens or not, SHARE UPDATE
EXCLUSIVE doesn't block UPDATE commands, so that the case you describe
above is not a deadlock. You would have to be doing some kind of DDL
on the table in question to have any risk.

regards, tom lane