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.
