Lock a table

Table locking is the most basic locking strategy. After a table is locked, only one session is allowed to perform read and write operations on the table at any time.

You can use the LOCK TABLES statement to lock a table.

Background

A database is a shared resource that can be simultaneously accessed by multiple users. When multiple users concurrently access the database, it is likely that the same data is operated by multiple transactions at the same time. Uncontrolled concurrent operations on the database may result in data inconsistency. Locking is key to the control of database concurrency. The locking mechanism is introduced to ensure resource security (or data integrity and consistency) by controlling concurrent data operations of multiple transactions.

Prerequisites

You can lock only tables in your own schema, or you must have the LOCK ANY TABLE privilege.

Example

Lock the tbl1 table in EXCLUSIVE mode.

```sql
obclient> LOCK TABLE tbl1 IN EXCLUSIVE MODE NOWAIT;
Query OK, 0 row affected
```

OceanBase Database supports the following locking modes:

• ROW SHARE: allows concurrent access to the locked table but prohibits other users from locking the table for exclusive access. This means that other users are not allowed to lock the table in EXCLUSIVE mode.
• ROW EXCLUSIVE: prohibits users from locking the table in SHARE or higher (SHARE, ROW SHARE EXCLUSIVE, or EXCLUSIVE) mode. When UPDATE, INSERT, DELETE, or SELECT FOR UPDATE operations are performed on a table, the table is automatically locked in ROW EXCLUSIVE mode.
• SHARE: allows concurrent queries but prohibits updates on the locked table. UPDATE, DELETE, INSERT, or SELECT FOR UPDATE operations on table rows are not allowed, and other users cannot lock the table in SHARE ROW EXCLUSIVE or EXCLUSIVE mode.
• SHARE ROW EXCLUSIVE: allows other users to view rows of the locked table but prohibits the users from performing the UPDATE, DELETE, or INSERT operation on table rows, using SELECT FOR UPDATE to query table rows, or locking the table in any mode other than ROW SHARE.
• EXCLUSIVE: allows other users to query the locked table but prohibits the users from performing any DML operations on the table or locking the table in any mode.

The following table describes the conflicts between different locking modes.

For more information about the LOCK TABLES statement, see LOCK TABLES.