I've been working on the TODO list item "Add SHOW command to display locks". The
code is basically finished, but I'd like to make sure the user interface is okay
with everyone before I send it in to -patches (if you're interested, the patch
is attached).
Rather than adding another SHOW command, I think using a table function
is a better idea. That's because the information returned by the lock
listing code will often need to be correlated with other information in
the system catalogs, or sorted/aggregated in various ways (e.g. "show me
the names of all locked relations", or "show me the relation with the most
AccessShareLocks'"). Written as a table function, the lock listing code
itself can be fairly simple, and the DBA can write the necessary SQL
queries to produce the information he needs. It also makes it easier to
parse the lock status information, if you're writing (for example) a
GUI admin tool.
Usage examples:
Basic information returned from function:
nconway=# select * from show_locks();
relation | database | backendpid | mode | isgranted
----------+----------+------------+-----------------+-----------
16575 | 16689 | 13091 | AccessShareLock | t
376 | 0 | 13091 | ExclusiveLock | t
After creating a simple relation and starting 2 transactions, one
of which has acquired the lock and one which is waiting on it:
nconway=# select l.backendpid, l.mode, l.isgranted from show_locks() l,
pg_class c where l.relation = c.oid and c.relname = 'a';
backendpid | mode | isgranted
------------+-----------------------+-----------
13098 | RowExclusiveLock | t
13108 | ShareRowExclusiveLock | f
During a 128 client pgbench run:
pgbench1=# select c.relname, count(l.isgranted) from show_locks() l,
pg_class c where c.oid = l.relation group by c.relname
order by count desc;
relname | count
---------------------+-------
accounts | 1081
tellers | 718
pg_xactlock | 337
branches | 208
history | 4
pg_class | 3
__show_locks_result | 1
And so on -- I think you get the idea.
Regarding performance, the only performance-critical aspect of the patch
is the place where we need to acquire the LockMgrLock, to ensure that
we get a consistent view of data from the lock manager's hash tables.
The patch is designed so that this lock is held for as short a period
as possible: the lock is acquired, the data is copied from shared memory
to local memory, the lock is released, and then the data is processed.
Any suggestions on how to optimize performance any further would be
welcome.
Let me know if there are any objections or suggestions for improvement.
In particular, should we provide some pre-defined views that correlate
the show_locks() data with data from the system catalogs? And if so,
which views should be pre-defined?
Also, should locks on special relations (e.g. pg_xactlock) or on
system catalogs be shown?
Cheers,
Neil
--
Neil Conway <[EMAIL PROTECTED]>
PGP Key ID: DB3C29FC
Index: src/backend/storage/lmgr/lmgr.c
===================================================================
RCS file: /var/lib/cvs/pgsql/src/backend/storage/lmgr/lmgr.c,v
retrieving revision 1.53
diff -c -r1.53 lmgr.c
*** src/backend/storage/lmgr/lmgr.c 20 Jun 2002 20:29:35 -0000 1.53
--- src/backend/storage/lmgr/lmgr.c 18 Jul 2002 17:38:13 -0000
***************
*** 104,118 ****
if (!(LockTableId))
elog(ERROR, "InitLockTable: couldn't initialize lock table");
- #ifdef USER_LOCKS
-
/*
* Allocate another tableId for long-term locks
*/
LongTermTableId = LockMethodTableRename(LockTableId);
if (!(LongTermTableId))
elog(ERROR, "InitLockTable: couldn't rename long-term lock table");
- #endif
return LockTableId;
}
--- 104,115 ----
Index: src/backend/storage/lmgr/lock.c
===================================================================
RCS file: /var/lib/cvs/pgsql/src/backend/storage/lmgr/lock.c,v
retrieving revision 1.108
diff -c -r1.108 lock.c
*** src/backend/storage/lmgr/lock.c 20 Jun 2002 20:29:35 -0000 1.108
--- src/backend/storage/lmgr/lock.c 18 Jul 2002 17:38:13 -0000
***************
*** 1053,1063 ****
if (!holder)
{
LWLockRelease(masterLock);
- #ifdef USER_LOCKS
if (lockmethod == USER_LOCKMETHOD)
elog(WARNING, "LockRelease: no lock with this tag");
else
- #endif
elog(WARNING, "LockRelease: holder table corrupted");
return FALSE;
}
--- 1053,1061 ----
***************
*** 1373,1378 ****
--- 1371,1442 ----
return size;
}
+ /*
+ * GetLockStatusData - Return a summary of the lock manager's internal
+ * status, for use in a user-level statistical reporting function.
+ *
+ * This function should be passed a pointer to a LockData struct. It fills
+ * the structure with the appropriate information and returns. The goal
+ * is to hold the LockMgrLock for as short a time as possible; thus, the
+ * function simply makes a copy of the necessary data and releases the
+ * lock, allowing the caller to contemplate and format the data for
+ * as long as it pleases.
+ */
+ void
+ GetLockStatusData(LockData *data)
+ {
+ HTAB *holderTable;
+ HOLDER *holder;
+ int i = 0;
+ HASH_SEQ_STATUS seqstat;
+
+ data->currIdx = 0;
+
+ LWLockAcquire(LockMgrLock, LW_EXCLUSIVE);
+
+ holderTable = LockMethodTable[DEFAULT_LOCKMETHOD]->holderHash;
+
+ data->nelements = holderTable->hctl->nentries;
+
+ data->holders = (HOLDER **) palloc(sizeof(HOLDER *) * data->nelements);
+ data->procs = (PGPROC **) palloc(sizeof(PGPROC *) * data->nelements);
+ data->locks = (LOCK **) palloc(sizeof(LOCK *) * data->nelements);
+
+ hash_seq_init(&seqstat, holderTable);
+
+ while ( (holder = hash_seq_search(&seqstat)) )
+ {
+ PGPROC *proc;
+ LOCK *lock;
+
+ data->holders[i] = (HOLDER *) palloc(sizeof(HOLDER));
+ data->procs[i] = (PGPROC *) palloc(sizeof(PGPROC));
+ data->locks[i] = (LOCK *) palloc(sizeof(LOCK));
+
+ /* Only do a shallow copy */
+ memcpy(data->holders[i], holder, sizeof(HOLDER));
+
+ proc = (PGPROC *) MAKE_PTR(holder->tag.proc);
+
+ memcpy(data->procs[i], proc, sizeof(PGPROC));
+
+ lock = (LOCK *) MAKE_PTR(holder->tag.lock);
+
+ memcpy(data->locks[i], lock, sizeof(LOCK));
+
+ i++;
+ }
+
+ Assert(i == data->nelements);
+
+ LWLockRelease(LockMgrLock);
+ }
+
+ char *
+ GetLockmodeName(LOCKMODE mode)
+ {
+ return lock_mode_names[mode];
+ }
#ifdef LOCK_DEBUG
/*
Index: src/backend/storage/lmgr/proc.c
===================================================================
RCS file: /var/lib/cvs/pgsql/src/backend/storage/lmgr/proc.c,v
retrieving revision 1.122
diff -c -r1.122 proc.c
*** src/backend/storage/lmgr/proc.c 13 Jul 2002 01:02:14 -0000 1.122
--- src/backend/storage/lmgr/proc.c 18 Jul 2002 17:38:13 -0000
***************
*** 397,406 ****
/* Remove from the standard lock table */
LockReleaseAll(DEFAULT_LOCKMETHOD, MyProc, true, InvalidTransactionId);
- #ifdef USER_LOCKS
/* Remove from the user lock table */
LockReleaseAll(USER_LOCKMETHOD, MyProc, true, InvalidTransactionId);
- #endif
SpinLockAcquire(ProcStructLock);
--- 397,404 ----
Index: src/backend/tcop/utility.c
===================================================================
RCS file: /var/lib/cvs/pgsql/src/backend/tcop/utility.c,v
retrieving revision 1.163
diff -c -r1.163 utility.c
*** src/backend/tcop/utility.c 18 Jul 2002 16:47:25 -0000 1.163
--- src/backend/tcop/utility.c 18 Jul 2002 17:38:13 -0000
***************
*** 217,224 ****
break;
/*
! * ******************************** portal manipulation
********************************
! *
*/
case T_ClosePortalStmt:
{
--- 217,223 ----
break;
/*
! * ************************* portal manipulation
***************************
*/
case T_ClosePortalStmt:
{
Index: src/backend/utils/adt/Makefile
===================================================================
RCS file: /var/lib/cvs/pgsql/src/backend/utils/adt/Makefile,v
retrieving revision 1.51
diff -c -r1.51 Makefile
*** src/backend/utils/adt/Makefile 4 Oct 2001 04:13:40 -0000 1.51
--- src/backend/utils/adt/Makefile 18 Jul 2002 17:38:13 -0000
***************
*** 17,23 ****
OBJS = acl.o arrayfuncs.o arrayutils.o bool.o cash.o char.o \
date.o datetime.o datum.o float.o format_type.o \
! geo_ops.o geo_selfuncs.o int.o int8.o like.o \
misc.o nabstime.o name.o not_in.o numeric.o numutils.o \
oid.o oracle_compat.o \
regexp.o regproc.o ruleutils.o selfuncs.o sets.o \
--- 17,23 ----
OBJS = acl.o arrayfuncs.o arrayutils.o bool.o cash.o char.o \
date.o datetime.o datum.o float.o format_type.o \
! geo_ops.o geo_selfuncs.o int.o int8.o like.o lockfuncs.o \
misc.o nabstime.o name.o not_in.o numeric.o numutils.o \
oid.o oracle_compat.o \
regexp.o regproc.o ruleutils.o selfuncs.o sets.o \
Index: src/bin/initdb/initdb.sh
===================================================================
RCS file: /var/lib/cvs/pgsql/src/bin/initdb/initdb.sh,v
retrieving revision 1.160
diff -c -r1.160 initdb.sh
*** src/bin/initdb/initdb.sh 18 Jul 2002 16:47:25 -0000 1.160
--- src/bin/initdb/initdb.sh 18 Jul 2002 17:38:13 -0000
***************
*** 763,770 ****
FROM pg_class C LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace) \
WHERE C.relkind = 'v';
- -- XXX why does pg_tables include sequences?
-
CREATE VIEW pg_tables AS \
SELECT \
C.relname AS tablename, \
--- 763,768 ----
***************
*** 969,974 ****
--- 967,985 ----
pg_stat_get_db_blocks_hit(D.oid) AS blks_read, \
pg_stat_get_db_blocks_hit(D.oid) AS blks_hit \
FROM pg_database D;
+
+ CREATE VIEW __show_locks_result AS \
+ SELECT \
+ ''::oid AS relation, \
+ ''::oid AS database, \
+ ''::int4 AS backendpid, \
+ ''::text AS mode, \
+ NULL::bool AS isgranted;
+
+ UPDATE pg_proc SET \
+ prorettype = (SELECT oid FROM pg_type \
+ WHERE typname = '__show_locks_result') \
+ WHERE proname = 'show_locks';
EOF
if [ "$?" -ne 0 ]; then
Index: src/include/pg_config.h.in
===================================================================
RCS file: /var/lib/cvs/pgsql/src/include/pg_config.h.in,v
retrieving revision 1.24
diff -c -r1.24 pg_config.h.in
*** src/include/pg_config.h.in 5 May 2002 00:03:29 -0000 1.24
--- src/include/pg_config.h.in 18 Jul 2002 17:38:13 -0000
***************
*** 180,194 ****
/* #define TCL_ARRAYS */
/*
- * User locks are handled totally on the application side as long term
- * cooperative locks which extend beyond the normal transaction boundaries.
- * Their purpose is to indicate to an application that someone is `working'
- * on an item. Define this flag to enable user locks. You will need the
- * loadable module user-locks.c to use this feature.
- */
- #define USER_LOCKS
-
- /*
* Define this if you want psql to _always_ ask for a username and a password
* for password authentication.
*/
--- 180,185 ----
Index: src/include/catalog/catversion.h
===================================================================
RCS file: /var/lib/cvs/pgsql/src/include/catalog/catversion.h,v
retrieving revision 1.140
diff -c -r1.140 catversion.h
*** src/include/catalog/catversion.h 15 Jul 2002 16:33:31 -0000 1.140
--- src/include/catalog/catversion.h 18 Jul 2002 17:38:13 -0000
***************
*** 53,58 ****
*/
/* yyyymmddN */
! #define CATALOG_VERSION_NO 200207141
#endif
--- 53,58 ----
*/
/* yyyymmddN */
! #define CATALOG_VERSION_NO 200207181
#endif
Index: src/include/catalog/pg_proc.h
===================================================================
RCS file: /var/lib/cvs/pgsql/src/include/catalog/pg_proc.h,v
retrieving revision 1.243
diff -c -r1.243 pg_proc.h
*** src/include/catalog/pg_proc.h 20 Jun 2002 20:29:44 -0000 1.243
--- src/include/catalog/pg_proc.h 18 Jul 2002 17:38:13 -0000
***************
*** 2681,2686 ****
--- 2681,2689 ----
DATA(insert OID = 1915 ( numeric_uplus PGNSP PGUID 12 f f f t f i 1 1700 "1700"
100 0 0 100 numeric_uplus - _null_ ));
DESCR("unary plus");
+ DATA(insert OID = 1920 ( show_locks PGNSP PGUID 12 f f f t t v 0 0 "0" 100 0 0
+100 show_locks_srf - _null_ ));
+ DESCR("view system lock information");
+
DATA(insert OID = 1922 ( has_table_privilege PGNSP PGUID 12 f f f t f s
3 16 "19 25 25" 100 0 0 100 has_table_privilege_name_name - _null_ ));
DESCR("user privilege on relation by username, relname");
DATA(insert OID = 1923 ( has_table_privilege PGNSP PGUID 12 f f f t f s
3 16 "19 26 25" 100 0 0 100 has_table_privilege_name_id - _null_ ));
Index: src/include/storage/lock.h
===================================================================
RCS file: /var/lib/cvs/pgsql/src/include/storage/lock.h,v
retrieving revision 1.61
diff -c -r1.61 lock.h
*** src/include/storage/lock.h 20 Jun 2002 20:29:52 -0000 1.61
--- src/include/storage/lock.h 18 Jul 2002 17:38:13 -0000
***************
*** 59,66 ****
#define USER_LOCKMETHOD 2
/*
! * There is normally only one lock method, the default one.
! * If user locks are enabled, an additional lock method is present.
*
* LOCKMETHODCTL and LOCKMETHODTABLE are split because the first lives
* in shared memory. (There isn't any really good reason for the split.)
--- 59,66 ----
#define USER_LOCKMETHOD 2
/*
! * There are currently two lock methods: the default method, and the method
! * used for user locks.
*
* LOCKMETHODCTL and LOCKMETHODTABLE are split because the first lives
* in shared memory. (There isn't any really good reason for the split.)
***************
*** 222,227 ****
--- 222,242 ----
#define HOLDER_LOCKMETHOD(holder) \
(((LOCK *) MAKE_PTR((holder).tag.lock))->tag.lockmethod)
+ /*
+ * This struct is used to encapsulate information passed from lmgr
+ * internals to the lock listing statistical functions (lockfuncs.c).
+ * It's just a convenient bundle of other lock.h structures. All
+ * the information at a given index (holders[i], procs[i], locks[i])
+ * is related.
+ */
+ typedef struct
+ {
+ int nelements; /* The length of holders, procs, & locks */
+ int currIdx; /* Current element being examined */
+ HOLDER **holders;
+ PGPROC **procs;
+ LOCK **locks;
+ } LockData;
/*
* function prototypes
***************
*** 246,251 ****
--- 261,268 ----
extern int LockShmemSize(int maxBackends);
extern bool DeadLockCheck(PGPROC *proc);
extern void InitDeadLockChecking(void);
+ extern void GetLockStatusData(LockData *data);
+ extern char *GetLockmodeName(LOCKMODE mode);
#ifdef LOCK_DEBUG
extern void DumpLocks(void);
Index: src/include/storage/shmem.h
===================================================================
RCS file: /var/lib/cvs/pgsql/src/include/storage/shmem.h,v
retrieving revision 1.37
diff -c -r1.37 shmem.h
*** src/include/storage/shmem.h 20 Jun 2002 20:29:52 -0000 1.37
--- src/include/storage/shmem.h 18 Jul 2002 17:38:13 -0000
***************
*** 53,60 ****
#define SHM_OFFSET_VALID(xx_offs)\
(((xx_offs) != 0) && ((xx_offs) != INVALID_OFFSET))
!
! /* shmemqueue.c */
typedef struct SHM_QUEUE
{
SHMEM_OFFSET prev;
--- 53,59 ----
#define SHM_OFFSET_VALID(xx_offs)\
(((xx_offs) != 0) && ((xx_offs) != INVALID_OFFSET))
! /* shmqueue.c */
typedef struct SHM_QUEUE
{
SHMEM_OFFSET prev;
Index: src/test/regress/expected/rules.out
===================================================================
RCS file: /var/lib/cvs/pgsql/src/test/regress/expected/rules.out,v
retrieving revision 1.53
diff -c -r1.53 rules.out
*** src/test/regress/expected/rules.out 3 May 2002 00:32:19 -0000 1.53
--- src/test/regress/expected/rules.out 18 Jul 2002 17:38:13 -0000
***************
*** 1266,1271 ****
--- 1266,1272 ----
SELECT viewname, definition FROM pg_views ORDER BY viewname;
viewname |
definition
--------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ __show_locks_result | SELECT 0::oid AS relation, 0::oid AS "database", 0 AS
+backendpid, ''::text AS "mode", NULL::boolean AS isgranted;
iexit | SELECT ih.name, ih.thepath, interpt_pp(ih.thepath,
r.thepath) AS exit FROM ihighway ih, ramp r WHERE (ih.thepath ## r.thepath);
pg_indexes | SELECT c.relname AS tablename, i.relname AS indexname,
pg_get_indexdef(x.indexrelid) AS indexdef FROM pg_index x, pg_class c, pg_class i
WHERE ((((c.relkind = 'r'::"char") AND (i.relkind = 'i'::"char")) AND (c.oid =
x.indrelid)) AND (i.oid = x.indexrelid));
pg_rules | SELECT n.nspname AS schemaname, c.relname AS tablename,
r.rulename, pg_get_ruledef(r.oid) AS definition FROM ((pg_rewrite r JOIN pg_class c ON
((c.oid = r.ev_class))) LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) WHERE
(r.rulename <> '_RETURN'::name);
***************
*** 1304,1310 ****
shoelace_obsolete | SELECT shoelace.sl_name, shoelace.sl_avail,
shoelace.sl_color, shoelace.sl_len, shoelace.sl_unit, shoelace.sl_len_cm FROM shoelace
WHERE (NOT (EXISTS (SELECT shoe.shoename FROM shoe WHERE (shoe.slcolor =
shoelace.sl_color))));
street | SELECT r.name, r.thepath, c.cname FROM ONLY road r,
real_city c WHERE (c.outline ## r.thepath);
toyemp | SELECT emp.name, emp.age, emp."location", (12 *
emp.salary) AS annualsal FROM emp;
! (38 rows)
SELECT tablename, rulename, definition FROM pg_rules
ORDER BY tablename, rulename;
--- 1305,1311 ----
shoelace_obsolete | SELECT shoelace.sl_name, shoelace.sl_avail,
shoelace.sl_color, shoelace.sl_len, shoelace.sl_unit, shoelace.sl_len_cm FROM shoelace
WHERE (NOT (EXISTS (SELECT shoe.shoename FROM shoe WHERE (shoe.slcolor =
shoelace.sl_color))));
street | SELECT r.name, r.thepath, c.cname FROM ONLY road r,
real_city c WHERE (c.outline ## r.thepath);
toyemp | SELECT emp.name, emp.age, emp."location", (12 *
emp.salary) AS annualsal FROM emp;
! (39 rows)
SELECT tablename, rulename, definition FROM pg_rules
ORDER BY tablename, rulename;
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
