一个事务最多能锁多少对象? how many objects can be locked per transaction

背景

在PostgreSQL中，表、视图、物化视图、序列、索引、TOAST等，统称为对象，那么在一个事务中可以锁多少个对象呢？（我不打算在这里讲锁级别。可以参考锁级别文章）

通过pg_locks这个视图可以查看到当前数据库的锁信息（注意行锁在行头，不会体现在pg_locks中，也不占用内存）。

或者直接通过如下SQL查询:

SELECT l.locktype, l.database, l.relation, l.page, l.tuple, l.virtualxid, l.transactionid, l.classid, l.objid, l.objsubid, l.virtualtransaction, l.pid, l.mode, l.granted FROM pg_lock_status()   
l(locktype, database, relation, page, tuple, virtualxid, transactionid,classid, objid, objsubid, virtualtransaction, pid, mode, granted);  

数据库启动时，会初始化共享内存区域，共享内存是一个统称，实际上有很多共享内存区，比如锁也是一块。

参考

src/backend/storage/ipc/ipci.c

/*  
 * CreateSharedMemoryAndSemaphores  
 *              Creates and initializes shared memory and semaphores.  
 *  
 * This is called by the postmaster or by a standalone backend.  
 * It is also called by a backend forked from the postmaster in the  
 * EXEC_BACKEND case.  In the latter case, the shared memory segment  
 * already exists and has been physically attached to, but we have to  
 * initialize pointers in local memory that reference the shared structures,  
 * because we didn't inherit the correct pointer values from the postmaster  
 * as we do in the fork() scenario.  The easiest way to do that is to run  
 * through the same code as before.  (Note that the called routines mostly  
 * check IsUnderPostmaster, rather than EXEC_BACKEND, to detect this case.  
 * This is a bit code-wasteful and could be cleaned up.)  
 *  
 * If "makePrivate" is true then we only need private memory, not shared  
 * memory.  This is true for a standalone backend, false for a postmaster.  
 */  
