697
PostgreSQL 10.0 preview 功能增強 - 串行隔離級別 預加鎖閾值可控
標簽
PostgreSQL , 10.0 , 串行隔離級別 , max_pred_locks_per_relation , 預加鎖
背景
PostgreSQL 串行隔離級別中一個重要的概念是預加鎖，在事務結束時，檢測預加鎖是否衝突。
10.0增加了三個控製參數，可以控製預加鎖的上限。避免內存過度使用。
1. 控製每個事務的對象預加鎖數量。
max_pred_locks_per_transaction (integer)
The shared predicate lock table tracks locks on max_pred_locks_per_transaction * (max_connections + max_prepared_transactions) objects (e.g., tables);
hence, no more than this many distinct objects can be locked at any one time.
This parameter controls the average number of object locks allocated for each transaction;
individual transactions can lock more objects as long as the locks of all transactions fit in the lock table.
This is not the number of rows that can be locked; that value is unlimited.
The default, 64, has generally been sufficient in testing, but you might need to raise this value if you have clients that touch many different tables in a single serializable transaction.
This parameter can only be set at server start.
2. 當單個對象的行或者頁預加鎖數量達到閾值時，升級為對象預加鎖。減少內存開銷。
max_pred_locks_per_relation (integer)
This controls how many pages or tuples of a single relation can be predicate-locked before the lock is promoted to covering the whole relation.
Values greater than or equal to zero mean an absolute limit, while negative values mean max_pred_locks_per_transaction divided by the absolute value of this setting.
The default is -2, which keeps the behaviour from previous versions of PostgreSQL.
This parameter can only be set in the postgresql.conf file or on the server command line.
3. 當單個頁內多少條記錄被加預加鎖時，升級為頁預加鎖。減少內存開銷。
max_pred_locks_per_page (integer)
This controls how many rows on a single page can be predicate-locked before the lock is promoted to covering the whole page.
The default is 2. This parameter can only be set in the postgresql.conf file or on the server command line.
關於串行隔離級別的概念，您可以參考
《PostgreSQL SERIALIZABLE ISOLATION LEVEL introduce》
patch 信息如下
Add GUCs for predicate lock promotion thresholds.
author Kevin Grittner <kgrittn@postgresql.org>
Sat, 8 Apr 2017 10:38:05 +0800 (21:38 -0500)
committer Kevin Grittner <kgrittn@postgresql.org>
Sat, 8 Apr 2017 10:38:05 +0800 (21:38 -0500)
commit c63172d60f242ad3581c83723a5b315bbe547a0e
tree 0a98686f027aacecb01869bfb269ebd486ea3ba7 tree | snapshot
parent 9c7f5229ad68d7e0e4dd149e3f80257893e404d4 commit | diff
Add GUCs for predicate lock promotion thresholds.
Defaults match the fixed behavior of prior releases, but now DBAs
have better options to tune serializable workloads.
It might be nice to be able to set this per relation, but that part
will need to wait for another release.
Author: Dagfinn Ilmari Mannsåker
這個patch的討論，詳見郵件組，本文末尾URL。
PostgreSQL社區的作風非常嚴謹，一個patch可能在郵件組中討論幾個月甚至幾年，根據大家的意見反複的修正，patch合並到master已經非常成熟，所以PostgreSQL的穩定性也是遠近聞名的。
參考
最後更新：2017-04-22 17:01:48