从一个实例谈谈postgresql索引锁

最近客户在使用我司开发的数据库时，报告了如下问题(也不能算是问题，就是疑惑吧)，环境如下：

OS : Red Hat Enterprise Linux Server release 6.7 (Santiago) 
Kernel : 2.6.32-573.el6.x86_64
PostgreSQL : PostgreSQL 9.6.2

执行准备工作：

postgres=# create table test (id int, data text); 
CREATE TABLE
postgres=# insert into test select a, 'Test data No ' || a from
generate_series(1,10000000) as a;
INSERT 0 10000000
postgres=# create index on test (id);
CREATE INDEX
postgres=# vacuum analyze verbose test;
......

然后，开启一个事务，在该事务中查询test表：

postgres=# begin ;
BEGIN
postgres=# select data from test where data = 'Test data No 1';
      data
----------------
 Test data No 1
(1 row)

在开另一个psql console，查询锁的情况：

postgres=# select c.oid, c.relname, l.locktype, l.pid, l.mode ,a.query 
from (pg_class as c inner join pg_locks as l on (c.oid = l.relation)) 
inner join pg_stat_activity as a on (l.pid = a.pid) where relname like 'test%'
order by pid;
  oid  |   relname   | locktype |  pid  |      mode       |
            query
-------+-------------+----------+-------+-----------------+----------
---
-------+-------------+----------+-------+-----------------+----------
---
-------+-------------+----------+-------+-----------------+----------
---
-------+-------------+----------+-------+-----------------+----------
---
-------+-------------+----------+-------+-----------------+--
 19412 | test_id_idx | relation | 27612 | AccessShareLock | select data 
from test where data = 'Test data No 1';
 19405 | test        | relation | 27612 | AccessShareLock | select
data from test where data = 'Test data No 1';
(2 rows)

发现在执行select期间对表上的所有的索引都加上了AccessShareLock锁，但是查询并没有走索引。这让客户非常奇怪。

没办法，带着这个问题调查了一番。大概有了以下的两点粗浅的认识和理解。

1.在执行一条select文时，数据库后端会对select文进行查询分析，查询重写，查询规划和查询执行四个阶段。在查询规划阶段，需要生成一个RelOptInfo结构存储优化的查询路径，该结构中存储了表上的索引信息。此处调用get_relation_info函数打开并锁定表上所有的索引，并将索引的信息写入到RelOptInfo结构体中。

具体到代码里，我们可以看到：

typedef struct RelOptInfo
{
	NodeTag		type;

	RelOptKind	reloptkind;

	/* all relations included in this RelOptInfo */
	Relids		relids;			/* set of base relids (rangetable indexes) */
	.
	.
	.
	List	   *indexlist;		/* list of IndexOptInfo */                                    <-------  here
	.
	.
	.
	bool		has_eclass_joins;		/* T means joininfo is incomplete */
} RelOptInfo;

这里indexlist就是查询的表上的所有的index的列表；

我们再看看get_relation_info函数，它获取该表上的“catalog information”，详情可以看看这段注释：

##############################################################
src/backend/optimizer/util/plancat.c:

/*
 * get_relation_info -
 *	  Retrieves catalog information for a given relation.
 *
 * Given the Oid of the relation, return the following info into fields
 * of the RelOptInfo struct:
 *
 *	min_attr	lowest valid AttrNumber
 *	max_attr	highest valid AttrNumber
 *	indexlist	list of IndexOptInfos for relation's indexes
 *	serverid	if it's a foreign table, the server OID
 *	fdwroutine	if it's a foreign table, the FDW function pointers
 *	pages		number of pages
 *	tuples		number of tuples
 *
 * Also, initialize the attr_needed[] and attr_widths[] arrays.  In most
 * cases these are left as zeroes, but sometimes we need to compute attr
 * widths here, and we may as well cache the results for costsize.c.
 *
 * If inhparent is true, all we need to do is set up the attr arrays:
 * the RelOptInfo actually represents the appendrel formed by an inheritance
 * tree, and so the parent rel's physical size and index information isn't
 * important for it.
 */
void
get_relation_info(PlannerInfo *root, Oid relationObjectId, bool inhparent,
				  RelOptInfo *rel)


###################################################################

而由于该查询是放在一个事务中的，我们发现：

2.一个事务内申请的锁会在事务结束时调用函数LockReleaseAll统一释放，在事务结束之前，该事务仍然保持对锁的持有。

代码如下:

src/backend/storage/lmgr/lock.c:

/*
 * LockRelease -- look up 'locktag' and release one 'lockmode' lock on it.
 *		Release a session lock if 'sessionLock' is true, else release a
 *		regular transaction lock.
 *
 * Side Effects: find any waiting processes that are now wakable,
 *		grant them their requested locks and awaken them.
 *		(We have to grant the lock here to avoid a race between
 *		the waking process and any new process to
 *		come along and request the lock.)
 */
bool
LockRelease(const LOCKTAG *locktag, LOCKMODE lockmode, bool sessionLock)

最后我们打开lock log，再模拟下客户现场。贴一下执行结果：

postgres=# begin;
BEGIN
postgres=# select data from test where data = 'Test data No 1';
LOG:  LockAcquire: lock [12373,16386] AccessShareLock at character 18
STATEMENT:  select data from test where data = 'Test data No 1';
LOG:  LockAcquire: lock [12373,16392] AccessShareLock
STATEMENT:  select data from test where data = 'Test data No 1';
LOG:  LockAcquire: lock [12373,16386] AccessShareLock
STATEMENT:  select data from test where data = 'Test data No 1';
      data
----------------
 Test data No 1
(1 row)


postgres=# end;
LOG:  LockReleaseAll: lockmethod=1
STATEMENT:  end;
LOG:  LockReleaseAll done
STATEMENT:  end;

果然，锁是在事务最后释放的。

综合1，2两点，我们向客户解释了这个问题是PostgreSQL的式样，不是bug。

Over~