void  
CreateSharedMemoryAndSemaphores(bool makePrivate, int port)  
{  
...  
  
        /*  
         * Set up shared memory allocation mechanism  
         */  
        if (!IsUnderPostmaster)  
                InitShmemAllocation();  
  
        /*  
         * Now initialize LWLocks, which do shared memory allocation and are  
         * needed for InitShmemIndex.  
         */  
        CreateLWLocks();  
  
        /*  
         * Set up shmem.c index hashtable  
         */  
        InitShmemIndex();  
  
        /*  
         * Set up xlog, clog, and buffers  
         */  
        XLOGShmemInit();  
        CLOGShmemInit();  
        CommitTsShmemInit();  
        SUBTRANSShmemInit();  
        MultiXactShmemInit();  
        InitBufferPool();  
  
        /*  
         * Set up lock manager  
         */  
        InitLocks();  
  
        /*  
         * Set up predicate lock manager  
         */  
        InitPredicateLocks();  
  
        /*  
         * Set up process table  
         */  
        if (!IsUnderPostmaster)  
                InitProcGlobal();  
        CreateSharedProcArray();  
        CreateSharedBackendStatus();  
        TwoPhaseShmemInit();  
        BackgroundWorkerShmemInit();  
  
        /*  
         * Set up shared-inval messaging  
         */  
        CreateSharedInvalidationState();  
  
        /*  
         * Set up interprocess signaling mechanisms  
         */  
        PMSignalShmemInit();  
        ProcSignalShmemInit();  
        CheckpointerShmemInit();  
        AutoVacuumShmemInit();  
        ReplicationSlotsShmemInit();  
        ReplicationOriginShmemInit();  
        WalSndShmemInit();  
        WalRcvShmemInit();  
  
        /*  
         * Set up other modules that need some shared memory space  
         */  
        SnapMgrInit();  
        BTreeShmemInit();  
        SyncScanShmemInit();  
        AsyncShmemInit();  
  

数据库同一时刻能锁多少对象？

数据库同一时刻能锁多少对象？由这这些参数决定，实际上主要原因是这些参数也决定了锁相关的共享内存区的大小。

共享内存区锁相关的slot被用完，就不能在锁其他对象了。

分别代表不同的含义。

1. max_locks_per_transaction (integer) – 平均每个事务的锁对象数量。

最多可以跟踪max_locks_per_transaction * (max_connections + max_prepared_transactions)这么多个对象锁。

The shared lock table tracks locks on   
max_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 historically proven sufficient,   
but you might need to raise this value if you have queries that touch many different tables in a single transaction,   
e.g. query of a parent table with many children.   
  
This parameter can only be set at server start.  
  
When running a standby server, you must set this parameter to the same or higher value than on the master server.   
  
Otherwise, queries will not be allowed in the standby server.  

2. max_pred_locks_per_transaction (integer) – 平均每个事务的预加锁数量（指串行隔离级别加载的预加锁）。

最多可以跟踪max_pred_locks_per_transaction * (max_connections + max_prepared_transactions)这么多个对象预加锁。

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.  

3. max_connections 最大允许的连接数.

4. max_prepared_transactions 最大允许的二阶段事务数量.

例子

假设

max_locks_per_transaction = 10  
  
max_prepared_transactions = 0  
  
max_connections = 40  

按照文档的说明，整个数据库集群在同一时刻可以被锁的对象数应该等于400.

要模拟的话非常简单, 例如在一个事务中创建表即可.

postgres=# \set VERBOSITY verbose  
postgres=# do language plpgsql $$  
declare  
begin  
  for i in 1..20000 loop  
    execute 'create table tblxxx'||i||'(id int)';   
  end loop;  
end;  
$$;  

报错如下，让你增加max_locks_per_transaction去，目的就是增加数据库启动时初始化的锁相关共享内存区大小，可以存放更多的锁SLOT.

WARNING:  53200: out of shared memory  
CONTEXT:  SQL statement "create table tblxxx5786(id int)"  
PL/pgSQL function inline_code_block line 5 at EXECUTE statement  
LOCATION:  ShmemAlloc, shmem.c:205  
ERROR:  53200: out of shared memory  
HINT:  You might need to increase max_locks_per_transaction.  
CONTEXT:  SQL statement "create table tblxxx5786(id int)"  
PL/pgSQL function inline_code_block line 5 at EXECUTE statement  
LOCATION:  LockAcquireExtended, lock.c:907  

内存占用情况

通过这些参数，估算它们的内存占用。

max_connections = 8     # (change requires restart)
# Note:  Increasing max_connections costs ~400 bytes of shared memory per 
# connection slot, plus lock space (see max_locks_per_transaction).
每个连接需要占用400字节内存. -- 至少这么多，会话跟踪相关的数据结构决定的。


#max_prepared_transactions = 0    # zero disables the feature
          # (change requires restart)
# Note:  Increasing max_prepared_transactions costs ~600 bytes of shared memory
# per transaction slot, plus lock space (see max_locks_per_transaction).
# It is not advisable to set max_prepared_transactions nonzero unless you
# actively intend to use prepared transactions.
每个二阶段事务，至少占用600字节共享内存

max_locks_per_transaction = 20   # min 10
          # (change requires restart)
# Note:  Each lock table slot uses ~270 bytes of shared memory, and there are
每个锁对象的跟踪，至少占用270字节共享内存。
# max_locks_per_transaction * (max_connections + max_prepared_transactions)
# lock table slots.
#max_pred_locks_per_transaction = 64  # min 10
          # (change requires restart)

参考

https://www.postgresql.org/docs/9.6/static/runtime-config-locks.html

https://github.com/adamhooper/openroad/blob/master/ami/files/postgresql.conf

digoal’s 大量PostgreSQL文章入口