本节介绍了PostgreSQL中的lightweight locks.

Lightweight locks (LWLocks). These locks are typically used to
interlock access to datastructures in shared memory. LWLocks support
both exclusive and shared lock modes (for read/write and read-only
access to a shared object). There is no provision for deadlock
detection, but the LWLock manager will automatically release held
LWLocks during elog() recovery, so it is safe to raise an error while
holding LWLocks. Obtaining or releasing an LWLock is quite fast (a few
dozen instructions) when there is no contention for the lock. When a
process has to wait for an LWLock, it blocks on a SysV semaphore so as
to not consume CPU time. Waiting processes will be granted the lock in
arrival order. There is no timeout.

简介
PG中的lightweight locks(LWLocks,在其他数据库称为Latchs)轻量级锁用于控制内存访问,只有两种级别的锁:shared和exclusive.
通过系统视图pg_stat_activity可查看关于Lock/Wait的相关信息

[local]:5432 pg12@testdb=# select * from  pg_stat_activity where pid = 5914;
-[ RECORD 1 ]----+------------------------------------
datid            | 16384
datname          | testdb
pid              | 5914
usesysid         | 10
usename          | pg12
application_name | psql
client_addr      | 
client_hostname  | 
client_port      | -1
backend_start    | 2019-08-22 11:40:58.504462+08
xact_start       | 2019-08-22 12:16:02.528978+08
query_start      | 2019-08-22 12:16:04.372427+08
state_change     | 2019-08-22 12:16:04.374888+08
wait_event_type  | Client
wait_event       | ClientRead
state            | idle in transaction
backend_xid      | 716
backend_xmin     | 
query            | delete from t_prewarm where id = 1;
backend_type     | client backend
Time: 14.262 ms
[local]:5432 pg12@testdb=# select * from  pg_stat_activity where pid = 5964;
-[ RECORD 1 ]----+------------------------------------------------
datid            | 16384
datname          | testdb
pid              | 5964
usesysid         | 10
usename          | pg12
application_name | psql
client_addr      | 
client_hostname  | 
client_port      | -1
backend_start    | 2019-08-22 11:41:10.420664+08
xact_start       | 2019-08-22 12:16:11.812598+08
query_start      | 2019-08-22 12:16:18.718567+08
state_change     | 2019-08-22 12:16:18.718572+08
wait_event_type  | Lock
wait_event       | transactionid
state            | active
backend_xid      | 
backend_xmin     | 716
query            | select * from t_prewarm where id = 1 for share;
backend_type     | client backend
Time: 4.655 ms

LWLock类型
WALInsertLock:保护WAL buffers.可以增加WAL buffers来改善争用.设置 synchronous_commit=off和full_page_writes=off可减少争用,但不建议这样做.
WALWriteLock:WAL Record刷盘或WAL segment切换时使用该锁保护.synchronous_commit=off 可清除刷盘等待.full_page_writes=off可减少刷盘数据大小.
LockMgrLock:在只读工作负载下会出现在等待事件中.不论大小,都会锁定relations.该Lock不是单个锁,而是至少16个分区.因此,在基准测试时使用多个表显得很重要.
ProcArrayLock:包含ProcArray结构体.
CLogControlLock:包含CLogControl结构体,如果在pg_stat_activity中频繁出现,那应检查$PGDATA/pg_clog(PG11+:pg_xact)目录,应位于已缓存的文件系统上.
SInvalidReadLock:包含sinval结构体.Readers 使用共享锁,而SICleanupQueue和其他数组范围内的更新则请求独占锁.如果共享缓存存在较大的压力,拿在系统视图上可看到该结构体的出现,增大shared_buffers可减少争用.
BufMappingLocks:包含buffers的区域.PG设置了128个buffer区域用于管理整个缓存.

Spinlocks
最低级别的锁是自旋锁,使用与CPU特定的机制实现.

参考资料
PostgreSQL locking, part 3: lightweight locks
PostgreSQL 自旋锁浅析
 PG src README

PostgreSQL DBA(85) - Locks(lightweight locks)