When IN EXCLUSIVE MODE is specified in the LOCK TABLE statement, the user may change the lock to be used during data updating, addition, and deletion. Referencing, adding, updating, or deletion of data in the table by other transactions are not allowed. In other words, the exclusive (EX) mode goes into effect. On the other hand, because row-based locking is not applied, the overhead for applying the lock is reduced, thus preventing a shortage of buffers for locking. However, because table-based locking is applied, other transactions may have to wait for a long time.

When either of the following SQLs is specified, the user may change the lock to be used during data retrieval:

WITHOUT LOCK of SELECT statement

The data that is retrieved is not locked until the transaction terminates. Because locking is released beginning with rows that have been retrieved, better concurrent execution can be achieved.

IN SHARE MODE of LOCK TABLE statement

The transaction monopolizes the resource and allows only referencing of the data in the table by other transactions. In other words, the shared (PR) mode goes into effect. On the other hand, because row-based locking is not applied, the overhead for applying the lock is reduced, thus preventing a shortage of buffers for locking. However, because table-based locking is applied, other transactions may have to wait for a long time.