Transaction Concurrency Control

This document describes transaction concurrency control in SynxDB.

Multi-version concurrency control

SynxDB and PostgreSQL do not use locking mechanisms for transaction concurrency control, but instead use multi-version concurrency control (MVCC) to maintain data consistency. MVCC ensures transaction isolation for each database session, allowing each query transaction to see a consistent data snapshot. This guarantees that the data observed by a transaction is consistent and unaffected by other concurrent transactions.

However, the specific data changes visible to a transaction are affected by the isolation level. The default isolation level is “read committed” (READ COMMITTED), which means that transactions can observe data changes made by other committed transactions. If the isolation level is set to “repeatable read” (REPEATABLE READ), then queries in that transaction will observe the data state at the start of the transaction and will not see changes made by other transactions during that period. To specify the isolation level of a transaction, you can use the statement BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ to start a transaction with “repeatable read” isolation level.

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

Lock modes

SynxDB provides multiple lock modes to control concurrent access to table data. Most SynxDB SQL commands automatically acquire appropriate locks to ensure that referenced tables are not deleted or modified in incompatible ways during command execution. For applications that have difficulty adapting to MVCC behavior, you can use the LOCK command to acquire explicit locks. However, in most cases, proper use of MVCC provides better performance.

Lock mode	Related 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	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, 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, REINDEX, CLUSTER, REFRESH MATERIALIZED VIEW (without CONCURRENTLY), VACUUM FULL	ACCESS SHARE, ROW SHARE, ROW EXCLUSIVE, SHARE, SHARE UPDATE EXCLUSIVE, SHARE ROW EXCLUSIVE, EXCLUSIVE, ACCESS EXCLUSIVE

Note

The Global Deadlock Detector is disabled by default. SynxDB acquires stricter EXCLUSIVE locks when executing UPDATE and DELETE operations, rather than the ROW EXCLUSIVE locks used in PostgreSQL.

When the Global Deadlock Detector is enabled:

• The lock mode for certain DELETE and UPDATE operations on heap tables will be ROW EXCLUSIVE. See Global Deadlock Detector.

Global deadlock detector

SynxDB’s Global Deadlock Detector background worker process collects lock information from all segments and uses a directed algorithm to detect whether local and global deadlocks exist. This algorithm allows SynxDB to relax restrictions on concurrent update and delete operations on heap tables. Nevertheless, SynxDB still employs table-level locking on AO/CO tables, limiting concurrent UPDATE, DELETE, and SELECT...FOR lock_strength operations.

By default, the Global Deadlock Detector is disabled. In SynxDB, concurrent UPDATE and DELETE operations on heap tables run serially. To activate these concurrent updates and let the Global Deadlock Detector determine when deadlocks exist, you can set the parameter gp_enable_global_deadlock_detector to on in the postgresql.conf configuration file and restart the database.

When the Global Deadlock Detector is enabled, a background worker process automatically starts on the coordinator host. You can configure the interval at which the Global Deadlock Detector collects and analyzes lock wait data through the gp_global_deadlock_detector_period server configuration parameter in the postgresql.conf configuration file.

If the Global Deadlock Detector determines that a deadlock exists, it takes action to break the deadlock by canceling one or more background processes of the most recent related transactions.

When the Global Deadlock Detector finds that the following types of transactions have deadlocks, only one transaction will succeed. Other transactions will fail due to not allowing concurrent updates on the same row and will display corresponding error messages.

• For concurrent transactions on the same row in heap tables, if the first transaction is an update operation and subsequent transactions perform updates or deletes with motion operators in the query plan.

• Concurrent update transactions on the same distribution key in heap tables based on the Postgres optimizer.

• Concurrent update transactions on the same row in hash tables based on the GPORCA optimizer.

Tip

• SynxDB uses the interval specified by the server configuration parameter deadlock_timeout for local deadlock detection. Because the local and global deadlock detection algorithms are different, the processes that get canceled might vary depending on which detector (local or global) triggers first.

• If the lock_timeout server configuration parameter is enabled and set to a value smaller than deadlock_timeout and gp_global_deadlock_detector_period, SynxDB will cancel a statement before triggering deadlock checks in the session.

To view lock wait information for all segments, execute the user-defined function gp_dist_wait_status(). Through the function’s output, you can determine which transactions are waiting for locks, which transactions hold locks, the type and mode of locks, session identifiers of waiters and holders, and which segments are running transactions. The following is an example output of the gp_dist_wait_status() function:

SELECT * FROM pg_catalog.gp_dist_wait_status();

-[ RECORD 1 ]----+--------------
segid            | 0
waiter_dxid      | 11
holder_dxid      | 12
holdTillEndXact  | t
waiter_lpid      | 31249
holder_lpid      | 31458
waiter_lockmode  | ShareLock
waiter_locktype  | transactionid
waiter_sessionid | 8
holder_sessionid | 9
-[ RECORD 2 ]----+--------------
segid            | 1
waiter_dxid      | 12
holder_dxid      | 11
holdTillEndXact  | t
waiter_lpid      | 31467
holder_lpid      | 31250
waiter_lockmode  | ShareLock
waiter_locktype  | transactionid
waiter_sessionid | 9
holder_sessionid | 8

When the Global Deadlock Detector cancels a transaction to break a deadlock, the following error message is reported:

ERROR: canceling statement due to user request: "cancelled by global deadlock detector"

Global deadlock detector management of concurrent UPDATE and DELETE operations

For heap tables, the Global Deadlock Detector can manage the following types of concurrent UPDATE and DELETE operations:

• Simple single-table UPDATE operations. Uses Postgres optimizer-based updates on non-distribution keys. The command does not contain a FROM clause and does not include subqueries in the WHERE clause.

  UPDATE t SET c2 = c2 + 1 WHERE c1 > 10;
  

• Simple single-table DELETE operations. Does not include subqueries in the FROM or WHERE clause.

  DELETE FROM t WHERE c1 > 10;
  

• Split UPDATE operations. If using the Postgres-based optimizer, the UPDATE command updates a distribution key.

  UPDATE t SET c = c + 1;  -- c is a distribution key
  

  If using the GPORCA optimizer, the UPDATE command updates a distribution key or references a distribution key.

  UPDATE t SET b = b + 1 WHERE c = 10; -- c is a distribution key
  

• Complex UPDATE operations, including multi-table join UPDATE commands.

  UPDATE t1 SET c = t1.c+1 FROM t2 WHERE t1.c = t2.c;
  

  Or commands that include subqueries in the WHERE clause.

  UPDATE t SET c = c + 1 WHERE c > ALL(SELECT * FROM t1);
  

• Complex DELETE operations, similar to complex UPDATE operations, involving multi-table joins or subqueries.

  DELETE FROM t USING t1 WHERE t.c > t1.c;
  

The following table lists concurrent UPDATE and DELETE commands managed by the Global Deadlock Detector. Examples of these commands include:

• Concurrent simple UPDATE commands on the same table row, managed by the Global Deadlock Detector.

• Concurrent complex UPDATE and simple UPDATE commands, where only one UPDATE will be executed while the other UPDATE will return an error.

Tip

In the table below, YES indicates concurrent execution is possible, and NO indicates concurrent execution is not possible.

Command	Simple UPDATE	Simple DELETE	Split UPDATE	Complex UPDATE	Complex DELETE
Simple UPDATE	YES	YES	NO	NO	NO
Simple DELETE	YES	YES	NO	YES	YES
Split UPDATE	NO	NO	NO	NO	NO
Complex UPDATE	NO	YES	NO	NO	NO
Complex DELETE	NO	YES	NO	NO	YES