NAME

LOCK - 明確地鎖定一個表

SYNOPSIS

LOCK [ TABLE ] name [, ...] [ IN lockmode MODE ]

where lockmode is one of:

    ACCESS SHARE | ROW SHARE | ROW EXCLUSIVE | SHARE UPDATE EXCLUSIVE
    | SHARE | SHARE ROW EXCLUSIVE | EXCLUSIVE | ACCESS EXCLUSIVE

DESCRIPTION 描述

LOCK TABLE 獲取一個表級鎖，必要時等待任何沖突的鎖釋放。 一旦獲取了這個鎖，它就會在當前事務的余下部分一直保持。 （沒有 UNLOCK TABLE 命令；鎖總是在事務結尾釋放。）

 在為那些引用了表的命令自動請求鎖的時候，PostgreSQL 總是盡可能使用最小限制的鎖模式。LOCK TABLE  是為你在需要更嚴格的鎖的場合提供的。 例如，假設一個應用在讀已提交隔離級別上運行事務， 并且它需要保證在表中的數據在事務的運行過程中都存在。要實現這個目的， 你可以在查詢之前對表使用 SHARE 鎖模式進行鎖定。 這樣將保護數據不被并行修改并且為任何更進一步的對表的讀操作提供實際的當前狀態的數據， 因為 SHARE 鎖模式與任何寫操作需要的 ROW EXCLUSIVE 模式沖突， 并且你的 LOCK TABLE name IN SHARE MODE  語句將等到所有并行的寫操作提交或回卷后才執行。因此，一旦你獲得該鎖，那么就不會存在未提交的寫操作．

 如果運行在可串行化隔離級別并且你需要讀取真實狀態的數據時， 你必須在執行任何數據修改語句之前運行一個 LOCK TABLE 語句。 一個可串行化事務的數據圖象將在其***個數據修改語句開始的時候凍結住。 稍后的 LOCK TABLE 將仍然阻止并發的寫 －－－ 但它不能保證事務讀取的東西對應最近提交的數值。

 如果一個此類的事務準備修改一個表中的數據，那么應該使用 SHARE ROW EXCLUSIVE 鎖模式，而不是 SHARE 模式。 這樣就保證任意時刻只有一個此類的事務運行。不這樣做就可能會死鎖： 當兩個并行的事務可能都請求 SHARE 模式，然后試圖更改表中的數據時， 兩個事務在實際執行更新的時候都需要 ROW EXCLUSIVE 鎖模式， 但是它們無法再次獲取這個鎖。（請注意，一個事務自己的鎖是從不沖突的， 因此一個事務可以在持有 SHARE 模式的鎖的時候請求 ROW EXCLUSIVE 模式－－但是不能在任何其它事務持有 SHARE  模式的時候請求。） 為了避免死鎖，所有事務應該保證以相同的順序對相同的對象請求鎖， 并且，如果涉及多種鎖模式，那么事務應該總是***請求最嚴格的鎖模式。

 有關鎖模式和鎖定策略的更多信息，請參考 Section 12.3 ``Explicit Locking'' 。  

PARAMETERS 參數

name

 要鎖定的現存表的名字（可以有模式修飾）。

 命令 LOCK a, b; 等效于 LOCK a; LOCK b;。 表是按照 LOCK 命令中聲明的順序一個接一個順序上鎖的。

lockmode

 鎖模式聲明這個鎖和那些鎖沖突。鎖模式在 Section 12.3 ``Explicit Locking'' 里描述。

 如果沒有聲明鎖模式，那么使用最嚴格的模式 ACCESS EXCLUSIVE。

NOTES 注意

LOCK ... IN ACCESS SHARE MODE 需要在目標表上有 SELECT 權限。所有其它形式的 LOCK 需要 UPDATE 和/或 DELETE 權限。

LOCK 只是在一個事務塊的內部有用 （BEGIN...COMMIT），因為鎖在事務結束的時候馬上被釋放。 出現在任意事務塊外面的 LOCK 都自動生成一個自包含的事務，因此該鎖在獲取之后馬上被丟棄。

LOCK TABLE 只處理表級的鎖，因此那些有 ROW 字樣的鎖都是用詞不當。這些模式名字通常應該應該理解為用戶視圖在一個被鎖定的表中獲取行級的鎖。 同樣 ROW EXCLUSIVE 模式也是一個可共享的表級鎖。 我們一定要記住，只要是涉及到 LOCK TABLE， 那么所有鎖模式都有相同的語意，區別只是它們與哪種鎖沖突的規則。  

EXAMPLES 例子

 演示在往一個外鍵表上插入時在有主鍵的表上使用 SHARE 的鎖：

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 鎖：

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;

