Обсуждение: Higher chance of deadlock due to ANALYZE VERBOSE / SHARE UPDATE EXCLUSIVE?
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?
Dean Toader
CONFIDENTIALITY NOTICE: The information contained in this electronic transmission may be confidential. If you are not an intended recipient, be aware that any disclosure, copying, distribution or use of the information contained in this transmission is prohibited and may be unlawful. If you have received this transmission in error, please notify us by email reply and then erase it from your computer system.
Dean Toader <deant@mirthcorp.com> writes:
> 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