Locks and lock troubleshooting in Hologres - Hologres - Alibaba Cloud Documentation Center

A lock is a semaphore management mechanism used by databases to isolate the executions of SQL statements that belong to different transactions. This topic describes the locks that are used in Hologres and how to troubleshoot lock issues.

Background information

The following figure shows how a query is processed in Hologres. Query链路 The frontend (FE) parses the query, the query engine generates an execution plan, and then the storage engine reads data. In the process, the following locks exist:

FE locks
The FE is the access layer and is compatible with the PostgreSQL protocol. Therefore, FE locks include specific PostgreSQL locks. FE locks are used to manage FE metadata.
Backend (BE) locks
The BE consists of the query engine and fixed plans. BE locks are provided by Hologres. BE locks are used to manage the schemas and data maintained by the storage engine.

Lock behavior changes

In Hologres V2.0 and later, the lock-free mechanism is enabled by default for the FE. If a Data Definition Language (DDL) statement and a Data Query Language (DQL) statement on the same table conflicts, an error message is returned for the new request. For example, if a query request is sent on Table A on which a DDL statement is being executed, an error message is returned for the query request. If you want the system to process the query request after the lock on Table A is released rather than reporting an error message, you can set the GUC parameter to off to disable the lock-free mechanism.
```
ALTER database <db_name> SET hg_experimental_disable_pg_locks = off;
```
In Hologres V2.1 and later, bulkload statements on tables with no primary keys are optimized. After the optimization, bulkload statements on tables with no primary keys acquire only row-level locks.

Introduction to locks

FE locks

The FE of Hologres is compatible with PostgreSQL. Therefore, FE locks are compatible with PostgreSQL. PostgreSQL provides the following types of locks to allow you to control concurrent access: table-level locks, row-level locks, and advisory locks. Hologres is compatible with table-level locks and advisory locks provided by PostgreSQL.

Note

Hologres does not support explicit locking statements or user-defined functions (UDFs) that are related to advisory locks.

Table-level locks

Categories

The following table describes table-level locks.

Name	Description	Remarks
ACCESS SHARE	In most cases, the ACCESS SHARE lock on a table is acquired only by `SELECT` statements that are used to query the table.	N/A.
ROW SHARE	The ROW SHARE lock on a table is acquired only by the `SELECT FOR UPDATE` and `SELECT FOR SHARE` statements that are used to query the table. For other relevant tables, such as the tables that are combined with the queried table by using JOIN, these statements acquire only the ACCESS SHARE lock.	Hologres does not support the `SELECT FOR UPDATE` or `SELECT FOR SHARE` statement. Therefore, you do not need to take note of this lock.
ROW EXCLUSIVE	The ROW EXCLUSIVE lock on a table is acquired by the `UPDATE`, `DELETE`, and `INSERT` statements that are executed to modify data in the table.	You also need to take note of BE locks when you manage the ROW EXCLUSIVE lock.
SHARE UPDATE EXCLUSIVE	The SHARE UPDATE EXCLUSIVE lock is used to protect tables from concurrent schema changes and vacuum runs. The SHARE UPDATE EXCLUSIVE lock is acquired by the following statements: `lazy VACUUM`, rather than `VACUUM Full`. `ANALYZE`. `CREATE INDEX CONCURRENTLY`. Note The SHARE UPDATE EXCLUSIVE lock is not acquired when you execute this statement in Hologres. Instead, this statement acquires the `SHARE` lock in the same way as the `CREATE INDEX` statement that is executed in non-concurrent mode. `CREATE STATISTICS`: Hologres does not support this statement. `COMMENT ON`. `ALTER TABLE VALIDATE CONSTRAINT`: Hologres does not support this statement. `ALTER TABLE SET/RESET (storage_parameter)`: Hologres allows you to execute this statement to configure only extended attributes and the PostgreSQL native attribute autovacuum_enabled. This statement that is executed to configure the preceding attributes does not acquire any locks on tables. If you execute statements to modify specific built-in storage parameters of PostgreSQL, this lock is acquired. For more information, see ALTER TABLE. `ALTER TABLE ALTER COLUMN SET/RESET options`. `ALTER TABLE SET STATISTICS`: Hologres does not support this statement. `ALTER TABLE CLUSTER ON`: Hologres does not support this statement. `ALTER TABLE SET WITHOUT CLUSTER`: Hologres does not support this statement.	Take note of the `ANALYZE` statement.
SHARE	Only the `CREATE INDEX` statement that is executed in non-concurrent mode acquires the SHARE lock. Note The SHARE lock is required when you create JSON indexes in Hologres.	Take note of the `CREATE INDEX` statement that is used to create JSON indexes.
SHARE ROW EXCLUSIVE	The SHARE ROW EXCLUSIVE lock is used to protect a table against concurrent data changes. The SHARE ROW EXCLUSIVE lock is acquired by the following statements: `CREATE COLLATION`: Hologres does not support this statement. `CREATE TRIGGER`: Hologres does not support this statement. Specific `ALTER TABLE` statements. `DISABLE/ENABLE [ REPLICA \| ALWAYS ] TRIGGER`: Hologres does not support this statement. `ADD table_constraint`: Hologres does not support this statement.	The statements that acquire the SHARE ROW EXCLUSIVE lock are not supported by Hologres. Therefore, you do not need to take note of this lock.
EXCLUSIVE	The EXCLUSIVE lock is acquired only by the `REFRESH MATERIALIZED VIEW CONCURRENTLY` statement.	Hologres does not support the `REFRESH MATERIALIZED VIEW CONCURRENTLY` statement. Therefore, you do not need to take note of this lock.
ACCESS EXCLUSIVE	The ACCESS EXCLUSIVE lock is used to ensure exclusive access. The lock conflicts with all the other locks. The ACCESS EXCLUSIVE lock is acquired by the following statements: `DROP TABLE` `TRUNCATE TABLE` `REINDEX`: Hologres does not support this statement. `CLUSTER`: Hologres does not support this statement. `VACUUM FULL` `REFRESH MATERIALIZED VIEW (without CONCURRENTLY)`: Hologres does not support this statement. `LOCK`: an explicit locking statement. If you do not specify a lock type, the ACCESS EXCLUSIVE lock is acquired. Hologres does not support this statement. `ALTER TABLE`: By default, all `ALTER TABLE` statements acquire the ACCESS EXCLUSIVE lock, except for the preceding `ALTER TABLE` statements that acquire specific locks.	You must take note of the ACCESS EXCLUSIVE lock. This lock is acquired by all DDL statements in Hologres and conflicts with all the other locks.

