首页 > 代码库 > Postgres中的锁

Postgres中的锁

2024-09-04 23:23:08   220人阅读

postgres开发实践中遇到一个问题：

1） A用户在执行一条语句

2） B用户执行语句查询相同的表

 

会发现B用户始终处于等待状态.

跟踪会发现A进程等待时进程堆栈如下：

#0  LockAcquire (locktag=0x7fffa3ad28f0, lockmode=1, sessionLock=0 ‘\000‘, dontWait=0 ‘\000‘) at /home/hl/uda/build/../collocated_join/src/backend/storage/lmgr/lock.c:677
#1  0x0000000000799654 in LockRelationOid (relid=57432, lockmode=1) at /home/hl/uda/build/../collocated_join/src/backend/storage/lmgr/lmgr.c:79
#2  0x0000000000531ba1 in RangeVarGetRelidExtended (relation=0xe71da0, lockmode=1, missing_ok=1 ‘\001‘, nowait=0 ‘\000‘, callback=0x0, callback_arg=0x0)
   at /home/hl/uda/build/../collocated_join/src/backend/catalog/namespace.c:377
#3  0x00000000004ab49b in relation_openrv_extended (relation=0xe71da0, lockmode=1, missing_ok=1 ‘\001‘) at /home/hl/uda/build/../collocated_join/src/backend/access/heap/heapam.c:1141
#4  0x00000000004ab759 in heap_openrv_extended (relation=0xe71da0, lockmode=1, missing_ok=1 ‘\001‘) at /home/hl/uda/build/../collocated_join/src/backend/access/heap/heapam.c:1247
#5  0x00000000005a1b2b in parserOpenTable (pstate=0xe72040, relation=0xe71da0, lockmode=1) at /home/hl/uda/build/../collocated_join/src/backend/parser/parse_relation.c:956
#6  0x00000000005a1d7b in addRangeTableEntry (pstate=0xe72040, relation=0xe71da0, alias=0xe71df8, inh=1 ‘\001‘, inFromCl=1 ‘\001‘)
   at /home/hl/uda/build/../collocated_join/src/backend/parser/parse_relation.c:1022
#7  0x000000000058b232 in transformTableEntry (pstate=0xe72040, r=0xe71da0) at /home/hl/uda/build/../collocated_join/src/backend/parser/parse_clause.c:429
#8  0x000000000058b625 in transformFromClauseItem (pstate=0xe72040, n=0xe71da0, top_rte=0x7fffa3ad2d38, top_rti=0x7fffa3ad2d34, namespace=0x7fffa3ad2d28)
   at /home/hl/uda/build/../collocated_join/src/backend/parser/parse_clause.c:640
#9  0x000000000058ab76 in transformFromClause (pstate=0xe72040, frmList=0xe71e58) at /home/hl/uda/build/../collocated_join/src/backend/parser/parse_clause.c:120
#10 0x000000000055a7a3 in transformSelectStmt (pstate=0xe72040, stmt=0xe71e90) at /home/hl/uda/build/../collocated_join/src/backend/parser/analyze.c:939

B进程堆栈如下：

#0  0x00007f110753d627 in semop () from /lib64/libc.so.6
#1  0x0000000000740f01 in PGSemaphoreLock (sema=0x7f11070313f0, interruptOK=1 ‘\001‘) at pg_sema.c:421
#2  0x00000000007a2521 in ProcSleep (locallock=0xddec58, lockMethodTable=0xa7c2c0 <default_lockmethod>) at /home/hl/uda/build/../collocated_join/src/backend/storage/lmgr/proc.c:1134
#3  0x000000000079c6b9 in WaitOnLock (locallock=0xddec58, owner=0xe9a4a8) at /home/hl/uda/build/../collocated_join/src/backend/storage/lmgr/lock.c:1623
#4  0x000000000079b57b in LockAcquireExtended (locktag=0x7fffa3ad2130, lockmode=8, sessionLock=0 ‘\000‘, dontWait=0 ‘\000‘, reportMemoryError=1 ‘\001‘)
   at /home/hl/uda/build/../collocated_join/src/backend/storage/lmgr/lock.c:987
