本节介绍了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](https://www.percona.com/blog/2018/10/30/postgresql-locking-part-3-lightweight-locks/) [PostgreSQL 自旋锁浅析](https://mp.weixin.qq.com/s?__biz=MzA4NjQzNjUyOA==&mid=2650559322&idx=1&sn=28473a47366fedf3e707540fa3922c62&chksm=87c04c85b0b7c593f508a07a73484216f5bcf74237724b800533eac41493c553828caacad389&scene=0&xtrack=1&key=0f0abb20a7092e0ca6dcae7ef22dbc4f6432dc06434d2cc647e0aa0d0254578c005f91d8f87a1ddee53b8bc9d2d37a24e3dc4a13577a0fcb222e33d79f6b56f15d2dec7adb98a0a305be4c753357f52a&ascene=1&uin=MjgxMzQxMTc0MQ%3D%3D&devicetype=Windows+10&version=62060833&lang=zh_CN&pass_ticket=wYRkmpb%2Bj0co5FYJobjrlTWveJYq66ID1Rh6ByZdca2pPWoDe0eSg7lTi0jI5IjS) [PG src README](https://github.com/postgres/postgres/blob/master/src/backend/storage/lmgr/README)