Timeout
By default, FE locks do not time out. If you want to specify a timeout period for FE locks to control the wait time, see Manage queries.

Lock conflicts

The following table describes the conflicts between different locks. If two locks conflict with each other, and an operation acquires one of the locks on a resource, other operations that acquire the other lock on the same resource must wait until the current lock is released.

Note

已开通 indicates that the two locks do not conflict with each other and 未开通 indicates that the two locks conflict with each other.

Requested lock mode	ACCESS SHARE	ROW SHARE	ROW EXCLUSIVE	SHARE UPDATE EXCLUSIVE	SHARE	SHARE ROW EXCLUSIVE	EXCLUSIVE	ACCESS EXCLUSIVE
ACCESS SHARE
ROW SHARE
ROW EXCLUSIVE
SHARE UPDATE EXCLUSIVE
SHARE
SHARE ROW EXCLUSIVE
EXCLUSIVE
ACCESS EXCLUSIVE

Advisory lock
PostgreSQL provides a method to create advisory locks that are defined by applications. In most cases, you do not need to pay attention to advisory locks when you use Hologres.

BE locks

Scopes of locks

The scope of a lock varies based on the type of the lock.

FE locks
FE locks take effect only on table objects. FE locks do not control access to the data stored in tables. FE locks can be in the Successful or Stuck state. When an FE lock is in the Stuck state, the FE lock conflicts with another lock.
BE locks
BE locks take effect on data and table schemas. BE locks are classified into table data locks, row data locks, and table schema locks based on the scopes.
- Table data locks: A table data lock takes effect on all data in a table. If multiple tasks need to acquire a table data lock for a table, only one task can acquire a table data lock on the table each time. The other tasks must wait until the current lock is released. As a result, the executions of these tasks are delayed.
- Row data locks: A row data lock takes effect on all data in a row. The efficiency in statement executions is higher when row data locks are used. The queries that are accelerated by using fixed plans acquire row data locks or table schema locks. For more information about the queries, see Accelerate the execution of SQL statements by using fixed plans.
- Table schema locks: A transaction requests a table schema lock when the transaction needs to read or modify the schema of a table. Most transactions acquire table schema locks. Table schema locks are classified into the following categories:
  - SchX: The SchX lock takes effect on DDL statements. Only the DROP TABLE statement acquires the SchX lock.
  - SchU: The SchU lock is acquired by DDL statements that modify table schemas, such as ALTER TABLE and set_table_property.
  - SchE: The SchE lock is acquired by DML and DQL statements to protect tables against deletion when the statements read data from or write data to the tables.
  Note
  The SchU lock controls DDL statements in a more fine-grained manner. The SchU lock allows the system to execute DQL statements with the ALTER TABLE statement in parallel without waiting. The SchX lock controls DDL statements in the most coarse-grained manner. All the other DDL, DML, and DQL statements need to wait when the system executes a DDL statement that acquires a SchX lock.
  If the execution of the Start Query statement requires a long period of time, the statement may be waiting for the BE lock to be released.

