|PostgreSQL Reference Manual - Volume 1 - SQL Language Reference|
by The PostgreSQL Global Development Group
Paperback (6"x9"), 716 pages
RRP £32.00 ($49.95)
Sales of this book support the PostgreSQL project! Get a printed copy>>>
10.3.2 Row-Level Locks
In addition to table-level locks, there are row-level locks, which can be exclusive or shared locks. An exclusive row-level lock on a specific row is automatically acquired when the row is updated or deleted. The lock is held until the transaction commits or rolls back, in just the same way as for table-level locks. Row-level locks do not affect data querying; they block writers to the same row only.
To acquire an exclusive row-level lock on a row without actually
modifying the row, select the row with
UPDATE. Note that once the row-level lock is acquired,
the transaction may update the row multiple times without
fear of conflicts.
To acquire a shared row-level lock on a row, select the row with
SELECT FOR SHARE. A shared lock does not prevent
other transactions from acquiring the same shared lock. However,
no transaction is allowed to update, delete, or exclusively lock a
row on which any other transaction holds a shared lock. Any attempt
to do so will block until the shared lock(s) have been released.
PostgreSQL doesn't remember any
information about modified rows in memory, so it has no limit to
the number of rows locked at one time. However, locking a row
may cause a disk write; thus, for example,
UPDATE will modify selected rows to mark them locked, and so
will result in disk writes.
In addition to table and row locks, page-level share/exclusive locks are used to control read/write access to table pages in the shared buffer pool. These locks are released immediately after a row is fetched or updated. Application developers normally need not be concerned with page-level locks, but we mention them for completeness.
|ISBN 0954612027||PostgreSQL Reference Manual - Volume 1 - SQL Language Reference||See the print edition|