Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit52f5d57

Browse files
committed
Create a function to reliably identify which sessions block which others.
This patch introduces "pg_blocking_pids(int) returns int[]", which returnsthe PIDs of any sessions that are blocking the session with the given PID.Historically people have obtained such information using a self-join onthe pg_locks view, but it's unreasonably tedious to do it that way with anymodicum of correctness, and the addition of parallel queries has prettymuch broken that approach altogether. (Given some more columns in the viewthan there are today, you could imagine handling parallel-query cases witha 4-way join; but ugh.)The new function has the following behaviors that are painful or impossibleto get right via pg_locks:1. Correctly understands which lock modes block which other ones.2. In soft-block situations (two processes both waiting for conflicting lockmodes), only the one that's in front in the wait queue is reported toblock the other.3. In parallel-query cases, reports all sessions blocking any member ofthe given PID's lock group, and reports a session by naming its leaderprocess's PID, which will be the pg_backend_pid() value visible toclients.The motivation for doing this right now is mostly to fix the isolationtests. Commit38f8bdc lobotomizedisolationtester's is-it-waiting query by removing its ability to recognizenonconflicting lock modes, as a crude workaround for the inability tohandle soft-block situations properly. But even without the lock modetests, the old query was excessively slow, particularly inCLOBBER_CACHE_ALWAYS builds; some of our buildfarm animals fail the newdeadlock-hard test because the deadlock timeout elapses before they canprobe the waiting status of all eight sessions. Replacing the pg_locksself-join with use of pg_blocking_pids() is not only much more correct, buta lot faster: I measure it at about 9X faster in a typical dev build withAsserts, and 3X faster in CLOBBER_CACHE_ALWAYS builds. That should provideenough headroom for the slower CLOBBER_CACHE_ALWAYS animals to pass thetest, without having to lengthen deadlock_timeout yet more and thus slowdown the test for everyone else.
1 parent73bf871 commit52f5d57

File tree

11 files changed

+470
-54
lines changed

11 files changed

+470
-54
lines changed

‎doc/src/sgml/catalogs.sgml

Lines changed: 35 additions & 21 deletions
Original file line numberDiff line numberDiff line change
@@ -7376,7 +7376,7 @@
73767376

73777377
<row>
73787378
<entry><link linkend="view-pg-locks"><structname>pg_locks</structname></link></entry>
7379-
<entry>currently heldlocks</entry>
7379+
<entry>lockscurrently heldor awaited</entry>
73807380
</row>
73817381

73827382
<row>
@@ -8015,16 +8015,16 @@
80158015

80168016
<para>
80178017
The view <structname>pg_locks</structname> provides access to
8018-
information about the locks held byopen transactions within the
8018+
information about the locks held byactive processes within the
80198019
database server. See <xref linkend="mvcc"> for more discussion
80208020
of locking.
80218021
</para>
80228022

80238023
<para>
80248024
<structname>pg_locks</structname> contains one row per active lockable
8025-
object, requested lock mode, and relevanttransaction. Thus, the same
8025+
object, requested lock mode, and relevantprocess. Thus, the same
80268026
lockable object might
8027-
appear many times, if multipletransactions are holding or waiting
8027+
appear many times, if multipleprocesses are holding or waiting
80288028
for locks on it. However, an object that currently has no locks on it
80298029
will not appear at all.
80308030
</para>
@@ -8200,31 +8200,31 @@
82008200

82018201
<para>
82028202
<structfield>granted</structfield> is true in a row representing a lock
8203-
held by the indicatedtransaction. False indicates that thistransaction is
8204-
currently waiting to acquire this lock, which implies thatsome other
8205-
transactionis holding a conflicting lock mode on the same lockable object.
8206-
The waitingtransaction will sleep until the other lock is released (or a
8207-
deadlock situation is detected). A singletransaction can be waiting to
8208-
acquire at most one lock at a time.
8203+
held by the indicatedprocess. False indicates that thisprocess is
8204+
currently waiting to acquire this lock, which implies thatat least one
8205+
other processis holdingor waiting fora conflicting lock mode on the same
8206+
lockable object.The waitingprocess will sleep until the other lock is
8207+
released (or adeadlock situation is detected).A singleprocess can be
8208+
waiting toacquire at most one lock at a time.
82098209
</para>
82108210

82118211
<para>
8212-
Every transaction holds an exclusive lock on its virtual transaction ID for
8213-
its entire duration. If a permanent ID is assigned to the transaction
8214-
(which normally happens only if the transaction changes the state of the
8215-
database), it also holds an exclusive lock onits permanent transaction ID
8216-
until it ends. Whenone transaction finds it necessary to wait specifically
8217-
for another transaction, it does so by attempting toacquire share lock on
8218-
the other transaction ID (either virtual or permanent ID depending on the
8219-
situation). That will succeed only whentheother transaction
8220-
terminates and releases its locks.
8212+
Throughout running a transaction, a server process holds an exclusive lock
8213+
on the transaction's virtual transaction ID. If a permanent ID is assigned
8214+
to the transaction(which normally happens only if the transaction changes
8215+
the state of thedatabase), it also holds an exclusive lock onthe
8216+
transaction's permanent transaction IDuntil it ends. Whena process finds
8217+
it necessary to wait specifically for another transaction toend, it does
8218+
so by attempting to acquire share lock on the other transaction's ID
8219+
(either virtual or permanent ID depending onthesituation). That will
8220+
succeed only when the other transactionterminates and releases its locks.
82218221
</para>
82228222

