Greenplum Command Center displays the locks currently held by queries and queries blocked by locks.
A block occurs when one query needs to acquire a lock that conflicts with a lock held by another query. If a query is blocked for a long period of time, you can investigate the blocking query and, if necessary, cancel one of the queries.
Locks can be acquired using the
LOCK TABLE SQL statement. Some SQL commands acquire locks automatically. Following are descriptions of the lock modes, the Greenplum Database commands that acquire them, and which lock modes conflict with them.
Conflicts with ACCESS EXCLUSIVE locks.
In general, any query that only reads a table and does not modify it acquires this lock mode.
SELECT FOR SHAREcommand.
Conflicts with EXCLUSIVE and ACCESS EXCLUSIVE locks.
A ROW SHARE lock is placed on the specified table and an ACCESS SHARE lock on any other tables referenced in the query.
Conflicts with SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE, and ACCESS EXCLUSIVE locks.
A ROW EXCLUSIVE lock is placed on the specified table and ACCESS SHARE locks are placed on any other referenced tables.
Conflicts with the SHARE UPDATE EXCLUSIVE, SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE, and ACCESS EXCLUSIVE locks.
SHARE UPDATE EXCLUSIVE protects a table against concurrent schema changes and
Conflicts with ROW EXCLUSIVE, SHARE UPDATE EXCLUSIVE, SHARE ROW EXCLUSIVE, EXCLUSIVE, and ACCESS EXCLUSIVE locks.
Protects a table against concurrent data changes.
Conflicts with ROW EXCLUSIVE, SHARE UPDATE EXCLUSIVE, SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE, and ACCESS EXCLUSIVE locks.
SELECT FOR UPDATE, and
DELETEcommands in Greenplum Database.
Conflicts with ROW SHARE, ROW EXCLUSIVE, SHARE UPDATE EXCLUSIVE, SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE, and ACCESS EXCLUSIVE locks. This lock mode allows only concurrent ACCESS SHARE locks - a table can be read by another transaction while this lock is held. This is more restrictive locking than in regular PostgreSQL.
VACUUM FULLcommands. Default lock mode for
LOCK TABLEstatements that do not specify a lock mode. Also briefly acquired by
FULL) on append-optimized tables during processing.
Conflicts with all locks.
This lock mode guarantees that the holder is the only transaction accessing the table in any way.