为什么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的值"的学习就结束了，希望能够解决大家的疑惑。理论与实践的搭配能更好的帮助大家学习，快去试试吧！若想继续学习更多相关知识，请继续关注网站，小编会继续努力为大家带来更多实用的文章！