Control Transactional Concurrency

This document introduces the transactional concurrency control in SynxDB Cloud, including:

MVCC mechanism

SynxDB Cloud and PostgreSQL do not use locks for concurrency control. Instead, they maintain data consistency through a multi-version model known as Multi-version Concurrency Control (MVCC). MVCC ensures transaction isolation for each database session, allowing each query transaction to see a consistent snapshot of data. This ensures that the data observed by a transaction remains consistent and unaffected by other concurrent transactions.

However, the specific data changes visible to a transaction are influenced by its isolation level. The default isolation level is “READ COMMITTED,” which means that a transaction can observe data changes made by other transactions that have already been committed. If the isolation level is set to “REPEATABLE READ,” then queries within that transaction will observe the data because it was at the beginning of the transaction and will not see changes made by other transactions in the interim. To specify the isolation level of a transaction, you can use the statement BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ to start a transaction with the “REPEATABLE READ” isolation level.

Because MVCC does not use explicit locks for concurrency control, lock contention is minimized and SynxDB Cloud maintains reasonable performance in multi-user environments. Locks acquired for querying (reading) data do not conflict with locks acquired for writing data.

Lock modes

SynxDB Cloud provides multiple lock modes to control concurrent access to data in tables. Most SynxDB Cloud SQL commands automatically acquire the appropriate locks to ensure that referenced tables are not dropped or modified in incompatible ways while a command runs. For applications that cannot adapt easily to MVCC behavior, you can use the LOCK command to acquire explicit locks. However, proper use of MVCC generally provides better performance.

Lock mode

SQL commands

Conflicting lock modes

ACCESS SHARE

SELECT

ACCESS EXCLUSIVE

ROW SHARE

SELECT...FOR lock_strength

EXCLUSIVE, ACCESS EXCLUSIVE

ROW EXCLUSIVE

INSERT, COPY

SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE, ACCESS EXCLUSIVE

SHARE UPDATE EXCLUSIVE

ANALYZE

SHARE UPDATE EXCLUSIVE, SHARE, EXCLUSIVE, ACCESS EXCLUSIVE

SHARE

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, UPDATE, SELECT...FOR lock_strength, REFRESH MATERIALIZED VIEW CONCURRENTLY

ROW SHARE, ROW EXCLUSIVE, SHARE UPDATE EXCLUSIVE, SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE, ACCESS EXCLUSIVE

ACCESS EXCLUSIVE

ALTER TABLE, DROP TABLE, TRUNCATE, CLUSTER, REFRESH MATERIALIZED VIEW（without CONCURRENTLY）, VACUUM FULL

ACCESS SHARE, ROW SHARE, ROW EXCLUSIVE, SHARE, SHARE UPDATE EXCLUSIVE, SHARE ROW EXCLUSIVE, EXCLUSIVE, ACCESS EXCLUSIVE