A shared table lock allows multiple transactions to read the data of a base table. A transaction that has a shared table lock on a base table can modify the table, provided that no other transaction holds a lock of any kind on the rows being modified.

A shared table lock is acquired, for example, by executing a LOCK TABLE...IN SHARED MODE statement. The REFRESH MATERIALIZED VIEW and REFRESH TEXT INDEX statements also support a WITH SHARE MODE clause that you can use to create shared table locks on the underlying tables while the refresh operation takes place.

An intent to write table lock, also known as an intent table lock, is implicitly acquired the first time a write lock on a row is acquired by a transaction. That is, an intent table lock is obtained when updating, inserting, or deleting a row. As with shared table locks, intent table locks are held until the transaction completes via a COMMIT or a ROLLBACK. Intent table locks conflict with shared and exclusive table locks, but not with other intent table locks.

An exclusive table lock prevents other transactions from modifying the schema or data in a table, including inserting new data. Unlike an exclusive schema lock, transactions executing at isolation level 0 can still read the rows in a table that has an exclusive table lock on it. Only one transaction can hold an exclusive lock on any table at one time. Exclusive table locks conflict with all other table and row locks.

You acquire an exclusive table lock implicitly when using the LOAD TABLE statement.

You acquire an exclusive table lock explicitly by using the LOCK TABLE...IN EXCLUSIVE MODE statement. The REFRESH MATERIALIZED VIEW and REFRESH TEXT INDEX statements also provide a WITH EXCLUSIVE MODE clause that you can use to place exclusive table locks on the underlying tables while the refresh operation takes place.

• Schema locks
• LOCK TABLE statement
• REFRESH MATERIALIZED VIEW statement
• REFRESH TEXT INDEX statement