Postgresql 简明教程

PostgreSQL - LOCKS

锁或排他锁或写锁可防止用户修改行或整个表。随后对 UPDATE 和 DELETE 修改的行在事务期间自动独占锁定。这可以防止其他用户在提交或回滚事务之前更改该行。

Locks or Exclusive Locks or Write Locks prevent users from modifying a row or an entire table. Rows modified by UPDATE and DELETE are then exclusively locked automatically for the duration of the transaction. This prevents other users from changing the row until the transaction is either committed or rolled back.

用户必须等待其他用户的情况仅当他们尝试修改同一行时出现。而当他们修改不同行时，不需要等待。SELECT 查询绝不会出现等待。

The only time when users must wait for other users is when they are trying to modify the same row. If they modify different rows, no waiting is necessary. SELECT queries never have to wait.

数据库会自动执行锁定。然而，在某些情况下，锁定必须手动控制。可以通过使用 LOCK 命令实现手动锁定。它允许用户指定事务的锁定类型和范围。

The database performs locking automatically. In certain cases, however, locking must be controlled manually. Manual locking can be done by using the LOCK command. It allows specification of a transaction’s lock type and scope.

Syntax for LOCK command

LOCK 命令的基本语法如下−

The basic syntax for LOCK command is as follows −

LOCK [ TABLE ]
name
 IN
lock_mode

name − The name (optionally schema-qualified) of an existing table to lock. If ONLY is specified before the table name, only that table is locked. If ONLY is not specified, the table and all its descendant tables (if any) are locked.
lock_mode − The lock mode specifies which locks this lock conflicts with. If no lock mode is specified, then ACCESS EXCLUSIVE, the most restrictive mode, is used. Possible values are: ACCESS SHARE, ROW SHARE, ROW EXCLUSIVE, SHARE UPDATE EXCLUSIVE, SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE, ACCESS EXCLUSIVE.

DeadLocks

当两个事务都等待对方完成各自的操作时，就会发生死锁。不过，尽管 PostgreSQL 可以检测到死锁并通过 ROLLBACK 结束它们，死锁仍然会造成不便。为了防止你的应用程序遇到此问题，务必使用相同的方式设计应用程序中的对象，以实现锁定。

Deadlocks can occur when two transactions are waiting for each other to finish their operations. While PostgreSQL can detect them and end them with a ROLLBACK, deadlocks can still be inconvenient. To prevent your applications from running into this problem, make sure to design them in such a way that they will lock objects in the same order.

Advisory Locks

PostgreSQL 可以创建具有应用程序定义的含义的锁定，称之为咨询锁定。因为系统并不会强制使用这些锁定，应用程序必须正确使用。咨询锁定可用于不适合 MVCC 模式的锁定策略。

PostgreSQL provides means for creating locks that have application-defined meanings. These are called advisory locks. As the system does not enforce their use, it is up to the application to use them correctly. Advisory locks can be useful for locking strategies that are an awkward fit for the MVCC model.

例如，咨询锁定的一个常见用法是模拟典型的所谓“平面文件”数据管理系统的悲观锁定策略。虽然存储在表中的标记可用作相同目的，但咨询锁定更高效，可以避免表膨胀，而且会在会话结束时由服务器自动清除。

For example, a common use of advisory locks is to emulate pessimistic locking strategies typical of the so-called "flat file" data management systems. While a flag stored in a table could be used for the same purpose, advisory locks are faster, avoid table bloat, and are automatically cleaned up by the server at the end of the session.

Example

考虑 COMPANY 表具有以下记录：

Consider the table COMPANY having records as follows −

testdb# select * from COMPANY;
 id | name  | age | address   | salary
----+-------+-----+-----------+--------
  1 | Paul  |  32 | California|  20000
  2 | Allen |  25 | Texas     |  15000
  3 | Teddy |  23 | Norway    |  20000
  4 | Mark  |  25 | Rich-Mond |  65000
  5 | David |  27 | Texas     |  85000
  6 | Kim   |  22 | South-Hall|  45000
  7 | James |  24 | Houston   |  10000
(7 rows)

以下示例以访问排他模式锁定 testdb 数据库中的 COMPANY 表。LOCK 语句仅在事务模式下使用−

The following example locks the COMPANY table within the testdb database in ACCESS EXCLUSIVE mode. The LOCK statement works only in a transaction mode −

testdb=#BEGIN;
LOCK TABLE company1 IN ACCESS EXCLUSIVE MODE;

以上给出的 PostgreSQL 语句将产生以下结果 -

The above given PostgreSQL statement will produce the following result −

LOCK TABLE

以上消息表示该表会在事务结束前被锁定，要结束事务，你必须回滚或提交事务。

The above message indicates that the table is locked until the transaction ends and to finish the transaction you will have to either rollback or commit the transaction.