COMPATIBILITY 兼容性

 在 SQL 標準里面沒有LOCK TABLE ，可以使用 SET TRANSACTION 來聲明當前事務的級別。 PostgreSQL 也支持這個，參閱 SET TRANSACTION [set_transaction(7)] 獲取詳細信息。

 除了 ACCESS SHARE，ACCESS EXCLUSIVE，和 SHARE UPDATE EXCLUSIVE  鎖模式外， PostgreSQL 鎖模式和 LOCK TABLE 語句都與那些在 Oracle 里面的兼容。  

#p#

NAME

LOCK - lock a table

SYNOPSIS

LOCK [ TABLE ] name [, ...] [ IN lockmode MODE ]

where lockmode is one of:

    ACCESS SHARE | ROW SHARE | ROW EXCLUSIVE | SHARE UPDATE EXCLUSIVE
    | SHARE | SHARE ROW EXCLUSIVE | EXCLUSIVE | ACCESS EXCLUSIVE

DESCRIPTION

LOCK TABLE obtains a table-level lock, waiting if necessary for any conflicting locks to be released. Once obtained, the lock is held for the remainder of the current transaction. (There is no UNLOCK TABLE command; locks are always released at transaction end.)

When acquiring locks automatically for commands that reference tables, PostgreSQL always uses the least restrictive lock mode possible. LOCK TABLE provides for cases when you might need more restrictive locking. For example, suppose an application runs a transaction at the isolation level read committed and needs to ensure that data in a table remains stable for the duration of the transaction. To achieve this you could obtain SHARE lock mode over the table before querying. This will prevent concurrent data changes and ensure subsequent reads of the table see a stable view of committed data, because SHARE lock mode conflicts with the ROW EXCLUSIVE lock acquired by writers, and your LOCK TABLE name IN SHARE MODE statement will wait until any concurrent holders of ROW EXCLUSIVE mode locks commit or roll back. Thus, once you obtain the lock, there are no uncommitted writes outstanding; furthermore none can begin until you release the lock.

To achieve a similar effect when running a transaction at the isolation level serializable, you have to execute the LOCK TABLE statement before executing any data modification statement. A serializable transaction's view of data will be frozen when its first data modification statement begins. A later LOCK TABLE will still prevent concurrent writes --- but it won't ensure that what the transaction reads corresponds to the latest committed values.

If a transaction of this sort is going to change the data in the table, then it should use SHARE ROW EXCLUSIVE lock mode instead of SHARE mode. This ensures that only one transaction of this type runs at a time. Without this, a deadlock is possible: two transactions might both acquire SHARE mode, and then be unable to also acquire ROW EXCLUSIVE mode to actually perform their updates. (Note that a transaction's own locks never conflict, so a transaction can acquire ROW EXCLUSIVE mode when it holds SHARE mode --- but not if anyone else holds SHARE mode.) To avoid deadlocks, make sure all transactions acquire locks on the same objects in the same order, and if multiple lock modes are involved for a single object, then transactions should always acquire the most restrictive mode first.

More information about the lock modes and locking strategies can be found in the section called ``Explicit Locking'' in the documentation.  

PARAMETERS

name

The name (optionally schema-qualified) of an existing table to lock.

The command LOCK a, b; is equivalent to LOCK a; LOCK b;. The tables are locked one-by-one in the order specified in the LOCK command.

lockmode

The lock mode specifies which locks this lock conflicts with. Lock modes are described in the section called ``Explicit Locking'' in the documentation.

If no lock mode is specified, then ACCESS EXCLUSIVE, the most restrictive mode, is used.

NOTES

LOCK ... IN ACCESS SHARE MODE requires SELECT privileges on the target table. All other forms of LOCK require UPDATE and/or DELETE privileges.

LOCK is useful only inside a transaction block (BEGIN/COMMIT pair), since the lock is dropped as soon as the transaction ends. A LOCK command appearing outside any transaction block forms a self-contained transaction, so the lock will be dropped as soon as it is obtained.

LOCK TABLE only deals with table-level locks, and so the mode names involving ROW are all misnomers. These mode names should generally be read as indicating the intention of the user to acquire row-level locks within the locked table. Also, ROW EXCLUSIVE mode is a sharable table lock. Keep in mind that all the lock modes have identical semantics so far as LOCK TABLE is concerned, differing only in the rules about which modes conflict with which.  

EXAMPLES

Obtain a SHARE lock on a primary key table when going to perform inserts into a 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;

Take a SHARE ROW EXCLUSIVE lock on a primary key table when going to perform a 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;

COMPATIBILITY

There is no LOCK TABLE in the SQL standard, which instead uses SET TRANSACTION to specify concurrency levels on transactions. PostgreSQL supports that too; see SET TRANSACTION [set_transaction(7)] for details.

Except for ACCESS SHARE, ACCESS EXCLUSIVE, and SHARE UPDATE EXCLUSIVE lock modes, the PostgreSQL lock modes and the LOCK TABLE syntax are compatible with those present in Oracle.