{"Entry":{"collection":"sql","key":"lock","name":"LOCK","aliases":["lock"],"metadata":{"aliases":["lock"],"changed_in":["6.5","7.0","7.2","7.4","8.0","8.4","9.0"],"changes":[{"from":"6.4","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["description","usage","compatibility"],"removed":[]},"status":"changed","synopsis":{"added":["LOCK [ TABLE ] table IN [ ROW | ACCESS ] { SHARE | EXCLUSIVE } MODE","LOCK [ TABLE ] table IN SHARE ROW EXCLUSIVE MODE"],"removed":[]},"to":"6.5"},{"from":"6.5","purpose_changed":true,"renamed":null,"sections":{"added":[],"changed":["description","compatibility"],"removed":[]},"status":"changed","synopsis":{"added":["LOCK [ TABLE ] name","LOCK [ TABLE ] name IN [ ROW | ACCESS ] { SHARE | EXCLUSIVE } MODE","LOCK [ TABLE ] name IN SHARE ROW EXCLUSIVE MODE"],"removed":["LOCK [ TABLE ] table","LOCK [ TABLE ] table IN [ ROW | ACCESS ] { SHARE | EXCLUSIVE } MODE","LOCK [ TABLE ] table IN SHARE ROW EXCLUSIVE MODE"]},"to":"7.0"},{"from":"7.0","purpose_changed":false,"renamed":{"from_file":"sql-lock.htm","to_file":"sql-lock.html"},"sections":{"added":[],"changed":["description","compatibility"],"removed":[]},"status":"changed","synopsis":null,"to":"7.1"},{"from":"7.1","purpose_changed":true,"renamed":null,"sections":{"added":[],"changed":["description","compatibility"],"removed":[]},"status":"changed","synopsis":{"added":["LOCK [ TABLE ] name [, ...]","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"],"removed":["LOCK [ TABLE ] name IN [ ROW | ACCESS ] { SHARE | EXCLUSIVE } MODE","LOCK [ TABLE ] name IN SHARE ROW EXCLUSIVE MODE"]},"to":"7.2"},{"from":"7.2","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["description","usage"],"removed":[]},"status":"changed","synopsis":null,"to":"7.3"},{"from":"7.3","purpose_changed":true,"renamed":null,"sections":{"added":["parameters","notes","examples"],"changed":["description","compatibility"],"removed":["usage"]},"status":"changed","synopsis":{"added":["LOCK [ TABLE ] name [, ...] [ IN lockmode MODE ]"],"removed":["LOCK [ TABLE ] name [, ...] IN lockmode MODE"]},"to":"7.4"},{"from":"7.4","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["description","parameters","notes"],"removed":[]},"status":"changed","synopsis":{"added":["LOCK [ TABLE ] name [, ...] [ IN lockmode MODE ] [ NOWAIT ]"],"removed":[]},"to":"8.0"},{"from":"8.0","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["notes"],"removed":[]},"status":"changed","synopsis":null,"to":"8.1"},{"from":"8.2","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["description","parameters","notes"],"removed":[]},"status":"changed","synopsis":null,"to":"8.3"},{"from":"8.3","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["parameters","notes"],"removed":[]},"status":"changed","synopsis":{"added":["LOCK [ TABLE ] [ ONLY ] name [ * ] [, ...] [ IN lockmode MODE ] [ NOWAIT ]"],"removed":[]},"to":"8.4"},{"from":"8.4","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":[],"removed":[]},"status":"changed","synopsis":{"added":[],"removed":["where lockmode is one of:"]},"to":"9.0"},{"from":"9.0","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["description","notes"],"removed":[]},"status":"changed","synopsis":null,"to":"9.1"},{"from":"9.2","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["notes"],"removed":[]},"status":"changed","synopsis":null,"to":"9.3"},{"from":"9.4","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["description","notes"],"removed":[]},"status":"changed","synopsis":null,"to":"9.5"},{"from":"10","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["description","notes"],"removed":[]},"status":"changed","synopsis":null,"to":"11"},{"from":"12","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["notes"],"removed":[]},"status":"changed","synopsis":null,"to":"13"},{"from":"14","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["notes"],"removed":[]},"status":"changed","synopsis":null,"to":"15"},{"from":"15","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["notes"],"removed":[]},"status":"changed","synopsis":null,"to":"16"},{"from":"16","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["notes"],"removed":[]},"status":"changed","synopsis":null,"to":"17"}],"content_hash":"aa1a79cd9416e02313bbc48ac7e30e5b791fd22adfef168a363b3dc7a6e4cafe","editorial":{},"first_version":"6.4","group":"query","imported_at":"2026-09-27T17:57:27.060825+08:00","last_version":"20","name":"LOCK","object":"","position":11004,"present_in":["6.4","6.5","7.0","7.1","7.2","7.3","7.4","8.0","8.1","8.2","8.3","8.4","9.0","9.1","9.2","9.3","9.4","9.5","9.6","10","11","12","13","14","15","16","17","18","19","20"],"purpose":"lock a table","purpose_zh":"","related":[],"slug":"lock","source_rev":"b7bd9cda","synopsis":"LOCK [ TABLE ] [ ONLY ] name [ * ] [, ...] [ IN lockmode MODE ] [ NOWAIT ]\n\nwhere lockmode is one of:\n\nACCESS SHARE | ROW SHARE | ROW EXCLUSIVE | SHARE UPDATE EXCLUSIVE\n| SHARE | SHARE ROW EXCLUSIVE | EXCLUSIVE | ACCESS EXCLUSIVE","verb":"LOCK"}},"Definition":{"Collection":"sql","Key":"lock","SourceDatabase":"center","Version":"18","SourceTable":"sqlcmd","SourceKey":"lock","SourceRevision":"b7bd9cda","Facts":{"anchor":"SQL-LOCK","file":"sql-lock.html","lang":"en","name":"LOCK","purpose":"lock a table","purpose_zh":"","related":[],"sections":[{"html":"\u003cp\u003e\u003ccode class=\"command\"\u003eLOCK TABLE\u003c/code\u003e obtains a table-level lock, waiting if necessary for any conflicting locks to be released. If \u003ccode class=\"literal\"\u003eNOWAIT\u003c/code\u003e is specified, \u003ccode class=\"command\"\u003eLOCK TABLE\u003c/code\u003e does not wait to acquire the desired lock: if it cannot be acquired immediately, the command is aborted and an error is emitted. Once obtained, the lock is held for the remainder of the current transaction. (There is no \u003ccode class=\"command\"\u003eUNLOCK TABLE\u003c/code\u003e command; locks are always released at transaction end.)\u003c/p\u003e\u003cp\u003eWhen a view is locked, all relations appearing in the view definition query are also locked recursively with the same lock mode.\u003c/p\u003e\u003cp\u003eWhen acquiring locks automatically for commands that reference tables, \u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e always uses the least restrictive lock mode possible. \u003ccode class=\"command\"\u003eLOCK TABLE\u003c/code\u003e provides for cases when you might need more restrictive locking. For example, suppose an application runs a transaction at the \u003ccode class=\"literal\"\u003eREAD COMMITTED\u003c/code\u003e isolation level and needs to ensure that data in a table remains stable for the duration of the transaction. To achieve this you could obtain \u003ccode class=\"literal\"\u003eSHARE\u003c/code\u003e 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 \u003ccode class=\"literal\"\u003eSHARE\u003c/code\u003e lock mode conflicts with the \u003ccode class=\"literal\"\u003eROW EXCLUSIVE\u003c/code\u003e lock acquired by writers, and your \u003ccode class=\"command\"\u003eLOCK TABLE \u003cem class=\"replaceable\"\u003e\u003ccode\u003ename\u003c/code\u003e\u003c/em\u003e IN SHARE MODE\u003c/code\u003e statement will wait until any concurrent holders of \u003ccode class=\"literal\"\u003eROW EXCLUSIVE\u003c/code\u003e 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.\u003c/p\u003e\u003cp\u003eTo achieve a similar effect when running a transaction at the \u003ccode class=\"literal\"\u003eREPEATABLE READ\u003c/code\u003e or \u003ccode class=\"literal\"\u003eSERIALIZABLE\u003c/code\u003e isolation level, you have to execute the \u003ccode class=\"command\"\u003eLOCK TABLE\u003c/code\u003e statement before executing any \u003ccode class=\"command\"\u003eSELECT\u003c/code\u003e or data modification statement. A \u003ccode class=\"literal\"\u003eREPEATABLE READ\u003c/code\u003e or \u003ccode class=\"literal\"\u003eSERIALIZABLE\u003c/code\u003e transaction's view of data will be frozen when its first \u003ccode class=\"command\"\u003eSELECT\u003c/code\u003e or data modification statement begins. A \u003ccode class=\"command\"\u003eLOCK TABLE\u003c/code\u003e later in the transaction will still prevent concurrent writes — but it won't ensure that what the transaction reads corresponds to the latest committed values.\u003c/p\u003e\u003cp\u003eIf a transaction of this sort is going to change the data in the table, then it should use \u003ccode class=\"literal\"\u003eSHARE ROW EXCLUSIVE\u003c/code\u003e lock mode instead of \u003ccode class=\"literal\"\u003eSHARE\u003c/code\u003e 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 \u003ccode class=\"literal\"\u003eSHARE\u003c/code\u003e mode, and then be unable to also acquire \u003ccode class=\"literal\"\u003eROW EXCLUSIVE\u003c/code\u003e mode to actually perform their updates. (Note that a transaction's own locks never conflict, so a transaction can acquire \u003ccode class=\"literal\"\u003eROW EXCLUSIVE\u003c/code\u003e mode when it holds \u003ccode class=\"literal\"\u003eSHARE\u003c/code\u003e mode — but not if anyone else holds \u003ccode class=\"literal\"\u003eSHARE\u003c/code\u003e 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.\u003c/p\u003e\u003cp\u003eMore information about the lock modes and locking strategies can be found in \u003ca href=\"/docs/18/explicit-locking.html\" title=\"13.3. Explicit Locking\"\u003eSection 13.3\u003c/a\u003e.\u003c/p\u003e","key":"description","title":"Description"},{"html":"\u003cdiv class=\"variablelist\"\u003e\u003cdl class=\"variablelist\"\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003ename\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eThe name (optionally schema-qualified) of an existing table to lock. If \u003ccode class=\"literal\"\u003eONLY\u003c/code\u003e is specified before the table name, only that table is locked. If \u003ccode class=\"literal\"\u003eONLY\u003c/code\u003e is not specified, the table and all its descendant tables (if any) are locked. Optionally, \u003ccode class=\"literal\"\u003e*\u003c/code\u003e can be specified after the table name to explicitly indicate that descendant tables are included.\u003c/p\u003e\u003cp\u003eThe command \u003ccode class=\"literal\"\u003eLOCK TABLE a, b;\u003c/code\u003e is equivalent to \u003ccode class=\"literal\"\u003eLOCK TABLE a; LOCK TABLE b;\u003c/code\u003e. The tables are locked one-by-one in the order specified in the \u003ccode class=\"command\"\u003eLOCK TABLE\u003c/code\u003e command.\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003elockmode\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eThe lock mode specifies which locks this lock conflicts with. Lock modes are described in \u003ca href=\"/docs/18/explicit-locking.html\" title=\"13.3. Explicit Locking\"\u003eSection 13.3\u003c/a\u003e.\u003c/p\u003e\u003cp\u003eIf no lock mode is specified, then \u003ccode class=\"literal\"\u003eACCESS EXCLUSIVE\u003c/code\u003e, the most restrictive mode, is used.\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eNOWAIT\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eSpecifies that \u003ccode class=\"command\"\u003eLOCK TABLE\u003c/code\u003e should not wait for any conflicting locks to be released: if the specified lock(s) cannot be acquired immediately without waiting, the transaction is aborted.\u003c/p\u003e\u003c/dd\u003e\u003c/dl\u003e\u003c/div\u003e","key":"parameters","title":"Parameters"},{"html":"\u003cp\u003eTo lock a table, the user must have the right privilege for the specified \u003cem class=\"replaceable\"\u003e\u003ccode\u003elockmode\u003c/code\u003e\u003c/em\u003e. If the user has \u003ccode class=\"literal\"\u003eMAINTAIN\u003c/code\u003e, \u003ccode class=\"literal\"\u003eUPDATE\u003c/code\u003e, \u003ccode class=\"literal\"\u003eDELETE\u003c/code\u003e, or \u003ccode class=\"literal\"\u003eTRUNCATE\u003c/code\u003e privileges on the table, any \u003cem class=\"replaceable\"\u003e\u003ccode\u003elockmode\u003c/code\u003e\u003c/em\u003e is permitted. If the user has \u003ccode class=\"literal\"\u003eINSERT\u003c/code\u003e privileges on the table, \u003ccode class=\"literal\"\u003eROW EXCLUSIVE MODE\u003c/code\u003e (or a less-conflicting mode as described in \u003ca href=\"/docs/18/explicit-locking.html\" title=\"13.3. Explicit Locking\"\u003eSection 13.3\u003c/a\u003e) is permitted. If a user has \u003ccode class=\"literal\"\u003eSELECT\u003c/code\u003e privileges on the table, \u003ccode class=\"literal\"\u003eACCESS SHARE MODE\u003c/code\u003e is permitted.\u003c/p\u003e\u003cp\u003eThe user performing the lock on the view must have the corresponding privilege on the view. In addition, by default, the view's owner must have the relevant privileges on the underlying base relations, whereas the user performing the lock does not need any permissions on the underlying base relations. However, if the view has \u003ccode class=\"literal\"\u003esecurity_invoker\u003c/code\u003e set to \u003ccode class=\"literal\"\u003etrue\u003c/code\u003e (see \u003ca href=\"/docs/18/sql-createview.html\" title=\"CREATE VIEW\"\u003e\u003ccode class=\"command\"\u003eCREATE VIEW\u003c/code\u003e\u003c/a\u003e), the user performing the lock, rather than the view owner, must have the relevant privileges on the underlying base relations.\u003c/p\u003e\u003cp\u003e\u003ccode class=\"command\"\u003eLOCK TABLE\u003c/code\u003e is useless outside a transaction block: the lock would remain held only to the completion of the statement. Therefore \u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e reports an error if \u003ccode class=\"command\"\u003eLOCK\u003c/code\u003e is used outside a transaction block. Use \u003ca href=\"/docs/18/sql-begin.html\" title=\"BEGIN\"\u003e\u003ccode class=\"command\"\u003eBEGIN\u003c/code\u003e\u003c/a\u003e and \u003ca href=\"/docs/18/sql-commit.html\" title=\"COMMIT\"\u003e\u003ccode class=\"command\"\u003eCOMMIT\u003c/code\u003e\u003c/a\u003e (or \u003ca href=\"/docs/18/sql-rollback.html\" title=\"ROLLBACK\"\u003e\u003ccode class=\"command\"\u003eROLLBACK\u003c/code\u003e\u003c/a\u003e) to define a transaction block.\u003c/p\u003e\u003cp\u003e\u003ccode class=\"command\"\u003eLOCK TABLE\u003c/code\u003e only deals with table-level locks, and so the mode names involving \u003ccode class=\"literal\"\u003eROW\u003c/code\u003e 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, \u003ccode class=\"literal\"\u003eROW EXCLUSIVE\u003c/code\u003e mode is a shareable table lock. Keep in mind that all the lock modes have identical semantics so far as \u003ccode class=\"command\"\u003eLOCK TABLE\u003c/code\u003e is concerned, differing only in the rules about which modes conflict with which. For information on how to acquire an actual row-level lock, see \u003ca href=\"/docs/18/explicit-locking.html#LOCKING-ROWS\" title=\"13.3.2. Row-Level Locks\"\u003eSection 13.3.2\u003c/a\u003e and \u003ca href=\"/docs/18/sql-select.html#SQL-FOR-UPDATE-SHARE\" title=\"The Locking Clause\"\u003eThe Locking Clause\u003c/a\u003e in the \u003ca href=\"/docs/18/sql-select.html\" title=\"SELECT\"\u003e\u003cspan class=\"refentrytitle\"\u003eSELECT\u003c/span\u003e\u003c/a\u003e documentation.\u003c/p\u003e","key":"notes","title":"Notes"},{"html":"\u003cp\u003eObtain a \u003ccode class=\"literal\"\u003eSHARE\u003c/code\u003e lock on a primary key table when going to perform inserts into a foreign key table:\u003c/p\u003e\u003cpre class=\"programlisting\"\u003eBEGIN WORK;\nLOCK TABLE films IN SHARE MODE;\nSELECT id FROM films\n    WHERE name = 'Star Wars: Episode I - The Phantom Menace';\n-- Do ROLLBACK if record was not returned\nINSERT INTO films_user_comments VALUES\n    (_id_, 'GREAT! I was waiting for it for so long!');\nCOMMIT WORK;\n\u003c/pre\u003e\u003cp\u003eTake a \u003ccode class=\"literal\"\u003eSHARE ROW EXCLUSIVE\u003c/code\u003e lock on a primary key table when going to perform a delete operation:\u003c/p\u003e\u003cpre class=\"programlisting\"\u003eBEGIN WORK;\nLOCK TABLE films IN SHARE ROW EXCLUSIVE MODE;\nDELETE FROM films_user_comments WHERE id IN\n    (SELECT id FROM films WHERE rating \u0026lt; 5);\nDELETE FROM films WHERE rating \u0026lt; 5;\nCOMMIT WORK;\n\u003c/pre\u003e","key":"examples","title":"Examples"},{"html":"\u003cp\u003eThere is no \u003ccode class=\"command\"\u003eLOCK TABLE\u003c/code\u003e in the SQL standard, which instead uses \u003ccode class=\"command\"\u003eSET TRANSACTION\u003c/code\u003e to specify concurrency levels on transactions. \u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e supports that too; see \u003ca href=\"/docs/18/sql-set-transaction.html\" title=\"SET TRANSACTION\"\u003e\u003cspan class=\"refentrytitle\"\u003eSET TRANSACTION\u003c/span\u003e\u003c/a\u003e for details.\u003c/p\u003e\u003cp\u003eExcept for \u003ccode class=\"literal\"\u003eACCESS SHARE\u003c/code\u003e, \u003ccode class=\"literal\"\u003eACCESS EXCLUSIVE\u003c/code\u003e, and \u003ccode class=\"literal\"\u003eSHARE UPDATE EXCLUSIVE\u003c/code\u003e lock modes, the \u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e lock modes and the \u003ccode class=\"command\"\u003eLOCK TABLE\u003c/code\u003e syntax are compatible with those present in \u003cspan class=\"productname\"\u003eOracle\u003c/span\u003e.\u003c/p\u003e","key":"compatibility","title":"Compatibility"}],"sections_same_as":"","slug":"18","synopsis_html":"LOCK [ TABLE ] [ ONLY ] \u003cem class=\"replaceable\"\u003e\u003ccode\u003ename\u003c/code\u003e\u003c/em\u003e [ * ] [, ...] [ IN \u003cem class=\"replaceable\"\u003e\u003ccode\u003elockmode\u003c/code\u003e\u003c/em\u003e MODE ] [ NOWAIT ]\n\n\u003cspan class=\"phrase\"\u003ewhere \u003cem class=\"replaceable\"\u003e\u003ccode\u003elockmode\u003c/code\u003e\u003c/em\u003e is one of:\u003c/span\u003e\n\n    ACCESS SHARE | ROW SHARE | ROW EXCLUSIVE | SHARE UPDATE EXCLUSIVE\n    | SHARE | SHARE ROW EXCLUSIVE | EXCLUSIVE | ACCESS EXCLUSIVE","synopsis_text":"LOCK [ TABLE ] [ ONLY ] name [ * ] [, ...] [ IN lockmode MODE ] [ NOWAIT ]\n\nwhere lockmode is one of:\n\nACCESS SHARE | ROW SHARE | ROW EXCLUSIVE | SHARE UPDATE EXCLUSIVE\n| SHARE | SHARE ROW EXCLUSIVE | EXCLUSIVE | ACCESS EXCLUSIVE"},"ManualEvidence":{},"MeasuredEvidence":{}},"Text":{"Collection":"sql","Key":"lock","SourceDatabase":"pgweb","Version":"18","Locale":"zh-Hans","Title":"LOCK","Summary":"锁定表","BodyHTML":"\u003cpre\u003eLOCK [ TABLE ] [ ONLY ] name [ * ] [, ...] [ IN lockmode MODE ] [ NOWAIT ]\n\n其中 lockmode 可以是以下之一：\n\nACCESS SHARE | ROW SHARE | ROW EXCLUSIVE | SHARE UPDATE EXCLUSIVE\n| SHARE | SHARE ROW EXCLUSIVE | EXCLUSIVE | ACCESS EXCLUSIVE\u003c/pre\u003e\u003csection\u003e\u003ch2\u003e描述\u003c/h2\u003e\u003cp\u003e\u003ccode\u003eLOCK TABLE\u003c/code\u003e获取一个表级锁；如有必要，会等待任何冲突锁被释放。如果指定了\u003ccode\u003eNOWAIT\u003c/code\u003e，\u003ccode\u003eLOCK TABLE\u003c/code\u003e就不会等待获取所需的锁：如果无法立即获得，命令将被中止并报错。锁一旦获得，就会一直持有到当前事务结束。（没有\u003ccode\u003eUNLOCK TABLE\u003c/code\u003e命令；锁总是在事务结束时释放。）\u003c/p\u003e\u003cp\u003e锁定一个视图时，出现在该视图定义查询中的所有关系也会以相同的锁模式递归地被锁定。\u003c/p\u003e\u003cp\u003e在为引用表的命令自动获取锁时，\u003cspan\u003ePostgreSQL\u003c/span\u003e总是尽可能使用限制最少的锁模式。\u003ccode\u003eLOCK TABLE\u003c/code\u003e适用于可能需要更严格锁定的场景。例如，假设某个应用在\u003ccode\u003eREAD COMMITTED\u003c/code\u003e隔离级别下运行事务，并且需要确保某个表中的数据在整个事务期间保持稳定。要做到这一点，可以在查询之前先对该表获取\u003ccode\u003eSHARE\u003c/code\u003e锁模式。这样可以阻止并发的数据更改，并确保后续对该表的读取看到已提交数据的稳定视图，因为\u003ccode\u003eSHARE\u003c/code\u003e锁模式与写入者获取的\u003ccode\u003eROW EXCLUSIVE\u003c/code\u003e锁冲突，而\u003ccode\u003eLOCK TABLE \u003cem\u003e\u003ccode\u003ename\u003c/code\u003e\u003c/em\u003e IN SHARE MODE\u003c/code\u003e 语句会一直等待，直到所有并发持有\u003ccode\u003eROW EXCLUSIVE\u003c/code\u003e模式锁的事务提交或回滚。因此，一旦获得该锁，就不存在尚未提交的写入；而且在释放该锁之前，也不会有新的写入开始。\u003c/p\u003e\u003cp\u003e若要在\u003ccode\u003eREPEATABLE READ\u003c/code\u003e或\u003ccode\u003eSERIALIZABLE\u003c/code\u003e 隔离级别的事务中达到类似效果，你必须在执行任何\u003ccode\u003eSELECT\u003c/code\u003e 或数据修改语句之前执行\u003ccode\u003eLOCK TABLE\u003c/code\u003e语句。\u003ccode\u003eREPEATABLE READ\u003c/code\u003e或\u003ccode\u003eSERIALIZABLE\u003c/code\u003e事务的数据视图，会在其第一条\u003ccode\u003eSELECT\u003c/code\u003e或数据修改语句开始时冻结。在事务稍后再执行\u003ccode\u003eLOCK TABLE\u003c/code\u003e仍然可以阻止并发写入 — 但它不能保证该事务读取到的是最新已提交的值。\u003c/p\u003e\u003cp\u003e如果这类事务还要修改表中的数据，那么它应使用\u003ccode\u003eSHARE ROW EXCLUSIVE\u003c/code\u003e锁模式，而不是\u003ccode\u003eSHARE\u003c/code\u003e模式。这样可以确保同一时间只有一个这类事务在运行。否则就可能发生死锁：两个事务都可能先获得\u003ccode\u003eSHARE\u003c/code\u003e模式，然后都无法再获得实际执行更新所需的\u003ccode\u003eROW EXCLUSIVE\u003c/code\u003e模式。（注意，事务自己的锁永远不会互相冲突，因此事务在持有\u003ccode\u003eSHARE\u003c/code\u003e模式时仍可获得 \u003ccode\u003eROW EXCLUSIVE\u003c/code\u003e模式，但前提是没有其他人持有\u003ccode\u003eSHARE\u003c/code\u003e模式。）为避免死锁，要确保所有事务都按相同顺序对相同对象获取锁；如果同一对象需要多种锁模式，则事务应始终先获取限制最严格的模式。\u003c/p\u003e\u003cp\u003e关于锁模式和锁策略的更多信息，请参见\u003ca href=\"/docs/18/explicit-locking.html\" rel=\"nofollow\"\u003e第 13.3 节\u003c/a\u003e。\u003c/p\u003e\u003c/section\u003e\u003csection\u003e\u003ch2\u003e参数\u003c/h2\u003e\u003cdiv\u003e\u003cdl\u003e\u003cdt\u003e\u003cspan\u003e\u003cem\u003e\u003ccode\u003ename\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e要锁定的现有表的名称（可选模式限定）。如果在表名前指定了 \u003ccode\u003eONLY\u003c/code\u003e，则只有该表会被锁定。如果未指定\u003ccode\u003eONLY\u003c/code\u003e，则该表及其所有后代表（如果有）都会被锁定。也可以在表名后指定\u003ccode\u003e*\u003c/code\u003e，以显式表明包含后代表。\u003c/p\u003e\u003cp\u003e命令\u003ccode\u003eLOCK TABLE a, b;\u003c/code\u003e等效于 \u003ccode\u003eLOCK TABLE a; LOCK TABLE b;\u003c/code\u003e。这些表会按 \u003ccode\u003eLOCK TABLE\u003c/code\u003e命令中指定的顺序逐个锁定。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003cem\u003e\u003ccode\u003elockmode\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e锁模式指定该锁会与哪些锁冲突。锁模式见\u003ca href=\"/docs/18/explicit-locking.html\" rel=\"nofollow\"\u003e第 13.3 节\u003c/a\u003e。\u003c/p\u003e\u003cp\u003e如果未指定锁模式，则使用限制最严格的\u003ccode\u003eACCESS EXCLUSIVE\u003c/code\u003e模式。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003ccode\u003eNOWAIT\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e指定\u003ccode\u003eLOCK TABLE\u003c/code\u003e不等待任何冲突锁被释放：如果指定的锁无法在不等待的情况下立即获得，事务就会中止。\u003c/p\u003e\u003c/dd\u003e\u003c/dl\u003e\u003c/div\u003e\u003c/section\u003e\u003csection\u003e\u003ch2\u003e注解\u003c/h2\u003e\u003cp\u003e要锁定一个表，用户必须拥有与所指定\u003cem\u003e\u003ccode\u003elockmode\u003c/code\u003e\u003c/em\u003e对应的权限。如果用户在该表上拥有\u003ccode\u003eMAINTAIN\u003c/code\u003e、\u003ccode\u003eUPDATE\u003c/code\u003e、\u003ccode\u003eDELETE\u003c/code\u003e或\u003ccode\u003eTRUNCATE\u003c/code\u003e权限，则允许使用任意 \u003cem\u003e\u003ccode\u003elockmode\u003c/code\u003e\u003c/em\u003e。如果用户在该表上拥有\u003ccode\u003eINSERT\u003c/code\u003e权限，则允许使用\u003ccode\u003eROW EXCLUSIVE MODE\u003c/code\u003e（或冲突更少的锁模式，见\u003ca href=\"/docs/18/explicit-locking.html\" rel=\"nofollow\"\u003e第 13.3 节\u003c/a\u003e）。如果用户在该表上拥有\u003ccode\u003eSELECT\u003c/code\u003e权限，则允许使用\u003ccode\u003eACCESS SHARE MODE\u003c/code\u003e。\u003c/p\u003e\u003cp\u003e在视图上执行锁定操作的用户必须对该视图拥有相应权限。此外，默认情况下，视图所有者必须对底层基关系拥有相关权限，而执行锁定操作的用户不需要对底层基关系拥有任何权限。但是，如果视图的\u003ccode\u003esecurity_invoker\u003c/code\u003e设置为\u003ccode\u003etrue\u003c/code\u003e（参见\u003ca href=\"/docs/18/sql-createview.html\" title=\"CREATE VIEW\" rel=\"nofollow\"\u003e\u003ccode\u003eCREATE VIEW\u003c/code\u003e\u003c/a\u003e），那么必须由执行锁定操作的用户而不是视图所有者，对底层基关系拥有相关权限。\u003c/p\u003e\u003cp\u003e\u003ccode\u003eLOCK TABLE\u003c/code\u003e在事务块外毫无用处：锁只会一直持有到该语句结束。因此，如果在事务块外使用\u003ccode\u003eLOCK\u003c/code\u003e，\u003cspan\u003ePostgreSQL\u003c/span\u003e会报告错误。请使用\u003ca href=\"/docs/18/sql-begin.html\" title=\"BEGIN\" rel=\"nofollow\"\u003e\u003ccode\u003eBEGIN\u003c/code\u003e\u003c/a\u003e和 \u003ca href=\"/docs/18/sql-commit.html\" title=\"COMMIT\" rel=\"nofollow\"\u003e\u003ccode\u003eCOMMIT\u003c/code\u003e\u003c/a\u003e（或\u003ca href=\"/docs/18/sql-rollback.html\" title=\"ROLLBACK\" rel=\"nofollow\"\u003e\u003ccode\u003eROLLBACK\u003c/code\u003e\u003c/a\u003e）来定义事务块。\u003c/p\u003e\u003cp\u003e\u003ccode\u003eLOCK TABLE\u003c/code\u003e只处理表级锁，因此名称中带有\u003ccode\u003eROW\u003c/code\u003e的模式其实都不准确。这些模式名称通常应理解为：用户打算在被锁定的表中获取行级锁。此外，\u003ccode\u003eROW EXCLUSIVE\u003c/code\u003e模式本身也是一种可共享的表锁。请记住，就\u003ccode\u003eLOCK TABLE\u003c/code\u003e而言，所有锁模式的语义完全相同，差别只在于哪些模式彼此冲突。关于如何获取真正的行级锁，请参阅\u003ca href=\"/docs/18/explicit-locking.html#LOCKING-ROWS\" rel=\"nofollow\"\u003e第 13.3.2 节\u003c/a\u003e和\u003ca href=\"/docs/18/sql-select.html#SQL-FOR-UPDATE-SHARE\" title=\"锁定子句\" rel=\"nofollow\"\u003eThe Locking Clause\u003c/a\u003e（后者位于\u003ca href=\"/docs/18/sql-select.html\" title=\"SELECT\" rel=\"nofollow\"\u003e\u003cspan\u003eSELECT\u003c/span\u003e\u003c/a\u003e文档中）。\u003c/p\u003e\u003c/section\u003e\u003csection\u003e\u003ch2\u003e示例\u003c/h2\u003e\u003cp\u003e在准备向外键表执行插入时，在主键表上获取一个\u003ccode\u003eSHARE\u003c/code\u003e锁：\u003c/p\u003e\u003cpre\u003eBEGIN WORK;\nLOCK TABLE films IN SHARE MODE;\nSELECT id FROM films\n    WHERE name = \u0026#39;Star Wars: Episode I - The Phantom Menace\u0026#39;;\n-- 如果未返回记录则执行 ROLLBACK\nINSERT INTO films_user_comments VALUES\n    (_id_, \u0026#39;GREAT! I was waiting for it for so long!\u0026#39;);\nCOMMIT WORK;\n\u003c/pre\u003e\u003cp\u003e在准备执行删除操作时，在主键表上获取一个\u003ccode\u003eSHARE ROW EXCLUSIVE\u003c/code\u003e锁：\u003c/p\u003e\u003cpre\u003eBEGIN WORK;\nLOCK TABLE films IN SHARE ROW EXCLUSIVE MODE;\nDELETE FROM films_user_comments WHERE id IN\n    (SELECT id FROM films WHERE rating \u0026lt; 5);\nDELETE FROM films WHERE rating \u0026lt; 5;\nCOMMIT WORK;\n\u003c/pre\u003e\u003c/section\u003e\u003csection\u003e\u003ch2\u003e兼容性\u003c/h2\u003e\u003cp\u003eSQL 标准中没有\u003ccode\u003eLOCK TABLE\u003c/code\u003e，而是使用\u003ccode\u003eSET TRANSACTION\u003c/code\u003e 来指定事务的并发级别。\u003cspan\u003ePostgreSQL\u003c/span\u003e也支持这一点；详见\u003ca href=\"/docs/18/sql-set-transaction.html\" title=\"SET TRANSACTION\" rel=\"nofollow\"\u003e\u003cspan\u003eSET TRANSACTION\u003c/span\u003e\u003c/a\u003e。\u003c/p\u003e\u003cp\u003e除\u003ccode\u003eACCESS SHARE\u003c/code\u003e、\u003ccode\u003eACCESS EXCLUSIVE\u003c/code\u003e和 \u003ccode\u003eSHARE UPDATE EXCLUSIVE\u003c/code\u003e锁模式外，\u003cspan\u003ePostgreSQL\u003c/span\u003e的锁模式和\u003ccode\u003eLOCK TABLE\u003c/code\u003e语法与\u003cspan\u003eOracle\u003c/span\u003e中的对应语法兼容。\u003c/p\u003e\u003c/section\u003e","SourceRevision":"ca936764","ContentHash":"adbf61682165275200c3f8c76d61b7d80a687106c09c206fb6df699abfd0772c","Payload":{"purpose_zh":"锁定表","sections":[{"html":"\u003cp\u003e\u003ccode class=\"command\"\u003eLOCK TABLE\u003c/code\u003e获取一个表级锁；如有必要，会等待任何冲突锁被释放。如果指定了\u003ccode class=\"literal\"\u003eNOWAIT\u003c/code\u003e，\u003ccode class=\"command\"\u003eLOCK TABLE\u003c/code\u003e就不会等待获取所需的锁：如果无法立即获得，命令将被中止并报错。锁一旦获得，就会一直持有到当前事务结束。（没有\u003ccode class=\"command\"\u003eUNLOCK TABLE\u003c/code\u003e命令；锁总是在事务结束时释放。）\u003c/p\u003e\u003cp\u003e锁定一个视图时，出现在该视图定义查询中的所有关系也会以相同的锁模式递归地被锁定。\u003c/p\u003e\u003cp\u003e在为引用表的命令自动获取锁时，\u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e总是尽可能使用限制最少的锁模式。\u003ccode class=\"command\"\u003eLOCK TABLE\u003c/code\u003e适用于可能需要更严格锁定的场景。例如，假设某个应用在\u003ccode class=\"literal\"\u003eREAD COMMITTED\u003c/code\u003e隔离级别下运行事务，并且需要确保某个表中的数据在整个事务期间保持稳定。要做到这一点，可以在查询之前先对该表获取\u003ccode class=\"literal\"\u003eSHARE\u003c/code\u003e锁模式。这样可以阻止并发的数据更改，并确保后续对该表的读取看到已提交数据的稳定视图，因为\u003ccode class=\"literal\"\u003eSHARE\u003c/code\u003e锁模式与写入者获取的\u003ccode class=\"literal\"\u003eROW EXCLUSIVE\u003c/code\u003e锁冲突，而\u003ccode class=\"command\"\u003eLOCK TABLE \u003cem class=\"replaceable\"\u003e\u003ccode\u003ename\u003c/code\u003e\u003c/em\u003e IN SHARE MODE\u003c/code\u003e 语句会一直等待，直到所有并发持有\u003ccode class=\"literal\"\u003eROW EXCLUSIVE\u003c/code\u003e模式锁的事务提交或回滚。因此，一旦获得该锁，就不存在尚未提交的写入；而且在释放该锁之前，也不会有新的写入开始。\u003c/p\u003e\u003cp\u003e若要在\u003ccode class=\"literal\"\u003eREPEATABLE READ\u003c/code\u003e或\u003ccode class=\"literal\"\u003eSERIALIZABLE\u003c/code\u003e 隔离级别的事务中达到类似效果，你必须在执行任何\u003ccode class=\"command\"\u003eSELECT\u003c/code\u003e 或数据修改语句之前执行\u003ccode class=\"command\"\u003eLOCK TABLE\u003c/code\u003e语句。\u003ccode class=\"literal\"\u003eREPEATABLE READ\u003c/code\u003e或\u003ccode class=\"literal\"\u003eSERIALIZABLE\u003c/code\u003e事务的数据视图，会在其第一条\u003ccode class=\"command\"\u003eSELECT\u003c/code\u003e或数据修改语句开始时冻结。在事务稍后再执行\u003ccode class=\"command\"\u003eLOCK TABLE\u003c/code\u003e仍然可以阻止并发写入 — 但它不能保证该事务读取到的是最新已提交的值。\u003c/p\u003e\u003cp\u003e如果这类事务还要修改表中的数据，那么它应使用\u003ccode class=\"literal\"\u003eSHARE ROW EXCLUSIVE\u003c/code\u003e锁模式，而不是\u003ccode class=\"literal\"\u003eSHARE\u003c/code\u003e模式。这样可以确保同一时间只有一个这类事务在运行。否则就可能发生死锁：两个事务都可能先获得\u003ccode class=\"literal\"\u003eSHARE\u003c/code\u003e模式，然后都无法再获得实际执行更新所需的\u003ccode class=\"literal\"\u003eROW EXCLUSIVE\u003c/code\u003e模式。（注意，事务自己的锁永远不会互相冲突，因此事务在持有\u003ccode class=\"literal\"\u003eSHARE\u003c/code\u003e模式时仍可获得 \u003ccode class=\"literal\"\u003eROW EXCLUSIVE\u003c/code\u003e模式，但前提是没有其他人持有\u003ccode class=\"literal\"\u003eSHARE\u003c/code\u003e模式。）为避免死锁，要确保所有事务都按相同顺序对相同对象获取锁；如果同一对象需要多种锁模式，则事务应始终先获取限制最严格的模式。\u003c/p\u003e\u003cp\u003e关于锁模式和锁策略的更多信息，请参见\u003ca href=\"/docs/18/explicit-locking.html\" title=\"13.3. 显式锁定\"\u003e第 13.3 节\u003c/a\u003e。\u003c/p\u003e","key":"description","title":"描述"},{"html":"\u003cdiv class=\"variablelist\"\u003e\u003cdl class=\"variablelist\"\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003ename\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e要锁定的现有表的名称（可选模式限定）。如果在表名前指定了 \u003ccode class=\"literal\"\u003eONLY\u003c/code\u003e，则只有该表会被锁定。如果未指定\u003ccode class=\"literal\"\u003eONLY\u003c/code\u003e，则该表及其所有后代表（如果有）都会被锁定。也可以在表名后指定\u003ccode class=\"literal\"\u003e*\u003c/code\u003e，以显式表明包含后代表。\u003c/p\u003e\u003cp\u003e命令\u003ccode class=\"literal\"\u003eLOCK TABLE a, b;\u003c/code\u003e等效于 \u003ccode class=\"literal\"\u003eLOCK TABLE a; LOCK TABLE b;\u003c/code\u003e。这些表会按 \u003ccode class=\"command\"\u003eLOCK TABLE\u003c/code\u003e命令中指定的顺序逐个锁定。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003elockmode\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e锁模式指定该锁会与哪些锁冲突。锁模式见\u003ca href=\"/docs/18/explicit-locking.html\" title=\"13.3. 显式锁定\"\u003e第 13.3 节\u003c/a\u003e。\u003c/p\u003e\u003cp\u003e如果未指定锁模式，则使用限制最严格的\u003ccode class=\"literal\"\u003eACCESS EXCLUSIVE\u003c/code\u003e模式。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eNOWAIT\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e指定\u003ccode class=\"command\"\u003eLOCK TABLE\u003c/code\u003e不等待任何冲突锁被释放：如果指定的锁无法在不等待的情况下立即获得，事务就会中止。\u003c/p\u003e\u003c/dd\u003e\u003c/dl\u003e\u003c/div\u003e","key":"parameters","title":"参数"},{"html":"\u003cp\u003e要锁定一个表，用户必须拥有与所指定\u003cem class=\"replaceable\"\u003e\u003ccode\u003elockmode\u003c/code\u003e\u003c/em\u003e对应的权限。如果用户在该表上拥有\u003ccode class=\"literal\"\u003eMAINTAIN\u003c/code\u003e、\u003ccode class=\"literal\"\u003eUPDATE\u003c/code\u003e、\u003ccode class=\"literal\"\u003eDELETE\u003c/code\u003e或\u003ccode class=\"literal\"\u003eTRUNCATE\u003c/code\u003e权限，则允许使用任意 \u003cem class=\"replaceable\"\u003e\u003ccode\u003elockmode\u003c/code\u003e\u003c/em\u003e。如果用户在该表上拥有\u003ccode class=\"literal\"\u003eINSERT\u003c/code\u003e权限，则允许使用\u003ccode class=\"literal\"\u003eROW EXCLUSIVE MODE\u003c/code\u003e（或冲突更少的锁模式，见\u003ca href=\"/docs/18/explicit-locking.html\" title=\"13.3. 显式锁定\"\u003e第 13.3 节\u003c/a\u003e）。如果用户在该表上拥有\u003ccode class=\"literal\"\u003eSELECT\u003c/code\u003e权限，则允许使用\u003ccode class=\"literal\"\u003eACCESS SHARE MODE\u003c/code\u003e。\u003c/p\u003e\u003cp\u003e在视图上执行锁定操作的用户必须对该视图拥有相应权限。此外，默认情况下，视图所有者必须对底层基关系拥有相关权限，而执行锁定操作的用户不需要对底层基关系拥有任何权限。但是，如果视图的\u003ccode class=\"literal\"\u003esecurity_invoker\u003c/code\u003e设置为\u003ccode class=\"literal\"\u003etrue\u003c/code\u003e（参见\u003ca href=\"/docs/18/sql-createview.html\" title=\"CREATE VIEW\"\u003e\u003ccode class=\"command\"\u003eCREATE VIEW\u003c/code\u003e\u003c/a\u003e），那么必须由执行锁定操作的用户而不是视图所有者，对底层基关系拥有相关权限。\u003c/p\u003e\u003cp\u003e\u003ccode class=\"command\"\u003eLOCK TABLE\u003c/code\u003e在事务块外毫无用处：锁只会一直持有到该语句结束。因此，如果在事务块外使用\u003ccode class=\"command\"\u003eLOCK\u003c/code\u003e，\u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e会报告错误。请使用\u003ca href=\"/docs/18/sql-begin.html\" title=\"BEGIN\"\u003e\u003ccode class=\"command\"\u003eBEGIN\u003c/code\u003e\u003c/a\u003e和 \u003ca href=\"/docs/18/sql-commit.html\" title=\"COMMIT\"\u003e\u003ccode class=\"command\"\u003eCOMMIT\u003c/code\u003e\u003c/a\u003e（或\u003ca href=\"/docs/18/sql-rollback.html\" title=\"ROLLBACK\"\u003e\u003ccode class=\"command\"\u003eROLLBACK\u003c/code\u003e\u003c/a\u003e）来定义事务块。\u003c/p\u003e\u003cp\u003e\u003ccode class=\"command\"\u003eLOCK TABLE\u003c/code\u003e只处理表级锁，因此名称中带有\u003ccode class=\"literal\"\u003eROW\u003c/code\u003e的模式其实都不准确。这些模式名称通常应理解为：用户打算在被锁定的表中获取行级锁。此外，\u003ccode class=\"literal\"\u003eROW EXCLUSIVE\u003c/code\u003e模式本身也是一种可共享的表锁。请记住，就\u003ccode class=\"command\"\u003eLOCK TABLE\u003c/code\u003e而言，所有锁模式的语义完全相同，差别只在于哪些模式彼此冲突。关于如何获取真正的行级锁，请参阅\u003ca href=\"/docs/18/explicit-locking.html#LOCKING-ROWS\" title=\"13.3.2. 行级锁\"\u003e第 13.3.2 节\u003c/a\u003e和\u003ca href=\"/docs/18/sql-select.html#SQL-FOR-UPDATE-SHARE\" title=\"锁定子句\"\u003eThe Locking Clause\u003c/a\u003e（后者位于\u003ca href=\"/docs/18/sql-select.html\" title=\"SELECT\"\u003e\u003cspan class=\"refentrytitle\"\u003eSELECT\u003c/span\u003e\u003c/a\u003e文档中）。\u003c/p\u003e","key":"notes","title":"注解"},{"html":"\u003cp\u003e在准备向外键表执行插入时，在主键表上获取一个\u003ccode class=\"literal\"\u003eSHARE\u003c/code\u003e锁：\u003c/p\u003e\u003cpre class=\"programlisting\"\u003eBEGIN WORK;\nLOCK TABLE films IN SHARE MODE;\nSELECT id FROM films\n    WHERE name = 'Star Wars: Episode I - The Phantom Menace';\n-- 如果未返回记录则执行 ROLLBACK\nINSERT INTO films_user_comments VALUES\n    (_id_, 'GREAT! I was waiting for it for so long!');\nCOMMIT WORK;\n\u003c/pre\u003e\u003cp\u003e在准备执行删除操作时，在主键表上获取一个\u003ccode class=\"literal\"\u003eSHARE ROW EXCLUSIVE\u003c/code\u003e锁：\u003c/p\u003e\u003cpre class=\"programlisting\"\u003eBEGIN WORK;\nLOCK TABLE films IN SHARE ROW EXCLUSIVE MODE;\nDELETE FROM films_user_comments WHERE id IN\n    (SELECT id FROM films WHERE rating \u0026lt; 5);\nDELETE FROM films WHERE rating \u0026lt; 5;\nCOMMIT WORK;\n\u003c/pre\u003e","key":"examples","title":"示例"},{"html":"\u003cp\u003eSQL 标准中没有\u003ccode class=\"command\"\u003eLOCK TABLE\u003c/code\u003e，而是使用\u003ccode class=\"command\"\u003eSET TRANSACTION\u003c/code\u003e 来指定事务的并发级别。\u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e也支持这一点；详见\u003ca href=\"/docs/18/sql-set-transaction.html\" title=\"SET TRANSACTION\"\u003e\u003cspan class=\"refentrytitle\"\u003eSET TRANSACTION\u003c/span\u003e\u003c/a\u003e。\u003c/p\u003e\u003cp\u003e除\u003ccode class=\"literal\"\u003eACCESS SHARE\u003c/code\u003e、\u003ccode class=\"literal\"\u003eACCESS EXCLUSIVE\u003c/code\u003e和 \u003ccode class=\"literal\"\u003eSHARE UPDATE EXCLUSIVE\u003c/code\u003e锁模式外，\u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e的锁模式和\u003ccode class=\"command\"\u003eLOCK TABLE\u003c/code\u003e语法与\u003cspan class=\"productname\"\u003eOracle\u003c/span\u003e中的对应语法兼容。\u003c/p\u003e","key":"compatibility","title":"兼容性"}],"sections_same_as":"","synopsis_html":"LOCK [ TABLE ] [ ONLY ] \u003cem class=\"replaceable\"\u003e\u003ccode\u003ename\u003c/code\u003e\u003c/em\u003e [ * ] [, ...] [ IN \u003cem class=\"replaceable\"\u003e\u003ccode\u003elockmode\u003c/code\u003e\u003c/em\u003e MODE ] [ NOWAIT ]\n\n\u003cspan class=\"phrase\"\u003e其中 \u003cem class=\"replaceable\"\u003e\u003ccode\u003elockmode\u003c/code\u003e\u003c/em\u003e 可以是以下之一：\u003c/span\u003e\n\n    ACCESS SHARE | ROW SHARE | ROW EXCLUSIVE | SHARE UPDATE EXCLUSIVE\n    | SHARE | SHARE ROW EXCLUSIVE | EXCLUSIVE | ACCESS EXCLUSIVE","synopsis_text":"LOCK [ TABLE ] [ ONLY ] name [ * ] [, ...] [ IN lockmode MODE ] [ NOWAIT ]\n\n其中 lockmode 可以是以下之一：\n\nACCESS SHARE | ROW SHARE | ROW EXCLUSIVE | SHARE UPDATE EXCLUSIVE\n| SHARE | SHARE ROW EXCLUSIVE | EXCLUSIVE | ACCESS EXCLUSIVE"}},"RequestedLocale":"zh-Hans","Fallback":false,"Versions":["10","11","12","13","14","15","16","17","18","19","20","6.4","6.5","7.0","7.1","7.2","7.3","7.4","8.0","8.1","8.2","8.3","8.4","9.0","9.1","9.2","9.3","9.4","9.5","9.6"],"Locales":["en","zh-Hans"],"Signatures":null,"Spellings":null,"SQLState":null,"Evidence":null}