The following table describes the locks acquired by statements that are commonly used in Hologres. 已开通 indicates that the statement acquires the lock.

Note

All insert, update, and delete operations performed by using statements other than fixed plans are bulkloads.
The CREATE INDEX statement is used to create JSON indexes.
DDL statements include CREATE, DROP, and ALTER.

Statement/Lock scope	Table-level lock	Table data lock	Row data lock	Table schema lock
CREATE		N/A.
DROP	Note After the DROP statement acquires a lock, the system cannot execute other statements before the system drops the table. As a result, the executions of other statements fail because the table is dropped. Note:	N/A.	N/A.	Note Conflicts with other statements.
ALTER	Note Same as the DROP statement.	N/A.	N/A.	Note When the system executes an ALTER statement that acquires a table schema lock, the system can execute `SELECT` statements for the same table in parallel.
SELECT	Note When the system executes a SELECT statement that acquires a table-level lock, the system can execute the `INSERT`, `UPDATE`, and `DELETE` statements for the same table in parallel. The SELECT statement conflicts only with DDL statements.	N/A.	N/A.	Note When the system executes a `SELECT` statement that acquires a table schema lock, the system can execute all statements in parallel except for the `DROP` statement.
INSERT (including INSERT ON CONFLICT)	Note The `INSERT` statement conflicts with the `CREATE INDEX` statement and DDL statements.	In Hologres V2.0 and earlier, bulkload statements acquire table data locks. In Hologres V2.1 and later, only bulkload statements that are executed on tables with primary keys acquire table data locks. Note Bulkload statements and fixed plans are mutually exclusive.	A statement acquires a row data lock if the statement is executed by using fixed plans. In Hologres V2.1 and later, bulkload statements that are executed on tables with no primary keys acquire only row data locks. Note In Hologres V2.0 and earlier, real-time data write by using fixed plans and offline data write cannot be performed at the same time. In Hologres V2.1 and later, real-time data write by using fixed plans and offline data write can be performed at the same time on tables with no primary keys.	Note Conflicts with DDL and DML statements.
UPDATE	Note Conflicts with `CREATE INDEX` and DDL statements.			Note Conflicts with DDL and DML statements.
DELETE	Note Conflicts with `CREATE INDEX` and DDL statements.			Note Conflicts with DDL and DML statements.

Locks related to transactions

Hologres supports only explicit transactions that use DDL statements. Hologres does not support transactions that use only DML statements or transactions that use DDL and DML statements at the same time.