82238223
<para>
82248224
Although tuples are a lockable type of object,
82258225
information about row-level locks is stored on disk, not in memory,
82268226
and therefore row-level locks normally do not appear in this view.
8227-
If atransaction is waiting for a
8227+
If aprocess is waiting for a
82288228
row-level lock, it will usually appear in the view as waiting for the
82298229
permanent transaction ID of the current holder of that row lock.
82308230
</para>
@@ -8260,7 +8260,7 @@
82608260
<structfield>pid</structfield> column of the <link
82618261
linkend="pg-stat-activity-view"><structname>pg_stat_activity</structname></link>
82628262
view to get more
8263-
information on the session holding orwaiting to hold each lock,
8263+
information on the session holding orawaiting each lock,
82648264
for example
82658265
<programlisting>
82668266
SELECT * FROM pg_locks pl LEFT JOIN pg_stat_activity psa
@@ -8280,6 +8280,20 @@ SELECT * FROM pg_locks pl LEFT JOIN pg_prepared_xacts ppx
82808280
</programlisting>
82818281
</para>
82828282

8283+
<para>
8284+
While it is possible to obtain information about which processes block
8285+
which other processes by joining <structname>pg_locks</structname> against
8286+
itself, this is very difficult to get right in detail. Such a query would
8287+
have to encode knowledge about which lock modes conflict with which
8288+
others. Worse, the <structname>pg_locks</structname> view does not expose
8289+
information about which processes are ahead of which others in lock wait
8290+
queues, nor information about which processes are parallel workers running
8291+
on behalf of which other client sessions. It is better to use
8292+
the <function>pg_blocking_pids()</> function
8293+
(see <xref linkend="functions-info-session-table">) to identify which
8294+
process(es) a waiting process is blocked behind.
8295+
</para>
8296+
82838297
<para>
82848298
The <structname>pg_locks</structname> view displays data from both the
82858299
regular lock manager and the predicate lock manager, which are

‎doc/src/sgml/func.sgml

Lines changed: 29 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -14996,6 +14996,12 @@ SELECT * FROM pg_ls_dir('.') WITH ORDINALITY AS t(ls,n);
1499614996
</entry>
1499714997
</row>
1499814998

14999+
<row>
15000+
<entry><literal><function>pg_blocking_pids(<type>int</type>)</function></literal></entry>
15001+
<entry><type>int[]</type></entry>
15002+
<entry>Process ID(s) that are blocking specified server process ID</entry>
15003+
</row>
15004+
1499915005
<row>
1500015006
<entry><literal><function>pg_conf_load_time()</function></literal></entry>
1500115007
<entry><type>timestamp with time zone</type></entry>
@@ -15183,6 +15189,29 @@ SET search_path TO <replaceable>schema</> <optional>, <replaceable>schema</>, ..
1518315189
Unix-domain socket.
1518415190
</para>
1518515191

15192+
<indexterm>
15193+
<primary>pg_blocking_pids</primary>
15194+
</indexterm>
15195+
15196+
<para>
15197+
<function>pg_blocking_pids</function> returns an array of the process IDs
15198+
of the sessions that are blocking the server process with the specified
15199+
process ID, or an empty array if there is no such server process or it is
15200+
not blocked. One server process blocks another if it either holds a lock
15201+
that conflicts with the blocked process's lock request (hard block), or is
15202+
waiting for a lock that would conflict with the blocked process's lock
15203+
request and is ahead of it in the wait queue (soft block). When using
15204+
parallel queries the result always lists client-visible process IDs (that
15205+
is, <function>pg_backend_pid</> results) even if the actual lock is held
15206+
or awaited by a child worker process. As a result of that, there may be
15207+
duplicated PIDs in the result. Also note that when a prepared transaction
15208+
holds a conflicting lock, it will be represented by a zero process ID in
15209+
the result of this function.
15210+
Frequent calls to this function could have some impact on database
15211+
performance, because it needs exclusive access to the lock manager's
15212+
shared state for a short time.
15213+
</para>
15214+
1518615215
<indexterm>
1518715216
<primary>pg_conf_load_time</primary>
1518815217
</indexterm>

‎src/backend/storage/ipc/procarray.c

Lines changed: 23 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -2312,6 +2312,29 @@ HaveVirtualXIDsDelayingChkpt(VirtualTransactionId *vxids, int nvxids)
23122312
*/
23132313
PGPROC*
23142314
BackendPidGetProc(intpid)
2315+
{
2316+
PGPROC*result;
2317+
2318+
if (pid==0)/* never match dummy PGPROCs */
2319+
returnNULL;
2320+
2321+
LWLockAcquire(ProcArrayLock,LW_SHARED);
2322+
2323+
result=BackendPidGetProcWithLock(pid);
2324+
2325+
LWLockRelease(ProcArrayLock);
2326+
2327+
returnresult;
2328+
}
2329+
2330+
/*
2331+
* BackendPidGetProcWithLock -- get a backend's PGPROC given its PID
2332+
*
2333+
* Same as above, except caller must be holding ProcArrayLock. The found
2334+
* entry, if any, can be assumed to be valid as long as the lock remains held.
2335+
*/
2336+
PGPROC*
2337+
BackendPidGetProcWithLock(intpid)
23152338
{
23162339
PGPROC*result=NULL;
23172340
ProcArrayStruct*arrayP=procArray;
@@ -2320,8 +2343,6 @@ BackendPidGetProc(int pid)
23202343
if (pid==0)/* never match dummy PGPROCs */
23212344
returnNULL;
23222345

2323-
LWLockAcquire(ProcArrayLock,LW_SHARED);
2324-
23252346
for (index=0;index<arrayP->numProcs;index++)
23262347
{
23272348
PGPROC*proc=&allProcs[arrayP->pgprocnos[index]];
@@ -2333,8 +2354,6 @@ BackendPidGetProc(int pid)
23332354
}
23342355
}
23352356

2336-
LWLockRelease(ProcArrayLock);
2337-
23382357
returnresult;
23392358
}
23402359

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp