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