Hologres does not support nested subtransactions.
Although Hologres is compatible with the syntax of transactions that use only DML statements, Hologres does not support atomic commits or rollbacks of the transactions.
For example, in the following DML statements, if the INSERT operation succeeds, but the UPDATE operation fails, the inserted data is not rolled back.
```
begin;
insert into t1(id, salary) values (1, 0);
update t1 set salary = 0.1 where id = 1;
commit;
```
Transactions that use only DDL statements can be processed as expected.
If one of the DDL statements fails, the entire transaction is rolled back. For example, if the following ALTER operation fails, the CREATE and DROP operations are rolled back.
```
begin;
create table t1(i int);
drop table if exists t2;
alter table t1 add column n text;
commit;
```
Transactions that use both DDL and DML statements are prohibited.
For example, the following transaction uses a DDL statement and a DML statement. The system returns an error when the system executes the DML statement.
```
begin;
create table t1(i int);
update t1 set i = 1 where i = 1;
-- DML statement error
ERROR:  UPDATE in ddl transaction is not supported now.
```
The locks acquired by statements in an explicit transaction are released only after the entire transaction is terminated. A transaction is terminated if the transaction is committed or rolled back.
For example, when the following transaction performs the ALTER operation on the parent table, an ACCESS EXCLUSIVE lock is acquired on the login_history parent table and the login_history_202001 child table. The lock is not released after the ALTER operation is complete. The lock is released only after the commit operation is complete regardless of whether the commit operation is successful or not. The tables are locked if the system does not perform the commit operation. In this case, the system returns errors if DDL statements are submitted for the locked tables.
```
 -- suppose we have three tables
create table temp1(i int, t text);
create table login_history(ds text, user_id bigint, ts timestamptz) partition by list (ds);
create table login_history_202001 partition of login_history for values in ('202001');

begin;
alter table login_history_s1 add column user_id bigint;
drop table temp1;
create table tx2(i int);
commit;
```

Check FE locks

This section describes how to check whether an FE lock exists and how to troubleshoot FE lock issues.

If a query consumes a long period of time, check whether the query statement acquires an FE lock by checking the value of the wait_event_type field.

In the following example, the value of the wait_event_type field in the returned result is lock, which indicates that the query statement acquires an FE lock.

-- Hologres V2.0 and later:
select query,state,query_id,transaction_id,pid,wait_event_type,wait_event,running_info,extend_info FROM hg_stat_activity where query_id = 200640xxxx;
-- The following result is returned:
----------------+----------------------------------------------------------------
query           | drop table test_order_table1;
state           | active
query_id        | 200640xxxx
pid             | 123xx
transaction_id  | 200640xxxx
wait_event_type | Lock
wait_event      | relation
running_info    | {"current_stage":{"stage_duration_ms":47383,"stage_name":"PARSING"},"fe_id":1,"warehouse_id":0}+
                |
extend_info     | {}                                                                                                                                           +

-- Hologres V1.3 and earlier:
select datname, pid, application_name, wait_event_type, state,query_start, query from pg_stat_activity where backend_type in ('client backend');
-- The following result is returned:
----------------+----------------------------------------------------------------
datname           | holo_poc
pid               | 321xxx
application_name  | PostgreSQL JDBC Driver
wait_event_type   | lock
state             | active
query_start       |2023-04-20 14:31:46.989+08
query             | delete from xxx

View the owner of the FE lock.
Execute the following statement to view the owner of the FE lock:
```
select * from pg_locks where pid = <pid>;
```
In the preceding statement, set pid to the pid value returned in Step 1.
Check the process that holds the FE lock.
The returned result of Step 2 indicates that the SQL statement acquires the FE lock. Execute the following statement to check the process that holds the FE lock. In this statement, set relation to the object ID (OID) that indicates the table relationship. Set granted to t, indicating that the FE lock is being held.
```
-- Query the process that holds the table-level lock.
select pid from pg_locks where relation = <OID> and granted = 't';
```
Check the query that holds the FE lock.
Execute the following statement to check the query that holds the FE lock. In this statement, set pid to the pid value obtained in Step 3.
```
select * from pg_stat_activity where pid = <PID>;
```
Release the FE lock.
After you identify the query that holds the FE lock, execute the following statement to terminate the query. This way, the FE lock is released.
```
select pg_cancel_backend(<pid>);
```

Check BE locks

If the be_lock_waiters and be_lock_converting_waiters fields of the hg_stat_activity view are not empty, the query acquires a BE lock or is waiting for a BE lock to be released at the backend. This section describes how to check BE locks.

Scenario 1: The current query generates a BE lock, and other queries are waiting for the BE lock to be released.

You can execute the following statement to check whether the current SQL statement acquires a BE lock and check the queries that are waiting for the BE lock to be released.

select query_id, transaction_id, ((extend_info::json)->'be_lock_waiters'->>0)::text as be_lock_waiters 
FROM hg_stat_activity as h where h.state = 'active' and ((extend_info::json)->'be_lock_waiters')::text != '';

-- The following result is returned:
----------------+------------------
query_id        | 10005xxx
transaction_id  | 10005xxx
be_lock_waiters | 13235xxx

Scenario 2: The current query is waiting for a BE lock to be released by another query.

