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.
ACCESS SHARE
Acquired by SELECT and ANALYZE commands.
Conflicts with ACCESS EXCLUSIVE locks.
In general, any query that only reads a table and does not modify it acquires this lock mode.
ROW SHARE
Acquired by SELECT FOR SHARE command.
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.
ROW EXCLUSIVE
Acquired by INSERT and COPY commands.
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.
SHARE UPDATE EXCLUSIVE
Acquired by VACUUM and VACUUM FULL.
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 VACUUM runs.
SHARE
Acquired by CREATE INDEX.
Conflicts with ROW EXCLUSIVE, SHARE UPDATE EXCLUSIVE, SHARE ROW EXCLUSIVE, EXCLUSIVE, and ACCESS EXCLUSIVE locks.
Protects a table against concurrent data changes.
SHARE ROW EXCLUSIVE
This lock mode is not automatically acquired by any Greenplum Database command.
Conflicts with ROW EXCLUSIVE, SHARE UPDATE EXCLUSIVE, SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE, and ACCESS EXCLUSIVE locks.
EXCLUSIVE
Acquired by UPDATE, SELECT FOR UPDATE, and DELETE commands 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.
ACCESS EXCLUSIVE
Acquired by the ALTER TABLE, DROP TABLE, TRUNCATE, REINDEX, CLUSTER, and VACUUM FULL commands. Default lock mode for LOCK TABLE statements that do not specify a lock mode. Also briefly acquired by VACUUM (without 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.
For more on locks in Greenplum Database queries, see the LOCK command Reference. See also Tuning SQL Queries.
