Within a REPEATABLE READ or SERIALIZABLE transaction, however, an error will be thrown if a row to be locked has changed since the transaction started.
#LIGHTWRITE 6 LOCKING UP UPDATE#
That is, other transactions that attempt UPDATE, DELETE, SELECT FOR UPDATE, SELECT FOR NO KEY UPDATE, SELECT FOR SHARE or SELECT FOR KEY SHARE of these rows will be blocked until the current transaction ends conversely, SELECT FOR UPDATE will wait for a concurrent transaction that has run any of those commands on the same row, and will then lock and return the updated row (or no row, if the row was deleted). This prevents them from being locked, modified or deleted by other transactions until the current transaction ends. This is also the default lock mode for LOCK TABLE statements that do not specify a mode explicitly.įOR UPDATE causes the rows retrieved by the SELECT statement to be locked as though for update. Many forms of ALTER TABLE also acquire a lock at this level. This mode guarantees that the holder is the only transaction accessing the table in any way.Īcquired by the DROP TABLE, TRUNCATE, REINDEX, CLUSTER, VACUUM FULL, and REFRESH MATERIALIZED VIEW (without CONCURRENTLY) commands. ACCESS EXCLUSIVEĬonflicts with locks of all modes ( ACCESS SHARE, ROW SHARE, ROW EXCLUSIVE, SHARE UPDATE EXCLUSIVE, SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE, and ACCESS EXCLUSIVE). This mode allows only concurrent ACCESS SHARE locks, i.e., only reads from the table can proceed in parallel with a transaction holding this lock mode.Īcquired by REFRESH MATERIALIZED VIEW CONCURRENTLY. EXCLUSIVEĬonflicts with the ROW SHARE, ROW EXCLUSIVE, SHARE UPDATE EXCLUSIVE, SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE, and ACCESS EXCLUSIVE lock modes. This mode protects a table against concurrent data changes, and is self-exclusive so that only one session can hold it at a time.Īcquired by CREATE TRIGGER and many forms of ALTER TABLE (see ALTER TABLE). SHARE ROW EXCLUSIVEĬonflicts with the ROW EXCLUSIVE, SHARE UPDATE EXCLUSIVE, SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE, and ACCESS EXCLUSIVE lock modes. This mode protects a table against concurrent data changes.Īcquired by CREATE INDEX (without CONCURRENTLY). SHAREĬonflicts with the ROW EXCLUSIVE, SHARE UPDATE EXCLUSIVE, SHARE ROW EXCLUSIVE, EXCLUSIVE, and ACCESS EXCLUSIVE lock modes.
#LIGHTWRITE 6 LOCKING UP FULL#
This mode protects a table against concurrent schema changes and VACUUM runs.Īcquired by VACUUM (without FULL), ANALYZE, CREATE INDEX CONCURRENTLY, and ALTER TABLE VALIDATE and other ALTER TABLE variants (for full details see ALTER TABLE). SHARE UPDATE EXCLUSIVEĬonflicts with the SHARE UPDATE EXCLUSIVE, SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE, and ACCESS EXCLUSIVE lock modes. In general, this lock mode will be acquired by any command that modifies data in a table. The commands UPDATE, DELETE, and INSERT acquire this lock mode on the target table (in addition to ACCESS SHARE locks on any other referenced tables). ROW EXCLUSIVEĬonflicts with the SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE, and ACCESS EXCLUSIVE lock modes. The SELECT FOR UPDATE and SELECT FOR SHARE commands acquire a lock of this mode on the target table(s) (in addition to ACCESS SHARE locks on any other tables that are referenced but not selected FOR UPDATE/FOR SHARE). ROW SHAREĬonflicts with the EXCLUSIVE and ACCESS EXCLUSIVE lock modes. In general, any query that only reads a table and does not modify it will acquire this lock mode. The SELECT command acquires a lock of this mode on referenced tables. Notice in particular that some lock modes are self-conflicting (for example, an ACCESS EXCLUSIVE lock cannot be held by more than one transaction at a time) while others are not self-conflicting (for example, an ACCESS SHARE lock can be held by multiple transactions).Ĭonflicts with the ACCESS EXCLUSIVE lock mode only. For example, it might acquire ACCESS EXCLUSIVE lock and later acquire ACCESS SHARE lock on the same table.) Non-conflicting lock modes can be held concurrently by many transactions. (However, a transaction never conflicts with itself. Two transactions cannot hold locks of conflicting modes on the same table at the same time.
The only real difference between one lock mode and another is the set of lock modes with which each conflicts (see Table 13-2).
To some extent the names reflect the typical usage of each lock mode - but the semantics are all the same. Remember that all of these lock modes are table-level locks, even if the name contains the word "row" the names of the lock modes are historical. You can also acquire any of these locks explicitly with the command LOCK. The list below shows the available lock modes and the contexts in which they are used automatically by PostgreSQL.
0 kommentar(er)
