Lock table and unlock table-Dataphin(Dataphin)-阿里云帮助中心

Dataphin supports locking and unlocking physical tables. For example, if an INSERT statement fails and leaves a table or partition locked, you can use the UNLOCK TABLE syntax to release it. This topic describes the syntax for LOCK TABLE, UNLOCK TABLE, and SHOW LOCKS.

Locking tables

Prerequisites
- Only physical tables can be locked.
- You must have write permissions on the physical table.
  
  For more information, see request, renew, and return table permissions.

Syntax

lock table <table_name> [partition (<partition_spec>)] {shared|exclusive};

Parameter explanation
- table_name: The name of the table to lock.
- partition_spec: The partition to lock.
- shared|exclusive: The lock type. Valid values: shared and exclusive.

Examples

Locking a table
```
lock table some_table exclusive;
```

Locking a partition of a table

lock table some_partitioned_table partition (ds = '20211130') exclusive;

Querying locked tables

Syntax
```
show locks <table_name>;
```
Parameter explanation

table_name: Required. The name of the locked table.
Examples
```
show locks some_table;
```

Unlocking tables

Prerequisites
- Only physical tables can be locked.
- You must have write permissions on the physical table.
  
  For more information, see request, renew, and return table permissions.

Syntax

unlock table <table_name> [partition (<partition_spec>)];

Parameter explanation
- table_name: The name of the table to unlock.
- partition_spec: The partition to unlock.

Examples

Unlocking a table
```
unlock table some_table;
```

Unlocking a partition of a table

unlock table some_partitioned_table partition (ds = '20211130');