PostgreSQL中APP在涉及locks时需要注意的地方有哪些

2025-03-13 技术教程

这篇文章主要讲解了“PostgreSQL中APP在涉及locks时需要注意的地方有哪些”，文中的讲解内容简单清晰，易于学习与理解，下面请大家跟着小编的思路慢慢深入，一起来研究和学习“PostgreSQL中APP在涉及locks时需要注意的地方有哪些”吧！

测试数据:

[local]:5432pg12@testdb=#droptableifexiststbl;DROPTABLETime:36.136ms[local]:5432pg12@testdb=#createtabletbl(idint,c1varchar(20),c2varchar(20));CREATETABLETime:4.903ms[local]:5432pg12@testdb=#[local]:5432pg12@testdb=#insertintotblselectx,'c1'||x,'c2'||xfromgenerate_series(1,1000000)asx;INSERT01000000Time:3677.812ms(00:03.678)[local]:5432pg12@testdb=#

— session 1

[local]:5432pg12@testdb=#selectpg_backend_pid();pg_backend_pid----------------1541(1row)

— session 2

[local]:5432pg12@testdb=#selectpg_backend_pid();pg_backend_pid----------------1628(1row)Time:4.446ms

1: Never add a column with a default value
表上新增列时获取的锁是AccessExclusiveLock,会阻塞RW(包括SELECT),为了尽快完成列的添加,新增有默认值的列,可拆分为新增列,然后执行UPDATE语句以免出现R阻塞.

--session1[local]:5432pg12@testdb=#begin;BEGINTime:0.929ms[local]:5432pg12@testdb=#*altertabletbladdcolumnc3varchar(20)default'c3';ALTERTABLETime:32.881ms[local]:5432pg12@testdb=#--session2[local]:5432pg12@testdb=#select*fromtbl;--阻塞--session3[local]:5432pg12@testdb=#selectpid,locktype,relation::regclass,mode,page,tuple,virtualxid,transactionid,virtualtransaction,granted,fastpathfrompg_lockswhererelation='tbl'::regclass;-[RECORD1]------+--------------------pid|1541locktype|relationrelation|tblmode|AccessExclusiveLockpage|tuple|virtualxid|transactionid|virtualtransaction|3/8granted|tfastpath|fTime:29.088ms

使用先添加列,后更新默认值的方法

------session1[local]:5432pg12@testdb=#begin;BEGINTime:0.330ms[local]:5432pg12@testdb=#*altertabletbladdcolumnc4varchar(20);ALTERTABLETime:0.460ms[local]:5432pg12@testdb=#*end;COMMITTime:0.530ms[local]:5432pg12@testdb=#begin;BEGINTime:0.199ms[local]:5432pg12@testdb=#*updatetblsetc4='c4';UPDATE1000000Time:5286.769ms(00:05.287)[local]:5432pg12@testdb=#*------session2[local]:5432pg12@testdb=#select*fromtbllimit1;id|c1|c2|c3|c4----+-----+-----+----+----1|c11|c21|c3|(1row)Time:2.793ms------session3[local]:5432pg12@testdb=#selectpid,locktype,relation::regclass,mode,page,tuple,virtualxid,transactionid,virtualtransaction,granted,fastpathfrompg_lockswhererelation='tbl'::regclass;-[RECORD1]------+-----------------pid|1541locktype|relationrelation|tblmode|RowExclusiveLockpage|tuple|virtualxid|transactionid|virtualtransaction|3/10granted|tfastpath|tTime:1.062ms

虽然更新耗费的时间远比直接add column设置默认值要大,但锁等级是RowExclusiveLock,并不会阻塞读

2: Beware of lock queues, use lock timeouts
PG中每一个锁都有一个队列,在获取锁时如需等待存在冲突的其他锁,则会阻塞.可通过设置超时时间避免长时间的等待.这样虽然会失败,但可通过后台查询等方法获取数据库活动,保持数据库可控.

------session1[local]:5432pg12@testdb=#begin;BEGINTime:1.148ms[local]:5432pg12@testdb=#*altertabletbladdcolumnc5varchar(20)default'c3';ALTERTABLETime:2.726ms[local]:5432pg12@testdb=#*------session3[local]:5432pg12@testdb=#selectpid,locktype,relation::regclass,mode,page,tuple,virtualxid,transactionid,virtualtransaction,granted,fastpathfrompg_lockswhererelation='tbl'::regclass;-[RECORD1]------+--------------------pid|1541locktype|relationrelation|tblmode|AccessExclusiveLockpage|tuple|virtualxid|transactionid|virtualtransaction|3/11granted|tfastpath|fTime:2.751ms------session2[local]:5432pg12@testdb=#begin;BEGINTime:0.861ms[local]:5432pg12@testdb=#*SETlock_timeoutTO'1s';SETTime:0.689ms[local]:5432pg12@testdb=#*select*fromtbllimit1;ERROR:cancelingstatementduetolocktimeoutLINE1:select*fromtbllimit1;^Time:1001.031ms(00:01.001)[local]:5432pg12@testdb=#!end;ROLLBACKTime:0.984ms[local]:5432pg12@testdb=#!

