8000 Add transaction-level advisory locks. · postgres/postgres@62c7bd3 · GitHub
[go: up one dir, main page]
Skip to content

Commit 62c7bd3

Browse files
ItGackyItGacky
committed
Add transaction-level advisory locks.
They share the same locking namespace with the existing session-level advisory locks, but they are automatically released at the end of the current transaction and cannot be released explicitly via unlock functions. Marko Tiikkaja, reviewed by me.
1 parent 87bb2ad commit 62c7bd3

File tree

14 files changed

+814
-71
lines changed
  • utils
  • test/regress

    • 14 files changed

    +814
    -71
    lines changed

    ‎doc/src/sgml/func.sgml

    Lines changed: 111 additions & 17 deletions
    8000
    Original file line numberDiff line numberDiff line change
    @@ -14623,91 +14623,147 @@ SELECT (pg_stat_file('filename')).modification;
    1462314623
    <literal><function>pg_advisory_lock(<parameter>key</> <type>bigint</>)</function></literal>
    1462414624
    </entry>
    1462514625
    <entry><type>void</type></entry>
    14626-
    <entry>Obtain exclusive advisory lock</entry>
    14626+
    <entry>Obtain exclusive session level advisory lock</entry>
    1462714627
    </row>
    1462814628
    <row>
    1462914629
    <entry>
    1463014630
    <literal><function>pg_advisory_lock(<parameter>key1</> <type>int</>, <parameter>key2</> <type>int</>)</function></literal>
    1463114631
    </entry>
    1463214632
    <entry><type>void</type></entry>
    14633-
    <entry>Obtain exclusive advisory lock</entry>
    14633+
    <entry>Obtain exclusive session level advisory lock</entry>
    1463414634
    </row>
    1463514635
    <row>
    1463614636
    <entry>
    1463714637
    <literal><function>pg_advisory_lock_shared(<parameter>key</> <type>bigint</>)</function></literal>
    1463814638
    </entry>
    1463914639
    <entry><type>void</type></entry>
    14640-
    <entry>Obtain shared advisory lock</entry>
    14640+
    <entry>Obtain shared session level advisory lock</entry>
    1464114641
    </row>
    1464214642
    <row>
    1464314643
    <entry>
    1464414644
    <literal><function>pg_advisory_lock_shared(<parameter>key1</> <type>int</>, <parameter>key2</> <type>int</>)</function></literal>
    1464514645
    </entry>
    1464614646
    <entry><type>void</type></entry>
    14647-
    <entry>Obtain shared advisory lock</entry>
    14647+
    <entry>Obtain shared session level advisory lock</entry>
    1464814648
    </row>
    1464914649
    <row>
    1465014650
    <entry>
    1465114651
    <literal><function>pg_advisory_unlock(<parameter>key</> <type>bigint</>)</function></literal>
    1465214652
    </entry>
    1465314653
    <entry><type>boolean</type></entry>
    14654-
    <entry>Release an exclusive advisory lock</entry>
    14654+
    <entry>Release an exclusive session level advisory lock</entry>
    1465514655
    </row>
    1465614656
    <row>
    1465714657
    <entry>
    1465814658
    <literal><function>pg_advisory_unlock(<parameter>key1</> <type>int</>, <parameter>key2</> <type>int</>)</function></literal>
    1465914659
    </entry>
    1466014660
    <entry><type>boolean</type></entry>
    14661-
    <entry>Release an exclusive advisory lock</entry>
    14661+
    <entry>Release an exclusive session level advisory lock</entry>
    1466214662
    </row>
    1466314663
    <row>
    1466414664
    <entry>
    1466514665
    <literal><function>pg_advisory_unlock_all()</function></literal>
    1466614666
    </entry>
    1466714667
    <entry><type>void</type></entry>
    14668-
    <entry>Release all advisory locks held by the current session</entry>
    14668+
    <entry>Release all session level advisory locks held by the current session</entry>
    1466914669
    </row>
    1467014670
    <row>
    1467114671
    <entry>
    1467214672
    <literal><function>pg_advisory_unlock_shared(<parameter>key</> <type>bigint</>)</function></literal>
    1467314673
    </entry>
    1467414674
    <entry><type>boolean</type></entry>
    14675-
    <entry>Release a shared advisory lock</entry>
    14675+
    <entry>Release a shared session level advisory lock</entry>
    1467614676
    </row>
    1467714677
    <row>
    1467814678
    <entry>
    1467914679
    <literal><function>pg_advisory_unlock_shared(<parameter>key1</> <type>int</>, <parameter>key2</> <type>int</>)</function></literal>
    1468014680
    </entry>
    1468114681
    <entry><type>boolean</type></entry>
    14682-
    <entry>Release a shared advisory lock</entry>
    14682+
    <entry>Release a shared session level advisory lock</entry>
    14683+
    </row>
    14684+
    <row>
    14685+
    <entry>
    14686+
    <literal><function>pg_advisory_xact_lock(<parameter>key</> <type>bigint</>)</function></literal>
    14687+
    </entry>
    14688+
    <entry><type>void</type></entry>
    14689+
    <entry>Obtain exclusive transaction level advisory lock</entry>
    14690+
    </row>
    14691+
    <row>
    14692+
    <entry>
    14693+
    <literal><function>pg_advisory_xact_lock(<parameter>key1</> <type>int</>, <parameter>key2</> <type>int</>)</function></literal>
    14694+
    </entry>
    14695+
    <entry><type>void</type></entry>
    14696+
    <entry>Obtain exclusive transaction level advisory lock</entry>
    14697+
    </row>
    14698+
    <row>
    14699+
    <entry>
    14700+
    <literal><function>pg_advisory_xact_lock_shared(<parameter>key</> <type>bigint</>)</function></literal>
    14701+
    </entry>
    14702+
    <entry><type>void</type></entry>
    14703+
    <entry>Obtain shared transaction level advisory lock</entry>
    14704+
    </row>
    14705+
    <row>
    14706+
    <entry>
    14707+
    <literal><function>pg_advisory_xact_lock_shared(<parameter>key1</> <type>int</>, <parameter>key2</> <type>int</>)</function></literal>
    14708+
    </entry>
    14709+
    <entry><type>void</type></entry>
    14710+
    <entry>Obtain shared advisory lock for the current transaction</entry>
    1468314711
    </row>
    1468414712
    <row>
    1468514713
    <entry>
    1468614714
    <literal><function>pg_try_advisory_lock(<parameter>key</> <type>bigint</>)</function></literal>
    1468714715
    </entry>
    1468814716
    <entry><type>boolean</type></entry>
    14689-
    <entry>Obtain exclusive advisory lock if available</entry>
    14717+
    <entry>Obtain exclusive session level advisory lock if available</entry>
    1469014718
    </row>
    1469114719
    <row>
    1469214720
    <entry>
    1469314721
    <literal><function>pg_try_advisory_lock(<parameter>key1</> <type>int</>, <parameter>key2</> <type>int</>)</function></literal>
    1469414722
    </entry>
    1469514723
    <entry><type>boolean</type></entry>
    14696-
    <entry>Obtain exclusive advisory lock if available</entry>
    14724+
    <entry>Obtain exclusive session level advisory lock if available</entry>
    1469714725
    </row>
    1469814726
    <row>
    1469914727
    <entry>
    1470014728
    <literal><function>pg_try_advisory_lock_shared(<parameter>key</> <type>bigint</>)</function></literal>
    1470114729
    </entry>
    1470214730
    <entry><type>boolean</type></entry>
    14703-
    <entry>Obtain shared advisory lock if available</entry>
    14731+
    <entry>Obtain shared session level advisory lock if available</entry>
    1470414732
    </row>
    1470514733
    <row>
    1470614734
    <entry>
    1470714735
    <literal><function>pg_try_advisory_lock_shared(<parameter>key1</> <type>int</>, <parameter>key2</> <type>int</>)</function></literal>
    1470814736
    </entry>
    1470914737
    <entry><type>boolean</type></entry>
    14710-
    <entry>Obtain shared advisory lock if available</entry>
    14738+
    <entry>Obtain shared session level advisory lock if available</entry>
    14739+
    </row>
    14740+
    <row>
    14741+
    <entry>
    14742+
    <literal><function>pg_try_advisory_xact_lock(<parameter>key</> <type>bigint</>)</function></literal>
    14743+
    </entry>
    14744+
    <entry><type>boolean</type></entry>
    14745+
    <entry>Obtain exclusive transaction level advisory lock if available</entry>
    14746+
    </row>
    14747+
    <row>
    14748+
    <entry>
    14749+
    <literal><function>pg_try_advisory_xact_lock(<parameter>key1</> <type>int</>, <parameter>key2</> <type>int</>)</function></literal>
    14750+
    </entry>
    14751+
    <entry><type>boolean</type></entry>
    14752+
    <entry>Obtain exclusive transaction level advisory lock if available</entry>
    14753+
    </row>
    14754+
    <row>
    14755+
    <entry>
    14756+
    <literal><function>pg_try_advisory_xact_lock_shared(<parameter>key</> <type>bigint</>)</function></literal>
    14757+
    </entry>
    14758+
    <entry><type>boolean</type></entry>
    14759+
    <entry>Obtain shared transaction level advisory lock if available</entry>
    14760+
    </row>
    14761+
    <row>
    14762+
    <entry>
    14763+
    <literal><function>pg_try_advisory_xact_lock_shared(<parameter>key1</> <type>int</>, <parameter>key2</> <type>int</>)</function></literal>
    14764+
    </entry>
    14765+
    <entry><type>boolean</type></entry>
    14766+
    <entry>Obtain shared transaction level advisory lock if available</entry>
    1471114767
    </row>
    1471214768
    </tbody>
    1471314769
    </tgroup>
    @@ -14758,12 +14814,50 @@ SELECT (pg_stat_file('filename')).modification;
    1475814814
    a shared rather than an exclusive lock.
    1475914815
    </para>
    1476014816

    14817+
    <indexterm>
    14818+
    <primary>pg_advisory_xact_lock</primary>
    14819+
    </indexterm>
    14820+
    <para>
    14821+
    <function>pg_advisory_xact_lock</> works the same as
    14822+
    <function>pg_advisory_lock</>, expect the lock is automatically released
    14823+
    at the end of the current transaction and can not be released explicitly.
    14824+
    </para>
    14825+
    14826+
    <indexterm>
    14827+
    <primary>pg_advisory_xact_lock_shared</primary>
    14828+
    </indexterm>
    14829+
    <para>
    14830+
    <function>pg_advisory_xact_lock_shared</> works the same as
    14831+
    <function>pg_advisory_lock_shared</>, expect the lock is automatically released
    14832+
    at the end of the current transaction and can not be released explicitly.
    14833+
    </para>
    14834+
    14835+
    <indexterm>
    14836+
    <primary>pg_try_advisory_xact_lock</primary>
    14837+
    </indexterm>
    14838+
    <para>
    14839+
    <function>pg_try_advisory_xact_lock</> works the same as
    14840+
    <function>pg_try_advisory_lock</>, expect the lock, if acquired,
    14841+
    is automatically released at the end of the current transaction and
    14842+
    can not be released explicitly.
    14843+
    </para>
    14844+
    14845+
    <indexterm>
    14846+
    <primary>pg_try_advisory_xact_lock_shared</primary>
    14847+
    </indexterm>
    14848+
    <para>
    14849+
    <function>pg_try_advisory_xact_lock_shared</> works the same as
    14850+
    <function>pg_try_advisory_lock_shared</>, expect the lock, if acquired,
    14851+
    is automatically released at the end of the current transaction and
    14852+
    can not be released explicitly.
    14853+
    </para>
    14854+
    1476114855
    <indexterm>
    1476214856
    <primary>pg_advisory_unlock</primary>
    1476314857
    </indexterm>
    1476414858
    <para>
    1476514859
    <function>pg_advisory_unlock</> will release a previously-acquired
    14766-
    exclusive advisory lock. It
    14860+
    exclusive session level advisory lock. It
    1476714861
    returns <literal>true</> if the lock is successfully released.
    1476814862
    If the lock was not held, it will return <literal>false</>,
    1476914863
    and in addition, an SQL warning will be raised by the server.
    @@ -14775,15 +14869,15 @@ SELECT (pg_stat_file('filename')).modification;
    1477514869
    <para>
    1477614870
    <function>pg_advisory_unlock_shared</> works the same as
    1477714871
    <function>pg_advisory_unlock</>,
    14778-
    except it releases a shared advisory lock.
    14872+
    except it releases a shared session level advisory lock.
    1477914873
    </para>
    1478014874

    1478114875
    <indexterm>
    1478214876
    <primary>pg_advisory_unlock_all</primary>
    1478314877
    </indexterm>
    1478414878
    <para>
    14785-
    <function>pg_advisory_unlock_all</> will release all advisory locks
    14786-
    held by the current session. (This function is implicitly invoked
    14879+
    <function>pg_advisory_unlock_all</> will release all session level advisory
    14880+
    locks held by the current session. (This function is implicitly invoked
    1478714881
    at session end, even if the client disconnects ungracefully.)
    1478814882
    </para>
    1478914883

    ‎doc/src/sgml/mvcc.sgml

    Lines changed: 22 additions & 13 deletions
    Original file line numberDiff line numberDiff line change
    @@ -1199,19 +1199,28 @@ UPDATE accounts SET balance = balance - 100.00 WHERE acctnum = 22222;
    11991199
    called <firstterm>advisory locks</>, because the system does not
    12001200
    enforce their use &mdash; it is up to the application to use them
    12011201
    correctly. Advisory locks can be useful for locking strategies
    1202-
    that are an awkward fit for the MVCC model. Once acquired, an
    1203-
    advisory lock is held until explicitly released or the session ends.
    1204-
    Unlike standard locks, advisory locks do not
    1205-
    honor transaction semantics: a lock acquired during a
    1206-
    transaction that is later rolled back will still be held following the
    1202+
    that are an awkward fit for the MVCC model.</para>
    1203+
    1204+
    <para>
    1205+
    There are two different types of advisory locks in
    1206+
    <productname>PostgreSQL</productname>: session level and transaction level.
    1207+
    Once acquired, a session level advisory lock is held until explicitly
    1208+
    released or the session ends. Unlike standard locks, session level
    1209+
    advisory locks do not honor transaction semantics: a lock acquired during
    1210+
    a transaction that is later rolled back will still be held following the
    12071211
    rollback, and likewise an unlock is effective even if the calling
    1208-
    transaction fails later. The same lock can be acquired multiple times by
    1209-
    its owning process: for each lock request there must be a corresponding
    1210-
    unlock request before the lock is actually released. (If a session
    1211-
    already holds a given lock, additional requests will always succeed, even
    1212-
    if other sessions are awaiting the lock.) Like all locks in
    1213-
    <productname>PostgreSQL</productname>, a complete list of advisory
    1214-
    locks currently held by any session can be found in the
    1212+
    transaction fails later. The same session level lock can be acquired
    1213+
    multiple times by its owning process: for each lock request there must be
    1214+
    a corresponding unlock request before the lock is actually released. (If a
    1215+
    session already holds a given lock, additional requests will always succeed,
    1216+
    even if other sessions are awaiting the lock.) Transaction level locks on
    1217+
    the other hand behave more like regular locks; they are automatically
    1218+
    released at the end of the transaction, and can not be explicitly unlocked.
    1219+
    Session and transaction level locks share the same lock space, which means
    1220+
    that a transaction level lock will prevent another session from obtaining
    1221+
    a session level lock on that same resource and vice versa.
    1222+
    Like all locks in <productname>PostgreSQL</productname>, a complete list of
    1223+
    advisory locks currently held by any session can be found in the
    12151224
    <link linkend="view-pg-locks"><structname>pg_locks</structname></link>
    12161225
    system view.
    12171226
    </para>
    @@ -1233,7 +1242,7 @@ UPDATE accounts SET balance = balance - 100.00 WHERE acctnum = 22222;
    12331242
    strategies typical of so called <quote>flat file</> data management
    12341243
    systems.
    12351244
    While a flag stored in a table could be used for the same purpose,
    1236-
    advisory locks are faster, avoid MVCC bloat, and are automatically
    1245+
    advisory locks are faster, avoid MVCC bloat, and can be automatically
    12371246
    cleaned up by the server at the end of the session.
    12381247
    In certain cases using this advisory locking method, especially in queries
    12391248
    involving explicit ordering and <literal>LIMIT</> clauses, care must be

    ‎src/backend/storage/lmgr/README

    Lines changed: 7 additions & 4 deletions
    Original file line numberDiff line numberDiff line change
    @@ -505,7 +505,7 @@ User Locks
    505505
    ----------
    506506

    507507
    User locks are handled totally on the application side as long term
    508-
    cooperative locks which extend beyond the normal transaction boundaries.
    508+
    cooperative locks which may extend beyond the normal transaction boundaries.
    509509
    Their purpose is to indicate to an application that someone is `working'
    510510
    on an item. So it is possible to put an user lock on a tuple's oid,
    511511
    retrieve the tuple, work on it for an hour and then update it and remove
    @@ -516,9 +516,12 @@ level by someone.
    516516
    User locks and normal locks are completely orthogonal and they don't
    517517
    interfere with each other.
    518518

    519-
    User locks are always held as session locks, so that they are not released at
    520-
    transaction end. They must be released explicitly by the application --- but
    521-
    they are released automatically when a backend terminates.
    519+
    There are two types of user locks: session level and transaction level.
    520+
    Session level user locks are not released at transaction end. They must
    521+
    be released explicitly by the application --- but they are released
    522+
    automatically when a backend terminates. On the other hand, transaction
    523+
    level user locks are released automatically at the end of the transaction
    524+
    as like as other normal locks.
    522525

    523526
    Locking during Hot Standby
    524527
    --------------------------

    0 commit comments

    Comments
     (0)
    0