lock.hlp

关系型数据库 Postgresql 6.5.2

.pgaw:Help.f.t insert end "LOCK" {bold} " Postgres always uses the least restrictive lock mode whenever possible. LOCK TABLE provided for cases when you might need more restrictive locking. 

For example, an application runs a transaction at READ COMMITTED isolation level and needs to ensure the existance of data in a table for the duration of the transaction. To achieve this you \
could use SHARE lock mode over the table before querying. This will protect data from concurrent changes and provide any further read operations over the table with data in their actual current \
state, because SHARE lock mode conflicts with any ROW EXCLUSIVE one acquired by writers, and your LOCK TABLE table IN SHARE MODE statement will wait until any concurrent \
write operations commit or rollback. 

" {} "Synopsis" {bold} "
" {} "
LOCK \[ TABLE \] table
LOCK \[ TABLE \] table IN \[ ROW | ACCESS \] \{ SHARE | EXCLUSIVE \} MODE
LOCK \[ TABLE \] table IN SHARE ROW EXCLUSIVE MODE

" {code} "Usage" {bold} "

    --
    -- SHARE lock primary key table when going to perform
    -- insert into foreign key table.
    --
" {} "    BEGIN WORK;
    LOCK TABLE films IN SHARE MODE;
    SELECT id FROM films 
      WHERE name = 'Star Wars: Episode I - The Phantom Menace';
    --
    -- Do ROLLBACK if record was not returned
    --
    INSERT INTO films_user_comments VALUES 
      (_id_, 'GREAT! I was waiting for it for so long!');
    COMMIT WORK;
  

    --
    -- SHARE ROW EXCLUSIVE lock primary key table when going to perform
    -- delete operation.
    --
    BEGIN WORK;
    LOCK TABLE films IN SHARE ROW EXCLUSIVE MODE;
    DELETE FROM films_user_comments WHERE id IN
      (SELECT id FROM films WHERE rating < 5);
    DELETE FROM films WHERE rating < 5;
    COMMIT WORK;

" {code} "Notes" {bold} "

LOCK is a Postgres language extension. 

Except for ACCESS SHARE/EXCLUSIVE lock modes, all other Postgres lock modes and the LOCK TABLE syntax are compatible with those present in Oracle. 

LOCK works only inside transactions. "