You can execute the following statement to obtain the query that holds BE lock. The current query is waiting for the BE lock to be released.

select query_id, transaction_id, ((extend_info::json)->'be_lock_waiters')::text as be_lock_waiters 
FROM hg_stat_activity as h where h.state = 'active' and ((extend_info::jsonb)->'be_lock_waiters')::jsonb ? '10005xxx';

-[ RECORD 1 ]---+------------------------------------------
query_id        | 200740017664xxxx
transaction_id  | 200740017664xxxx
be_lock_waiters | ["200640051468xxxx","200540035746xxxx"]

FAQ

What do I do if the internal error: Cannot acquire lock in time, current owners: [(Transaction =302xxxx, Lock Mode = SchS|SchE|X)]. error message is reported?
- Possible cause: The table from which you want to query data is locked by another query with a BE lock. In this example, the Lock Mode = SchS|SchE|X error message is returned, indicating that the queried table is locked with an SchS lock, SchE lock, or SchX lock. As a result, the current query needs to wait the BE lock to be released and times out. The timeout period is 5 minutes.
- Solution: Use the query ID to locate the query that holds the BE lock in the slow query log or in active queries. The query ID is the transaction ID that is specified in Transaction =302xxxx.
What do I do if the ERROR: Operation timed out: Wait schema version timeout.: Server current target schema version:xxx is late from request schema version: yyy error message is reported?
- Possible cause: A DDL statement is executed on an FE node and then asynchronously executed by the storage engine. After the FE node completes the DDL statement, the FE node updates the node version. In this case, if the storage engine is still executing the DDL statement, the version of the storage engine is earlier than that of the FE node. As a result, the query must wait for the storage engine to complete the DDL statement. If the storage engine fails to complete the DDL statement within 5 minutes, this error is returned.
- Solution:
  - Terminate the DDL statement that is waiting for the lock to be released and submit the query again.
  - Restart the instance. This is an extreme approach.
What do I do if the The requested table name: xxx (id: 10, version: 26) mismatches the version of the table (id: 10, version: 28) from server error message is reported?
- Possible cause: A DDL statement is executed on an FE node and then asynchronously executed by the storage engine. The storage engine has completed the DDL statement and updated the version. However, the number of FE nodes is large, and replay is still in progress. As a result, the versions of some FE nodes are earlier than the version of the storage engine. If the query is processed by one of these nodes, this error is returned.
- Solution:
  - Resubmit the query multiple times.
  - If the system still returns an error after a few minutes, restart the instance.
What do I do if the internal error: Cannot find index full ID: 86xxx (table id: 20yy, index id: 1) in storages or it is deleting! error message is reported?
- Possible cause: When the system performs the DROP Table or Truncate Table operation on a table, a DDL lock is acquired on the table. In this case, DML statements, such as the SELECT and DELETE statements, need to wait until the DDL lock is released. After the DDL lock is released, the table is dropped. As a result, this error message is returned.
- Solution: Do not submit other queries for a table when you perform the DROP or TRUNCATE operation on the table.

Category	Description
Exclusive(X)	The exclusive lock is requested when a transaction modifies one or more data entries. For example, Data Manipulation Language (DML) statements, such as the DELETE, INSERT, and UPDATE statements, request the exclusive lock. An exclusive lock on a resource can be successfully requested only if no exclusive or share lock is held on the resource. After an exclusive lock on a resource is successfully requested, other locks cannot be requested on the resource.
Shared(S)	The share lock is requested when a transaction reads one or more data entries. The share lock protects the data entries to be read against data changes that are committed by other transactions. A resource supports multiple share locks. This way, multiple Data Query Language (DQL) statements can be concurrently executed on the same resource because DQL statements do not change resources.
Intent(I)	The intent lock is used to indicate the lock hierarchy. A resource supports multiple intent locks. After an intent lock on a resource is successfully requested, no exclusive lock can be requested on the resource. For example, when a transaction requests an exclusive lock on a row, the transaction also requests an intent lock on the table that contains the row. Tables are in the higher hierarchy than rows. This way, other transactions cannot request an exclusive lock on the table.

Operation	DROP	ALTER	SELECT	UPDATE	DETELE	INSERT (including INSERT ON CONFLICT)
DROP
ALTER
SELECT
UPDATE
DETELE
INSERT (including INSERT ON CONFLICT)