Lock table.
LOCK [TABLE] [ONLY] name [ * ] [, ...] [IN lockmode MODE] [NOWAIT]Where lockmode is one of the following:
ACCESS SHARE | ROW SHARE | ROW EXCLUSIVE | SHARE UPDATE EXCLUSIVE
| SHARE | SHARE ROW EXCLUSIVE | EXCLUSIVE | ACCESS EXCLUSIVELOCK TABLE acquires a table-level lock and waits for any conflicting locks to be released if necessary. If NOWAIT is specified, LOCK TABLE does not wait to acquire the required lock: if it cannot be acquired immediately, the command aborts and issues an error. Once acquired, the lock will remain for the rest of the current transaction. There is no UNLOCK TABLE command; the lock is always released at the end of the transaction.
When automatically obtaining locks for commands that reference tables, the YMatrix database always uses a lock mode with minimal limitations. LOCK TABLE provides situations where you may need more restrictive locking. For example, suppose the application runs a transaction on the Read Committed isolation level and needs to ensure that the data in the table remains stable during the transaction. To do this, you can get SHARE lock mode on the table before querying. This will prevent concurrent data changes and ensure that a stable view of the committed data will be seen when subsequent readings of the tables, because the SHARE lock mode conflicts with the ROW EXCLUSIVE lock acquired by the writer, and your LOCK TABLE name IN SHARE MODE statement will wait until any concurrent holder ROW EXCLUSIVE lock will lock the commit or rollback. Therefore, once the lock is obtained, there will be no uncommitted unfinished write operations. Also, no one can start until you release the lock.
In order to achieve similar results when running transactions under REPEATABLE READ or SERIALIZABLE isolation level, the LOCK TABLE statement must be executed before any SELECT or data modification statement is executed. When the first SELECT or data modification statement of the data view of the REPEATABLE READ or SERIALIZABLE transaction starts, it will be frozen. LOCK TABLE later in the transaction will still block concurrent writes - but it does not ensure that the content read by the transaction corresponds to the latest commit value.
If such transactions want to change the data in the table, you should use SHARE ROW EXCLUSIVE lock mode instead of SHARE mode. This ensures that only one transaction of this type is run at a time. Otherwise, it may result in deadlocks: both transactions may acquire SHARE mode at the same time, and then cannot obtain ROW EXCLUSIVE mode to actually perform its updates. Note that the transaction's own locks never conflict, so when the transaction has SHARE mode it can get the ROW EXCLUSIVE mode, but if someone else has SHARE mode, it won't. To avoid deadlocks, make sure all transactions acquire locks to the same object in the same order, and if a single object involves multiple locking patterns, the transaction should always obtain the most restrictive pattern first.
name
lockmode
NOWAIT
LOCK TABLE ... IN ACCESS SHARE MODE requires SELECT privileges on the target table. All other forms of LOCK require table-level UPDATE, DELETE, or TRUNCATE privileges.
LOCK TABLE is useless outside of transaction blocks: the lock will be released only when the LOCK statement is completed. Therefore, if LOCK is used outside the transaction block, the YMatrix database will report an error. Use BEGIN and END to define transaction blocks.
LOCK TABLE only handles table-level locks, so the schema names involving ROW are all wrong. These schema names should generally be understood as instructing the user to acquire row-level locks in the locked table. In addition, ROW EXCLUSIVE mode is a shareable table lock. Remember that in the case of LOCK TABLE, all lock modes have the same semantics, except that they differ in the rules of which modes conflict with which modes. For information on how to obtain actual row-level locks, see the FOR UPDATE/FOR SHARE clause in the SELECT reference documentation.
Get SHARE lock on the films table when performing the insert operation in the films_user_comments table:
BEGIN WORK;
LOCK TABLE films IN SHARE MODE;
SELECT id FROM films
WHERE name = 'Star Wars: Episode I - The Phantom Menace';
-- Do ROLLBACK if record was not returned
INSERT INTO films_user_comments VALUES
(_id_, 'GREAT! I was waiting for it for so long!');
COMMIT WORK;When performing a delete operation, perform SHARE ROW EXCLUSIVE lock on the table:
BEGIN WORK;
LOCK TABLE films IN SHARE ROW EXCLUSIVE MODE;
DELETE FROM films_user_comments WHERE id IN
(SELECT id FROM films WHERE rating < 5);
DELETE FROM films WHERE rating < 5;
COMMIT WORK;There is no LOCK TABLE in the SQL standard, but SET TRANSACTION is used to specify the concurrency level of the transaction. This is also supported by the YMatrix database.
In addition to ACCESS SHARE, ACCESS EXCLUSIVE and SHARE UPDATE EXCLUSIVE lock mode, YMatrix database lock mode and LOCK TABLE syntax are compatible with Oracle.