Higher chance of deadlock due to ANALYZE VERBOSE / SHARE UPDATE EXCLUSIVE? - Mailing list pgsql-general
| From | Dean Toader |
|---|---|
| Subject | Higher chance of deadlock due to ANALYZE VERBOSE / SHARE UPDATE EXCLUSIVE? |
| Date | |
| Msg-id | D2261AD0-C190-43F3-AF40-50629987D582@mirthcorp.com Whole thread Raw |
| Responses |
Re: Higher chance of deadlock due to ANALYZE VERBOSE / SHARE UPDATE EXCLUSIVE?
|
| List | pgsql-general |
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.
pgsql-general by date: