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. |
