Concurrency Control in Greenplum Database

Unlike traditional database systems which use locks for concurrency control, Greenplum Database (as does PostgreSQL) maintains data consistency by using a multiversion model (Multiversion Concurrency Control, MVCC). This means that while querying a database, each transaction sees a snapshot of data which protects the transaction from viewing inconsistent data that could be caused by (other) concurrent updates on the same data rows. This provides transaction isolation for each database session.

MVCC, by eschewing explicit locking methodologies of traditional database systems, minimizes lock contention in order to allow for reasonable performance in multiuser environments. The main advantage to using the MVCC model of concurrency control rather than locking is that in MVCC locks acquired for querying (reading) data do not conflict with locks acquired for writing data, and so reading never blocks writing and writing never blocks reading.

Greenplum Database provides various lock modes to control concurrent access to data in tables. Most Greenplum Database SQL commands automatically acquire locks of appropriate modes to ensure that referenced tables are not dropped or modified in incompatible ways while the command executes. For applications that cannot adapt easily to MVCC behavior, the LOCK command can be used to acquire explicit locks. However, proper use of MVCC will generally provide better performance than locks.

Lock Modes in Greenplum Database           
Lock Mode 
Associated SQL Commands                    
Conflicts With
ACCESS SHARE                                          
SELECT                                                                     
ACCESS EXCLUSIVE
ROW SHARE                                                
SELECT FOR UPDATE, SELECT FOR SHARE            
EXCLUSIVE, ACCESS EXCLUSIVE
ROW EXCLUSIVE                                          
INSERT, COPY                                                         
HARE, SHARE ROW EXCLUSIVE, EXCLUSIVE, ACCESS 
                                                                                                                                                     EXCLUSIVE
SHARE UPDATE EXCLUSIVE                        
VACUUM (without FULL), ANALYZE                            
SHARE UPDATE EXCLUSIVE, SHARE, SHARE ROW 
                                                                                                                                                     EXCLUSIVE, EXCLUSIVE, ACCESS EXCLUSIVE
SHARE                                                       
CREATE INDEX                                                          
ROW EXCLUSIVE, SHARE UPDATE EXCLUSIVE, SHARE 
                                                                                                                                                     ROW EXCLUSIVE, EXCLUSIVE, ACCESS EXCLUSIVE
SHARE ROW EXCLUSIVE                                                                                                             
ROW EXCLUSIVE, SHARE UPDATE EXCLUSIVE, SHARE, 
                                                                                                                                                    SHARE ROW EXCLUSIVE, EXCLUSIVE, ACCESS EXCLUSIVE
EXCLUSIVE                                                 
DELETE, UPDATE1                                                   
ROW SHARE, ROW EXCLUSIVE, SHARE UPDATE 
                                                                                                                                                   EXCLUSIVE, SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE, 
                                                                                                                                                   ACCESS EXCLUSIVE
ACCESS EXCLUSIVE                                  
ALTER TABLE, DROP TABLE, TRUNCATE,            
ACCESS SHARE, ROW SHARE, ROW EXCLUSIVE, SHARE 
                                                                   REINDEX, CLUSTER, VACUUM FULL                     
UPDATE EXCLUSIVE, SHARE, SHARE ROW EXCLUSIVE, 
                                                                                                                                               EXCLUSIVE, ACCESS EXCLUSIVE

Comments