LOCK TABLE
LOCK TABLE#
Syntax#
lock_table ::=#
Prerequisites#
The SYS user, the owner of the schema containing the table, and users having the LOCK ANY TABLE system privilege can execute this statement.
Description#
This statement is used to lock a table in a lock_mode. Once a table is locked, the locked table remains until the associated transaction is committed or rolled back.
user_name#
This is used to specify the name of the owner of the table to be locked. If omitted, Altibase will assume that the table belongs to the schema of the user connected via the current session.
tbl_name#
This is used to specify the name of the table to lock.
partition_name#
This is used to specify the name of the partition to be locked.
If the partition name is specified, lock mode is applied to the partition and ROW SHAR or ROW EXCLUSIVE is applied to the table.
| Lock Mode | Partition Apply | Table Apply |
|---|---|---|
| ROW SHARE | ROW SHARE | ROW SHARE |
| SHARE UPDATE | SHARE UPDATE | ROW SHARE |
| ROW EXCLUSIVE | ROW EXCLUSIVE | ROW EXCLUSIVE |
| SHARE ROW EXCLUSIVE | SHARE ROW EXCLUSIVE | ROW EXCLUSIVE |
| SHARE | SHARE | ROW SHARE |
| EXCLUSIVE | EXCLUSIVE | ROW EXCLUSIVE |
lock_mode#
One of the following lock modes should be specified when executing the lock on a table:
- ROW SHARE
In ROW SHARE mode, concurrent access of other transactions on a locked table set by this mode is allowed. However, locking a table with the EXCLUSIVE Mode, which is for the exclusive access of other users, is prohibited. - SHARE UPDATE
This mode is a lock mode with an identical function with that of the ROW SHARE mode. - ROW EXCLUSIVE
The concurrent access of other transactions on a locked table set by this mode is allowed. However, locking a table with the EXCLUSIVE or SHARE mode, which is for the exclusive access of other users, is firmly prohibited. The lock is automatically obtained when updating, insert, or deleting data - SHARE ROW EXCLUSIVE
The SHARE ROW EXCLUSIVE is used to view an entire table while permitting others to view the table. However, locking a table with the SHARE mode for an exclusive access of other users is prohibited. - SHARE
In the SHARE mode, other transactions can view a table locked by this mode, but updating it is prohibited. - EXCLUSIVE
The EXCLUSVE mode allows the current transaction to view or update a table which is locked by this mode, but other transactions cannot be able to view nor update.
WAIT | NOWAIT#
This clause is used to specify whether to wait until a lock has been obtained. It can be omitted, in which case the wait to obtain a lock on an individual row will continue for an unlimited time.
- WAIT n
This option specifies that a transaction is to wait for n seconds to obtain a lock on the row. If the row cannot be locked during this time, an error will be raised. - NOWAIT
The NOWAIT option specifies that a transaction is not to wait to obtain a lock if it is not immediately possible. In this case, Altibase returns an error indicating that the specified table has already been locked by another user.
UNTIL NEXT DDL#
The UNTIL NEXT DDL statement enables an auto commit immediately before DDL is executed if the DDL is performed and the session is in the NON-AUTOCOMMIT mode.
However, if the EXCLUSIVE mode is selected in the lock_mode, the auto commit is not executed before the DDL is performed. This function can be used only once per a transaction.
|
SQL Statement |
Mode of Table Lock |
Lock Modes Permitted? |
||||
|
IS |
IX |
S |
SIX |
X |
||
|
SELECT … FROM tbl_name … |
IS |
Y(IS) |
Y(IX) |
Y(S) |
Y(SIX) |
N(X) |
|
INSERT INTO tbl_name … |
IX |
Y(IX) |
Y(IX) |
N(SIX) |
N(SIX) |
N(X) |
|
UPDATE tbl_name … |
IX |
Y*(IX) |
Y*(IX) |
N(SIX) |
N(SIX) |
N(X) |
|
DELETE FROM tbl_name … |
IX |
Y*(IX) |
Y*(IX) |
N(SIX) |
N(SIX) |
N(X) |
|
SELECT … FROM tbl_name FOR UPDATE … |
IS |
Y*(IX) |
Y*(IX) |
Y*(S) |
Y*(SIX) |
N(X) |
|
LOCK TABLE tbl_name IN ROW SHARE MODE |
IS |
Y(IS) |
Y(IX) |
Y(S) |
Y(SIX) |
N(X) |
|
LOCK TABLE tbl_name IN ROW EXCLUSIVE MODE |
IX |
Y(IX) |
Y(IX) |
N(SIX) |
N(SIX) |
N(X) |
|
LOCK TABLE tbl_name IN SHARE MODE |
S |
Y(S) |
N(SIX) |
Y(S) |
N(SIX) |
N(X) |
|
LOCK TABLE tbl_name IN SHARE ROW EXCLUSIVE MODE |
SIX |
Y(SIX) |
N(SIX) |
N(SIX) |
N(SIX) |
N(X) |
|
LOCK TABLE tbl_name IN EXCLUSIVE MODE |
X |
N(X) |
N(X) |
N(X) |
N(X) |
N(X) |
|
IS: row share (Intent share lock) IX: row exclusive (Intent exclusive lock) S: share SIX: share row exclusive (Share with Intent exclusive lock) X: exclusive * Y: If a row lock conflict is not caused by another transaction, the transaction can acquire a lock. Otherwise, a wait occurs.
The lock type shown in parentheses is: 1. If the current lock mode switch is allowed by another transaction(Y), the lock type currently held is switched to the type in parentheses. 2. If the current lock mode switch is not allowed by another transaction (N), the lock type can be switched to the type in parentheses only when the transaction that is currently acquiring the lcok acquires a new lock. |
||||||
[Table 4‑1] Summary of Table Locks
Examples#
The following example shows how Altibase manages data concurrency, integrity, and consistency when using LOCK TABLE and SELECT statements.
| Transaction A | Time Point | Transaction B |
|---|---|---|
| iSQL> AUTOCOMMIT OFF; Set autocommit off success. |
iSQL> AUTOCOMMIT OFF; Set autocommit off success. |
|
| 1 | (request X lock on employees) iSQL> LOCK TABLE employees IN EXCLUSIVE MODE; Command execute success. (acquire X lock on employees) |
|
| iSQL> DROP TABLE employees; [ERR-11170: The transaction has exceeded the lock timeout specified by the user.] |
2 | |
| 3 | iSQL> UPDATE employees SET salary = 2500000 WHERE eno = 15; 1 row updated. |
|
| (request S lock on employees) iSQL> LOCK TABLE employees IN SHARE MODE; (the request conflicts with the X lock already held by transaction B) wait wait wait |
4 | |
| 5 | iSQL> COMMIT; Commit success. (release X lock on employees) |
|
| (resume) Lock success. (acquire S lock on employees) iSQL> SELECT salary FROM employees WHERE eno = 15; SALARY -------------- 2500 row selected. (It can be seen that the data have been committed.) |
6 | |
| iSQL> ROLLBACK; Rollback success. (release S lock on employees) |
7 | |
| iSQL> LOCK TABLE employees IN EXCLUSIVE MODE; Lock success. (acquire X lock on employees) |
8 | |
| iSQL> SELECT SALARY FROM employees WHERE eno = 15; wait wait wait |
||
| iSQL> UPDATE employees SET eno = 30 WHERE eno = 15; 1 row updated. |
10 | |
| iSQL> COMMIT; Commit success. (release X loc on employees) |
11 | |
| 12 | (resume) SALARY -------------- 2500 1 row selected. |