PostgreSQL 10.0 preview 功能增强 - 串行隔离级别 预加锁阈值可控
背景
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的稳定性也是远近闻名的。
参考
https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=c63172d60f242ad3581c83723a5b315bbe547a0e