Shulou(Shulou.com)05/31 Report--

This article mainly introduces "why PG will prompt to increase the value of max_locks_per_transaction". In daily operation, I believe many people have doubts about why PG will prompt to increase the value of max_locks_per_transaction. Xiaobian consulted all kinds of data and sorted out a simple and easy-to-use method of operation. I hope it will be helpful to answer the question of "why PG will prompt to increase the value of max_locks_per_transaction"! Next, please follow the editor to study!

Sometimes we may find the following information in the PG log:

2020-01-09 16 testdb 2919.062 CST, "pg12", "testdb", 6193, "[local]", 5e16dcd.1831 CST,2/34,1512004206,ERROR,53200 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 16purl 2919.379 CST, "pg12", "testdb", 6193, "[local]", 5e16dccd.1831 CST,2/0,1512004206,ERROR,25P02 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"

Intuitively, OOM seems to have nothing to do with max_locks_per_transaction, so why does PG prompt to increase the value of max_locks_per_transaction? In a transaction, shared lock table can track up to max_locks_per_transaction * (max_connections + max_prepared_transactions) objects (such as data tables), and those exceeding it will report an OOM error. Note: the lock granularity is object (such as relation, etc.), regardless of the number of rows.

OOM scene simulation

The following is a simulation scenario in which 1w tables are created in the same transaction:

\ 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 |

Use watch to monitor output

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).

In the implementation of the tbl13034 Times error

2020-01-10 14 testdb 44local 18.855 CST, "pg12", "testdb", 32120, "[local]", 5e181bea.7d78mem3, "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 testdb 44displacement 19.202 CST," pg12 "," testdb ", 32120," [local] ", 5e181bea.7d78 CST,2/0,1512036258,ERROR,25P02 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 "

Related source code

Search for You might need to increase max_locks_per_transaction. The error message appears in 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;}}... At this point, the study on "Why PG prompts to increase the value of max_locks_per_transaction" is over. I hope to be able to solve everyone's doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.