为什么PG会提示增加max_locks_per_transaction的值
发表于:2024-09-22 作者:千家信息网编辑
千家信息网最后更新 2024年09月22日,这篇文章主要介绍"为什么PG会提示增加max_locks_per_transaction的值",在日常操作中,相信很多人在为什么PG会提示增加max_locks_per_transaction的值问题
千家信息网最后更新 2024年09月22日为什么PG会提示增加max_locks_per_transaction的值
这篇文章主要介绍"为什么PG会提示增加max_locks_per_transaction的值",在日常操作中,相信很多人在为什么PG会提示增加max_locks_per_transaction的值问题上存在疑惑,小编查阅了各式资料,整理出简单好用的操作方法,希望对大家解答"为什么PG会提示增加max_locks_per_transaction的值"的疑惑有所帮助!接下来,请跟着小编一起来学习吧!
有时候我们可能会在PG的日志发现如下信息:
2020-01-09 16:29:19.062 CST,"pg12","testdb",6193,"[local]",5e16dccd.1831,1,"CREATE TABLE",2020-01-09 15:57:01 CST,2/34,1512004206,ERROR,53200,"out of shared memory",,"You might need to increase max_locks_per_transaction.",,,,"CREATE TABLE a13030 (id int);",,,"psql"2020-01-09 16:29:19.379 CST,"pg12","testdb",6193,"[local]",5e16dccd.1831,2,"CREATE TABLE",2020-01-09 15:57:01 CST,2/0,1512004206,ERROR,25P02,"current transaction is aborted, commands ignored until end of transaction block",,,,,,"CREATE TABLE a13031 (id int);",,,"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张表:
\pset footer off\o /tmp/drop.sqlSELECT 'drop table if exists tbl' || id || ' ;' as "--" FROM generate_series(1, 20000) AS id;\i /tmp/drop.sql\pset footer off\pset tuples_only\o /tmp/create.sqlSELECT 'CREATE TABLE tbl' || id || ' (id int);' as "--" FROM generate_series(1, 20000) AS id;\o /tmp/ret.txtbegin;\i /tmp/create.sql
使用watch监控输出
watch -n1 "psql -c \"select locktype,mode,count(*) from pg_locks group by locktype,mode;\""Every 1.0s: psql -c "select locktype,mode,count(*) from pg_locks group by locktype,mode;" Fri Jan 10 14:41:26 2020Expanded display is used automatically. locktype | mode | count---------------+---------------------+------- object | AccessShareLock | 1 relation | AccessShareLock | 1 virtualxid | ExclusiveLock | 2 relation | AccessExclusiveLock | 3776 transactionid | ExclusiveLock | 1(5 rows)...Every 1.0s: psql -c "select locktype,mode,count(*) from pg_locks group by locktype,mode;" Fri Jan 10 14:41:50 2020Expanded display is used automatically. locktype | mode | count---------------+---------------------+------- object | AccessShareLock | 1 relation | AccessShareLock | 1 virtualxid | ExclusiveLock | 2 relation | AccessExclusiveLock | 10000 transactionid | ExclusiveLock | 1(5 rows)...
在执行到tbl13034时报错
2020-01-10 14:44:18.855 CST,"pg12","testdb",32120,"[local]",5e181bea.7d78,3,"CREATE TABLE",2020-01-10 14:38:34 CST,2/106085,1512036258,ERROR,53200,"out of shared memory",,"You might need to increase max_locks_per_transaction.",,,,"CREATE TABLE tbl13034 (id int);",,,"psql"2020-01-10 14:44:19.202 CST,"pg12","testdb",32120,"[local]",5e181bea.7d78,4,"CREATE TABLE",2020-01-10 14:38:34 CST,2/0,1512036258,ERROR,25P02,"current transaction is aborted, commands ignored until end of transaction block",,,,,,"CREATE TABLE tbl13035 (id int);",,,"psql"
相关源码
搜索You might need to increase max_locks_per_transaction.该错误信息出现在lock.c中
/* * LockAcquireExtended - allows us to specify additional options * * reportMemoryError specifies whether a lock request that fills the lock * table should generate an ERROR or not. Passing "false" allows the caller * to attempt to recover from lock-table-full situations, perhaps by forcibly * cancelling other lock holders and then retrying. Note, however, that the * return code for that is LOCKACQUIRE_NOT_AVAIL, so that it's unsafe to use * in combination with dontWait = true, as the cause of failure couldn't be * distinguished. * * If locallockp isn't NULL, *locallockp receives a pointer to the LOCALLOCK * table entry if a lock is successfully acquired, or NULL if not. */ LockAcquireResult LockAcquireExtended(const LOCKTAG *locktag, LOCKMODE lockmode, bool sessionLock, bool dontWait, bool reportMemoryError, LOCALLOCK **locallockp) { ... /* * If this lock could potentially have been taken via the fast-path by * some other backend, we must (temporarily) disable further use of the * fast-path for this lock tag, and migrate any locks already taken via * this method to the main lock table. */ if (ConflictsWithRelationFastPath(locktag, lockmode)) { uint32 fasthashcode = 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("out of shared memory"), errhint("You might need to increase max_locks_per_transaction."))); else return LOCKACQUIRE_NOT_AVAIL; } } ...
到此,关于"为什么PG会提示增加max_locks_per_transaction的值"的学习就结束了,希望能够解决大家的疑惑。理论与实践的搭配能更好的帮助大家学习,快去试试吧!若想继续学习更多相关知识,请继续关注网站,小编会继续努力为大家带来更多实用的文章!
提示
学习
事务
信息
场景
更多
错误
帮助
实用
最大
直观
接下来
对象
数据
数据表
文章
方法
日志
时报
有时候
数据库的安全要保护哪些东西
数据库安全各自的含义是什么
生产安全数据库录入
数据库的安全性及管理
数据库安全策略包含哪些
海淀数据库安全审计系统
建立农村房屋安全信息数据库
易用的数据库客户端支持安全管理
连接数据库失败ssl安全错误
数据库的锁怎样保障安全
黑龙江网络技术招聘
阐述当前网络技术的影响有
金数据库考试
计算机信息网络技术是什么
软件开发模组
点评网数据库
工业网络安全厂商
暗黑4服务器有哪些
2012数据库表格查询
如何利用超星数据库查资料
服务器域名安全吗
浦东新区综合软件开发信息中心
我为网络安全代言手抄报内容
普陀区软件开发技术服务前景
使用数据库保存数据的好处
native 数据库
绗缝机软件开发
北京一java软件开发
机甲和塔式服务器哪个更便宜
中兴服务器国产化
山东广电dns服务器云主机
我的世界粘液服务器指令
天津方诺互联网科技
北京信息化网络技术服务平台
网络安全数据条例
3个网络安全事件
数据库的决定因子
网络安全系统盈利
移动工程网络安全会议心得
成都 智慧 公安 大数据库