|The PostgreSQL 9.0 Reference Manual - Volume 1A - SQL Language Reference
by The PostgreSQL Global Development Group
Paperback (6"x9"), 454 pages
RRP £14.95 ($19.95)
Sales of this book support the PostgreSQL project! Get a printed copy>>>
11.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, just like table-level locks. Row-level locks do not affect data querying; they block only writers to the same row.
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 can 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 there is no limit on
the number of rows locked at one time. However, locking a row
might cause a disk write, e.g.,
UPDATE modifies 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 they are mentioned here for completeness.
|ISBN 9781906966041||The PostgreSQL 9.0 Reference Manual - Volume 1A - SQL Language Reference||See the print edition|