Postgresql 锁查看
之前版本 PostgreSQL 的 pg_stat_activity 视图的 waiting 字段判断会话是否等待锁资源（通俗地讲， waiting 值为true表示申请不到锁资源处于等待状态），但是并不会给出具体的锁的信息，9.6 版本之后 pg_stat_activity 视图的 waiting 字段被 wait_event_type 和 wait_event 字段取代，这两个字段分别代表等待事件的类型、等待事件名称；
SELECT pid, wait_event_type, wait_event FROM pg_stat_activity；
| Wait Event Type | Wait Event Name | Description |
|---|---|---|
| LWLockNamed | ShmemIndexLock | Waiting to find or allocate space in shared memory. |
| OidGenLock | Waiting to allocate or assign an OID. | |
| XidGenLock | Waiting to allocate or assign a transaction id. | |
| ProcArrayLock | Waiting to get a snapshot or clearing a transaction id at transaction end. | |
| SInvalReadLock | Waiting to retrieve or remove messages from shared invalidation queue. | |
| SInvalWriteLock | Waiting to add a message in shared invalidation queue. | |
| WALBufMappingLock | Waiting to replace a page in WAL buffers. | |
| WALWriteLock | Waiting for WAL buffers to be written to disk. | |
| ControlFileLock | Waiting to read or update the control file or creation of a new WAL file. | |
| CheckpointLock | Waiting to perform checkpoint. | |
| CLogControlLock | Waiting to read or update transaction status. | |
| SubtransControlLock | Waiting to read or update sub-transaction information. | |
| MultiXactGenLock | Waiting to read or update shared multixact state. | |
| MultiXactOffsetControlLock | Waiting to read or update multixact offset mappings. | |
| MultiXactMemberControlLock | Waiting to read or update multixact member mappings. | |
| RelCacheInitLock | Waiting to read or write relation cache initialization file. | |
| CheckpointerCommLock | Waiting to manage fsync requests. | |
| TwoPhaseStateLock | Waiting to read or update the state of prepared transactions. | |
| TablespaceCreateLock | Waiting to create or drop the tablespace. | |
| BtreeVacuumLock | Waiting to read or update vacuum-related information for a Btree index. | |
| AddinShmemInitLock | Waiting to manage space allocation in shared memory. | |
| AutovacuumLock | Autovacuum worker or launcher waiting to update or read the current state of autovacuum workers. | |
| AutovacuumScheduleLock | Waiting to ensure that the table it has selected for a vacuum still needs vacuuming. | |
| SyncScanLock | Waiting to get the start location of a scan on a table for synchronized scans. | |
| RelationMappingLock | Waiting to update the relation map file used to store catalog to filenode mapping. | |
| AsyncCtlLock | Waiting to read or update shared notification state. | |
| AsyncQueueLock | Waiting to read or update notification messages. | |
| SerializableXactHashLock | Waiting to retrieve or store information about serializable transactions. | |
| SerializableFinishedListLock | Waiting to access the list of finished serializable transactions. | |
| SerializablePredicateLockListLock | Waiting to perform an operation on a list of locks held by serializable transactions. | |
| OldSerXidLock | Waiting to read or record conflicting serializable transactions. | |
| SyncRepLock | Waiting to read or update information about synchronous replicas. | |
| BackgroundWorkerLock | Waiting to read or update background worker state. | |
| DynamicSharedMemoryControlLock | Waiting to read or update dynamic shared memory state. | |
| AutoFileLock | Waiting to update the postgresql.auto.conf file. | |
| ReplicationSlotAllocationLock | Waiting to allocate or free a replication slot. | |
| ReplicationSlotControlLock | Waiting to read or update replication slot state. | |
| CommitTsControlLock | Waiting to read or update transaction commit timestamps. | |
| CommitTsLock | Waiting to read or update the last value set for the transaction timestamp. | |
| ReplicationOriginLock | Waiting to setup, drop or use replication origin. | |
| MultiXactTruncationLock | Waiting to read or truncate multixact information. | |
| LWLockTranche | clog | Waiting for I/O on a clog (transaction status) buffer. |
| commit_timestamp | Waiting for I/O on commit timestamp buffer. | |
| subtrans | Waiting for I/O a subtransaction buffer. | |
| multixact_offset | Waiting for I/O on a multixact offset buffer. | |
| multixact_member | Waiting for I/O on a multixact_member buffer. | |
| async | Waiting for I/O on an async (notify) buffer. | |
| oldserxid | Waiting to I/O on an oldserxid buffer. | |
| wal_insert | Waiting to insert WAL into a memory buffer. | |
| buffer_content | Waiting to read or write a data page in memory. | |
| buffer_io | Waiting for I/O on a data page. | |
| replication_origin | Waiting to read or update the replication progress. | |
| replication_slot_io | Waiting for I/O on a replication slot. | |
| proc | Waiting to read or update the fast-path lock information. | |
| buffer_mapping | Waiting to associate a data block with a buffer in the buffer pool. | |
| lock_manager | Waiting to add or examine locks for backends, or waiting to join or exit a locking group (used by parallel query). | |
| predicate_lock_manager | Waiting to add or examine predicate lock information. | |
| Lock | relation | Waiting to acquire a lock on a relation. |
| extend | Waiting to extend a relation. | |
| page | Waiting to acquire a lock on page of a relation. | |
| tuple | Waiting to acquire a lock on a tuple. | |
| transactionid | Waiting for a transaction to finish. | |
| virtualxid | Waiting to acquire a virtual xid lock. | |
| speculative token | Waiting to acquire a speculative insertion lock. | |
| object | Waiting to acquire a lock on a non-relation database object. | |
| userlock | Waiting to acquire a userlock. | |
| advisory | Waiting to acquire an advisory user lock. | |
| BufferPin | BufferPin | Waiting to acquire a pin on a buffer. |
备注：通过以上表格， wait_event_type 主要分类四类：
- LWLockNamed：表示backend后台进程等待某种特定的轻量级锁；
- LWLockTranche：表示backend后台进程等待一组相关轻量级锁；
- Lock：表示backend后台进程等待重量级的锁，通常是指 relation、tuple、page、transactionid 等子类型锁；
- BufferPin：表示server process 后台进程等待 buffer pin，手册上解释为 Waiting to acquire a pin on a buffer，比较难理解，以后想想如何模拟此场景。