#5  0x000000000079ac15 in LockAcquire (locktag=0x7fffa3ad2130, lockmode=8, sessionLock=0 ‘\000‘, dontWait=0 ‘\000‘) at /home/hl/uda/build/../collocated_join/src/backend/storage/lmgr/lock.c:677
#6  0x0000000000799654 in LockRelationOid (relid=57432, lockmode=8) at /home/hl/uda/build/../collocated_join/src/backend/storage/lmgr/lmgr.c:79
#7  0x00000000004ab2a5 in relation_open (relationId=57432, lockmode=8) at /home/hl/uda/build/../collocated_join/src/backend/access/heap/heapam.c:1023
#8  0x00000000004ab54c in heap_open (relationId=57432, lockmode=8) at /home/hl/uda/build/../collocated_join/src/backend/access/heap/heapam.c:1189
#9  0x000000000062f0d3 in GetPartitionKeyInfo (pkrelid=57432, attnum=0x7fffa3ad2256, attRangeTypeId=0x7fffa3ad2250) at /home/hl/uda/build/../collocated_join/src/backend/commands/logictablecmds.c:201
#10 0x000000000073d28c in HitForeignTableByCond (rel=0xf76380, rte=0xeb6cb8, ftable=0xf77610, fserver=0xf77940, cond=0xf775d8, oldContext=0xe9a390)
   at /home/hl/uda/build/../collocated_join/src/backend/optimizer/route/createroute.c:599
#11 0x000000000073d721 in build_foreign_route (rel=0xf76380, rte=0xeb6cb8, cond=0xf775d8) at /home/hl/uda/build/../collocated_join/src/backend/optimizer/route/createroute.c:698
#12 0x00000000006ec7bb in set_foreign_size (root=0xeb6648, rel=0xf76380, rte=0xeb6cb8) at /home/hl/uda/build/../collocated_join/src/backend/optimizer/path/allpaths.c:415
#13 0x00000000006ec4e2 in set_rel_size (root=0xeb6648, rel=0xf76380, rti=2, rte=0xeb6cb8) at /home/hl/uda/build/../collocated_join/src/backend/optimizer/path/allpaths.c:250
#14 0x00000000006ecc33 in set_append_rel_size (root=0xeb6648, rel=0xf75d30, rti=1, rte=0xea56a8) at /home/hl/uda/build/../collocated_join/src/backend/optimizer/path/allpaths.c:623
#15 0x00000000006ec49f in set_rel_size (root=0xeb6648, rel=0xf75d30, rti=1, rte=0xea56a8) at /home/hl/uda/build/../collocated_join/src/backend/optimizer/path/allpaths.c:240

 

导致这个现象的原因是因为在我们在 解析parse的时候就开始加锁了， 直到portal执行完才将此锁释放。而且用的还是exclusive锁。所以

会导致另外的用户查询不了，解决的方法就是把锁的级别改成读锁。

 

pg中锁分为不同的等级：

#define NoLock                                        0

#define AccessShareLock                        1                /* SELECT */
#define RowShareLock                        2                /* SELECT FOR UPDATE/FOR SHARE */
#define RowExclusiveLock                3                /* INSERT, UPDATE, DELETE */
#define ShareUpdateExclusiveLock 4                /* VACUUM (non-FULL),ANALYZE, CREATE
                                                                                * INDEX CONCURRENTLY */
#define ShareLock                                5                /* CREATE INDEX (WITHOUT CONCURRENTLY) */
#define ShareRowExclusiveLock        6                /* like EXCLUSIVE MODE, but allows ROW
                                                                                * SHARE */
#define ExclusiveLock                        7                /* blocks ROW SHARE/SELECT...FOR
                                                                                * UPDATE */
#define AccessExclusiveLock                8                /* ALTER TABLE, DROP TABLE, VACUUM
                                                                                * FULL, and unqualified LOCK TABLE */

AccessExclusiveLock 这种级别的锁是为了修改而加的。 等级最低，必须等所有锁释放之后才能操作， 查询时用这个锁就会卡住

可以通过 pg_locks 表来查看锁
postgres=# select * from pg_locks where relation = 57432;
locktype | database | relation | page | tuple | virtualxid | transactionid | classid | objid | objsubid | virtualtransaction | pid | mode | granted | fastpath
----------+----------+----------+------+-------+------------+---------------+---------+-------+----------+--------------------+-----+-----------------+---------+----------
relation | 12652 | 57432 | | | | | | | | 2/24 | 674 | AccessShareLock | t | t

Postgres中的锁


联系
我们