3: Create indexes CONCURRENTLY
使用CONCURRENTLY模式创建Index.
新插入1000w数据

[local]:5432pg12@testdb=#insertintotblselectx,'c1'||x,'c2'||x,'c3'||xfromgenerate_series(1,10000000)asx;INSERT010000000Time:32784.183ms(00:32.784)

普通模式创建索引

回滚事务后,使用CONCURRENTLY模式创建索引,要注意的是CONCURRENTLY模式不能用在事务中

[local]:5432pg12@testdb=#*--onlyblocksotherDDL[local]:5432pg12@testdb=#*createindexCONCURRENTLYidx_tbl_idontbl(id);ERROR:CREATEINDEXCONCURRENTLYcannotruninsideatransactionblockTime:0.491ms[local]:5432pg12@testdb=#!

不启动事务,直接执行

使用CONCURRENTLY模式创建索引,获取的lock是ShareUpdateExclusiveLock,不会阻塞INSERT/UPDATE/DELETE操作(请求的锁是RowExclusiveLock)

4: Take aggressive locks as late as possible
这个跟编程中定义变量类似 : 离需要用到的地方越近的地方才定义.文中的例子见仁见智,选择使用.

5: Adding a primary key with minimal locking
重新构建测试数据

[local]:5432pg12@testdb=#truncatetabletbl;TRUNCATETABLETime:91.815ms[local]:5432pg12@testdb=#insertintotblselectx,'c1'||x,'c2'||x,'c3'||xfromgenerate_series(1,12000000)asx;INSERT012000000Time:59285.694ms(00:59.286)

把add primary key这一个动作拆解为先添加唯一索引,再添加primary key constraint这两个动作.

拆解后,使用CONCURRENTLY模式创建索引,与第3点类似

------session1[local]:5432pg12@testdb=#--takesalongtime,butdoesn’tblockqueries[local]:5432pg12@testdb=#CREATEUNIQUEINDEXCONCURRENTLYidx_tbl_idONtbl(id);CREATEINDEXTime:9908.405ms(00:09.908)[local]:5432pg12@testdb=#--blocksqueries,butonlyverybriefly[local]:5432pg12@testdb=#ALTERTABLEtblADDCONSTRAINTpk_tblPRIMARYKEYUSINGINDEXidx_tbl_id;NOTICE:ALTERTABLE/ADDCONSTRAINTUSINGINDEXwillrenameindex"idx_tbl_id"to"pk_tbl"ALTERTABLETime:4582.013ms(00:04.582)

6: Never VACUUM FULL

vacuum full请求的锁是AccessExclusiveLock,会阻塞读写,在目前vacuum full并不智能的情况下,手工发起对单个表的vacuum full会保险许多.

7: Avoid deadlocks by ordering commands
注意命令的顺序,避免死锁

------session1[local]:5432pg12@testdb=#begin;BEGINTime:0.440ms[local]:5432pg12@testdb=#*deletefromtblwhereid=1;DELETE1Time:0.567ms[local]:5432pg12@testdb=#*------session2[local]:5432pg12@testdb=#begin;BEGINTime:0.960ms[local]:5432pg12@testdb=#*deletefromtblwhereid=2;DELETE1Time:1.783ms[local]:5432pg12@testdb=#*------session3

产生死锁

------session1[local]:5432pg12@testdb=#*deletefromtblwhereid=2;------session2[local]:5432pg12@testdb=#*deletefromtblwhereid=1;ERROR:deadlockdetectedDETAIL:Process1628waitsforShareLockontransaction623;blockedbyprocess1541.Process1541waitsforShareLockontransaction624;blockedbyprocess1628.HINT:Seeserverlogforquerydetails.CONTEXT:whiledeletingtuple(0,1)inrelation"tbl"Time:1004.485ms(00:01.004)[local]:5432pg12@testdb=#!------session3

感谢各位的阅读，以上就是“PostgreSQL中APP在涉及locks时需要注意的地方有哪些”的内容了，经过本文的学习后，相信大家对PostgreSQL中APP在涉及locks时需要注意的地方有哪些这一问题有了更深刻的体会，具体使用情况还需要大家实践验证。这里是亿速云，小编将为大家推送更多相关知识点的文章，欢迎关注！