为什么PG会提示增加max_locks_per_transaction的值

2025-03-14 技术教程

这篇文章主要介绍“为什么PG会提示增加max_locks_per_transaction的值”，在日常操作中，相信很多人在为什么PG会提示增加max_locks_per_transaction的值问题上存在疑惑，小编查阅了各式资料，整理出简单好用的操作方法，希望对大家解答”为什么PG会提示增加max_locks_per_transaction的值”的疑惑有所帮助！接下来，请跟着小编一起来学习吧！

有时候我们可能会在PG的日志发现如下信息：

2020-01-0916:29:19.062CST,"pg12","testdb",6193,"[local]",5e16dccd.1831,1,"CREATETABLE",2020-01-0915:57:01CST,2/34,1512004206,ERROR,53200,"outofsharedmemory",,"Youmightneedtoincreasemax_locks_per_transaction.",,,,"CREATETABLEa13030(idint);",,,"psql"2020-01-0916:29:19.379CST,"pg12","testdb",6193,"[local]",5e16dccd.1831,2,"CREATETABLE",2020-01-0915:57:01CST,2/0,1512004206,ERROR,25P02,"currenttransactionisaborted,commandsignoreduntilendoftransactionblock",,,,,,"CREATETABLEa13031(idint);",,,"psql"

直观上来看，OOM似乎与max_locks_per_transaction扯不上什么关系，为什么PG会提示增加max_locks_per_transaction的值呢？在一个事务中，shared lock table最大可以跟踪max_locks_per_transaction * (max_connections + max_prepared_transactions) 个对象（如数据表），超过的会报OOM错误。注意：锁粒度是object(如relation等)，跟行数无关。

OOM场景模拟
下面是一个模拟场景，在同一个事务中创建1w张表：

\psetfooteroff\o/tmp/drop.sqlSELECT'droptableifexiststbl'||id||';'as"--"FROMgenerate_series(1,20000)ASid;\i/tmp/drop.sql\psetfooteroff\psettuples_only\o/tmp/create.sqlSELECT'CREATETABLEtbl'||id||'(idint);'as"--"FROMgenerate_series(1,20000)ASid;\o/tmp/ret.txtbegin;\i/tmp/create.sql

使用watch监控输出

在执行到tbl13034时报错

2020-01-1014:44:18.855CST,"pg12","testdb",32120,"[local]",5e181bea.7d78,3,"CREATETABLE",2020-01-1014:38:34CST,2/106085,1512036258,ERROR,53200,"outofsharedmemory",,"Youmightneedtoincreasemax_locks_per_transaction.",,,,"CREATETABLEtbl13034(idint);",,,"psql"2020-01-1014:44:19.202CST,"pg12","testdb",32120,"[local]",5e181bea.7d78,4,"CREATETABLE",2020-01-1014:38:34CST,2/0,1512036258,ERROR,25P02,"currenttransactionisaborted,commandsignoreduntilendoftransactionblock",,,,,,"CREATETABLEtbl13035(idint);",,,"psql"

相关源码
搜索You might need to increase max_locks_per_transaction.该错误信息出现在lock.c中

/**LockAcquireExtended-allowsustospecifyadditionaloptions**reportMemoryErrorspecifieswhetheralockrequestthatfillsthelock*tableshouldgenerateanERRORornot.Passing"false"allowsthecaller*toattempttorecoverfromlock-table-fullsituations,perhapsbyforcibly*cancellingotherlockholdersandthenretrying.Note,however,thatthe*returncodeforthatisLOCKACQUIRE_NOT_AVAIL,sothatit'sunsafetouse*incombinationwithdontWait=true,asthecauseoffailurecouldn'tbe*distinguished.**Iflocallockpisn'tNULL,*locallockpreceivesapointertotheLOCALLOCK*tableentryifalockissuccessfullyacquired,orNULLifnot.*/LockAcquireResultLockAcquireExtended(constLOCKTAG*locktag,LOCKMODElockmode,boolsessionLock,booldontWait,boolreportMemoryError,LOCALLOCK**locallockp){.../**Ifthislockcouldpotentiallyhavebeentakenviathefast-pathby*someotherbackend,wemust(temporarily)disablefurtheruseofthe*fast-pathforthislocktag,andmigrateanylocksalreadytakenvia*thismethodtothemainlocktable.*/if(ConflictsWithRelationFastPath(locktag,lockmode)){uint32fasthashcode=FastPathStrongLockHashPartition(hashcode);BeginStrongLockAcquire(locallock,fasthashcode);if(!FastPathTransferRelationLocks(lockMethodTable,locktag,hashcode)){AbortStrongLockAcquire();if(locallock->nLocks==0)RemoveLocalLock(locallock);if(locallockp)*locallockp=NULL;if(reportMemoryError)ereport(ERROR,(errcode(ERRCODE_OUT_OF_MEMORY),errmsg("outofsharedmemory"),errhint("Youmightneedtoincreasemax_locks_per_transaction.")));elsereturnLOCKACQUIRE_NOT_AVAIL;}}...

到此，关于“为什么PG会提示增加max_locks_per_transaction的值”的学习就结束了，希望能够解决大家的疑惑。理论与实践的搭配能更好的帮助大家学习，快去试试吧！若想继续学习更多相关知识，请继续关注亿速云网站，小编会继续努力为大家带来更多实用的文章！