AntDB-Oracle Compatibility Developer’s Manual P3–19
Published: 2023-09-04 05:27:55

#### LOCK

**Name**

LOCK -- lock a table

**Summary**

LOCK TABLE name \[, ...] IN lockmodeMODE \[  \[ WAIT \[, integer] \| NOWAIT ]

lockmode is one of the following five options:

```
| lock\_mode| Description
|----------|----------
| ROW SHARE| Allow simultaneous access to tables, but prevents users from locking the entire table for exclusive access.
| ROW EXCLUSIVE| Allow concurrent access to a table, but prevents users from locking the entire table with exclusive access and locking the table with shared access.
| SHARE| Concurrent queries are allowed, but users cannot update locked tables.
| SHARE ROW EXCLUSIVE| Users can view the records in the table, but cannot update the table or lock the table.`SHARE`
| EXCLUSIVE| Allows querying of locked tables, but no other activities can be performed.
```

**Description**

The LOCK TABLE command is used to acquire a table-level lock and wait for any conflicting locks to be released if necessary. If NOWAIT is specified, then the LOCK TABLE command does not wait to acquire the desired lock: if the lock cannot be acquired immediately, then the command will break and generate an error. When a lock is acquired, the lock is held as the remainder of the current transaction (there is no "UNLOCK TABLE" command. The lock is automatically released when the transaction ends.)

AntDB always tries to use the least restrictive lock mode when the command refers to a table to get a lock automatically. When a higher restriction level lock is needed, you can use the LOCK TABLE command. For example, we may want a program that needs to keep data in a table in a stable state during a transaction that runs at the read committed level. To achieve this, a SHARE lock pattern can be obtained on the table before the query. This will prevent concurrent change data operations and will ensure that subsequent read operations on the table see a stable view of the committed data, since the SHARE lock pattern conflicts with the row-level EXCLUSIVE locks acquired by the write process, and the LOCK TABLE name IN SHARE MODE command will wait until the concurrent holder of any row-level EXCLUSIVE locks commits or rolls back the transaction. command will be in a waiting state until any concurrent holder of a row-level EXCLUSIVE lock commits or rolls back the transaction.

When running a serial isolation level transaction, to achieve a similar effect as mentioned above, the LOCK TABLE command must be executed before any data modification statements are executed. When the execution of the first data modification statement begins, the data that a serial transaction can see is in a frozen state. A subsequent LOCK TABLE command will prevent synchronous write operations, but it does not guarantee that the data read by the transaction corresponds to the latest committed value.

If a transaction of this sorting style changes data in a table, it should use the SHARE ROW EXCLUSIVE lock mode instead of the SHARE mode.

This command ensures that only one transaction of this type is run at a time. Otherwise, deadlock will occur: 2 transactions may acquire SHARE mode locks at the same time and will not be able to acquire ROW EXCLUSIVE mode locks to actually perform data update operations. (Note that locks owned by a transaction itself never conflict, so when a transaction acquires a SHARE schema lock, but not other transactions acquire a SHARE schema, it can acquire a ROW EXCLUSIVE schema lock). To avoid deadlocks, you must ensure that all transactions acquire locks on the same object in the same order, and that if multiple lock patterns are involved for individual objects, then the transaction should acquire the highest restricted lock pattern first.

**argument**

name

The name of the table to be locked (which can be referenced in a schema-qualified manner). The commands LOCK TABLE a,b are equivalent to LOCK TABLE A; LOCK TABLE b. The order of the locked tables is specified in the LOCK TABLE command.

lockmode

The mode of lock

WAIT

Specify that the database will wait (for a specified number of seconds to reach the specified integer) to obtain a DML lock.

NOWAIT

Specify that the LOCK TABLE command should not wait for any conflicting locks to be released: if the specified lock is not immediately available without waiting, then the transaction will be interrupted.

**Note**

All forms of locks require UPDATE or DELETE privileges.

LOCK TABLE is only useful inside a transaction block because the lock is removed as soon as the transaction ends.

LOCK TABLE handles only table-level locks, so schema names that involve ROW are misnomers. These schema names usually indicate the user's intent to obtain a row-level lock inside the locked table. The ROW EXCLUSIVE locking schema is a shareable table-level lock. It is important to remember that all lock patterns have the same semantics when focusing on LOCK TABLE, but differ only in the rules about which patterns are in conflict with each other.

**Example**

```
--session1：
create table test(id int, num int);
insert into test values(1,2);
begin;
lock table test in SHARE mode nowait;

--session2  Executing the following command waits for the lock to be released
update test set num = 3 where id = 1;
```