NAME

LOCK - 明確地鎖定一個(gè)表

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 獲取一個(gè)表級(jí)鎖，必要時(shí)等待任何沖突的鎖釋放。 一旦獲取了這個(gè)鎖，它就會(huì)在當(dāng)前事務(wù)的余下部分一直保持。 （沒(méi)有 UNLOCK TABLE 命令；鎖總是在事務(wù)結(jié)尾釋放。）

 在為那些引用了表的命令自動(dòng)請(qǐng)求鎖的時(shí)候，PostgreSQL 總是盡可能使用最小限制的鎖模式。LOCK TABLE  是為你在需要更嚴(yán)格的鎖的場(chǎng)合提供的。 例如，假設(shè)一個(gè)應(yīng)用在讀已提交隔離級(jí)別上運(yùn)行事務(wù)， 并且它需要保證在表中的數(shù)據(jù)在事務(wù)的運(yùn)行過(guò)程中都存在。要實(shí)現(xiàn)這個(gè)目的， 你可以在查詢之前對(duì)表使用 SHARE 鎖模式進(jìn)行鎖定。 這樣將保護(hù)數(shù)據(jù)不被并行修改并且為任何更進(jìn)一步的對(duì)表的讀操作提供實(shí)際的當(dāng)前狀態(tài)的數(shù)據(jù)， 因?yàn)?nbsp;SHARE 鎖模式與任何寫操作需要的 ROW EXCLUSIVE 模式?jīng)_突， 并且你的 LOCK TABLE name IN SHARE MODE  語(yǔ)句將等到所有并行的寫操作提交或回卷后才執(zhí)行。因此，一旦你獲得該鎖，那么就不會(huì)存在未提交的寫操作．

 如果運(yùn)行在可串行化隔離級(jí)別并且你需要讀取真實(shí)狀態(tài)的數(shù)據(jù)時(shí)， 你必須在執(zhí)行任何數(shù)據(jù)修改語(yǔ)句之前運(yùn)行一個(gè) LOCK TABLE 語(yǔ)句。 一個(gè)可串行化事務(wù)的數(shù)據(jù)圖象將在其***個(gè)數(shù)據(jù)修改語(yǔ)句開始的時(shí)候凍結(jié)住。 稍后的 LOCK TABLE 將仍然阻止并發(fā)的寫 －－－ 但它不能保證事務(wù)讀取的東西對(duì)應(yīng)最近提交的數(shù)值。

 如果一個(gè)此類的事務(wù)準(zhǔn)備修改一個(gè)表中的數(shù)據(jù)，那么應(yīng)該使用 SHARE ROW EXCLUSIVE 鎖模式，而不是 SHARE 模式。 這樣就保證任意時(shí)刻只有一個(gè)此類的事務(wù)運(yùn)行。不這樣做就可能會(huì)死鎖： 當(dāng)兩個(gè)并行的事務(wù)可能都請(qǐng)求 SHARE 模式，然后試圖更改表中的數(shù)據(jù)時(shí)， 兩個(gè)事務(wù)在實(shí)際執(zhí)行更新的時(shí)候都需要 ROW EXCLUSIVE 鎖模式， 但是它們無(wú)法再次獲取這個(gè)鎖。（請(qǐng)注意，一個(gè)事務(wù)自己的鎖是從不沖突的， 因此一個(gè)事務(wù)可以在持有 SHARE 模式的鎖的時(shí)候請(qǐng)求 ROW EXCLUSIVE 模式－－但是不能在任何其它事務(wù)持有 SHARE  模式的時(shí)候請(qǐng)求。） 為了避免死鎖，所有事務(wù)應(yīng)該保證以相同的順序?qū)ο嗤膶?duì)象請(qǐng)求鎖， 并且，如果涉及多種鎖模式，那么事務(wù)應(yīng)該總是***請(qǐng)求最嚴(yán)格的鎖模式。

 有關(guān)鎖模式和鎖定策略的更多信息，請(qǐng)參考 Section 12.3 ``Explicit Locking'' 。  

PARAMETERS 參數(shù)

name

 要鎖定的現(xiàn)存表的名字（可以有模式修飾）。

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

lockmode

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

 如果沒(méi)有聲明鎖模式，那么使用最嚴(yán)格的模式 ACCESS EXCLUSIVE。

NOTES 注意

LOCK ... IN ACCESS SHARE MODE 需要在目標(biāo)表上有 SELECT 權(quán)限。所有其它形式的 LOCK 需要 UPDATE 和/或 DELETE 權(quán)限。

LOCK 只是在一個(gè)事務(wù)塊的內(nèi)部有用 （BEGIN...COMMIT），因?yàn)殒i在事務(wù)結(jié)束的時(shí)候馬上被釋放。 出現(xiàn)在任意事務(wù)塊外面的 LOCK 都自動(dòng)生成一個(gè)自包含的事務(wù)，因此該鎖在獲取之后馬上被丟棄。

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

EXAMPLES 例子

 演示在往一個(gè)外鍵表上插入時(shí)在有主鍵的表上使用 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;

 在執(zhí)行刪除操作時(shí)對(duì)一個(gè)有主鍵的表進(jìn)行 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 標(biāo)準(zhǔn)里面沒(méi)有LOCK TABLE ，可以使用 SET TRANSACTION 來(lái)聲明當(dāng)前事務(wù)的級(jí)別。 PostgreSQL 也支持這個(gè)，參閱 SET TRANSACTION [set_transaction(7)] 獲取詳細(xì)信息。

 除了 ACCESS SHARE，ACCESS EXCLUSIVE，和 SHARE UPDATE EXCLUSIVE  鎖模式外， PostgreSQL 鎖模式和 LOCK TABLE 語(yǔ)句都與那些在 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.