REMOVE LOCK

The REMOVE LOCK utility function clears orphaned locks that block file cleanup and prevent operations on locked objects within the system.

To remove all existing locks, see REMOVE STATEMENT LOCKS

Read more about locks in Concurrency and Locks.

Syntax

SELECT REMOVE_LOCK(<locked_object>, <stmt_id> [, <ignore_stmt_exists> ])

Example

  1. Get locked object names:

    SELECT SHOW_LOCKS();

    Output:

    statement id |statement string                  |username |server       |port |locked object             |lock mode |statement start time |lock start time     |is_statement_active |is_snapshot_active
-------------+----------------------------------+---------+-------------+-----+--------------------------+----------+---------------------+--------------------+--------------------+------------------
0            |COPY schema.table FROM WRAPPER .. |sqream   |192.168.4.35 |5000 |database$master           |Inclusive |29-10-2023 14:20:08  |2023-10-29 14:20:08 |1                   |1
0            |COPY schema.table FROM WRAPPER .. |sqream   |192.168.4.35 |5000 |schema$master$schema      |Inclusive |29-10-2023 14:20:08  |2023-10-29 14:20:08 |1                   |1
0            |COPY schema.table FROM WRAPPER .. |sqream   |192.168.4.35 |5000 |table$master$schema$table |Inclusive |29-10-2023 14:20:08  |2023-10-29 14:20:08 |1                   |1

  2. Show server status:

    SELECT SHOW_SERVER_STATUS();

    Output:

    service |instanceid |connection_id |serverip     |serverport |database_name |user_name |clientip     |statementid |statement                                                                                                                      |statementstarttime  |statementstatus |statementstatusstart
--------+-----------+--------------+-------------+-----------+--------------+----------+-------------+------------+-------------------------------------------------------------------------------------------------------------------------------+--------------------+----------------+--------------------
sqream  |node_9383  |1             |192.168.4.35 |5000       |master        |sqream    |192.168.4.35 |0           |COPY schema.table FROM WRAPPER parquet_fdw OPTIONS (location='/abc/*.c000', CONTINUE_ON_ERROR=true, ERROR_LOG='/abc/log_out'); |29-10-2023 14:20:08 |Executing       |29-10-2023 14:20:08

  3. Remove a specific lock:

    SELECT REMOVE_LOCK ('database$master', 0);

    SELECT REMOVE_LOCK ('schema$master$schema', 0);

    SELECT REMOVE_LOCK ('table$master$schema$table', 0);

Permissions

This utility function requires a SUPERUSER permission on the database level.