為什么PG會提示增加max_locks_per_transaction的值

這篇文章主要介紹“為什么PG會提示增加max_locks_per_transaction的值”，在日常操作中，相信很多人在為什么PG會提示增加max_locks_per_transaction的值問題上存在疑惑，小編查閱了各式資料，整理出簡單好用的操作方法，希望對大家解答”為什么PG會提示增加max_locks_per_transaction的值”的疑惑有所幫助！接下來，請跟著小編一起來學(xué)習(xí)吧！

有時候我們可能會在PG的日志發(fā)現(xiàn)如下信息：

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扯不上什么關(guān)系，為什么PG會提示增加max_locks_per_transaction的值呢？在一個事務(wù)中，shared lock table最大可以跟蹤max_locks_per_transaction * (max_connections + max_prepared_transactions) 個對象（如數(shù)據(jù)表），超過的會報OOM錯誤。注意：鎖粒度是object(如relation等)，跟行數(shù)無關(guān)。

OOM場景模擬
下面是一個模擬場景，在同一個事務(wù)中創(chuàng)建1w張表：

\pset footer off
\o /tmp/drop.sql
SELECT '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.sql
SELECT 'CREATE TABLE tbl' || id || ' (id int);' as "--"
       FROM generate_series(1, 20000) AS id;
\o /tmp/ret.txt
begin;
\i /tmp/create.sql

使用watch監(jiān)控輸出

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 2020
Expanded 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 2020
Expanded display is used automatically.
   locktype    |        mode         | count
---------------+---------------------+-------
 object        | AccessShareLock     |     1
 relation      | AccessShareLock     |     1
 virtualxid    | ExclusiveLock       |     2
 relation      | AccessExclusiveLock | 10000
 transactionid | ExclusiveLock       |     1
(5 rows)
...

在執(zhí)行到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"

相關(guān)源碼
搜索You might need to increase max_locks_per_transaction.該錯誤信息出現(xiàn)在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;
         }
     }
     ...

到此，關(guān)于“為什么PG會提示增加max_locks_per_transaction的值”的學(xué)習(xí)就結(jié)束了，希望能夠解決大家的疑惑。理論與實踐的搭配能更好的幫助大家學(xué)習(xí)，快去試試吧！若想繼續(xù)學(xué)習(xí)更多相關(guān)知識，請繼續(xù)關(guān)注億速云網(wǎng)站，小編會繼續(xù)努力為大家?guī)砀鄬嵱玫奈恼拢?/p>