PostgreSQL数据库事务锁分为两类：表级锁和行级锁
表级锁
表级锁有8种模式：
*ACCESS SHARE*
只与ACCESS EXCLUSIVE冲突。
SELECT命令在被引用的表上请求一个这种锁。通常，任何只读取表而不修改它的命令都请求这种锁模式。
ROW SHARE
与EXCLUSIVE和ACCESS EXCLUSIVE锁模式冲突。
SELECT FOR UPDATE和SELECT FOR SHARE命令会自动在目标表上请求ROW SHARE锁（且所有被引用但不是FOR SHARE/FOR UPDATE的其他表上，还会自动加上ACCESS SHARE锁）。
ROW EXCLUSIVE
与ROW SHARE锁相同，ROW EXCLUSIVE允许并发读取表，但是禁止修改表中数据。UPDATE，DELETE，INSERT命令会自动在目标表上请求这个锁（且所有被引用的其他表上还会自动加上的ACCESS SHARE锁）。通常情况下，所有会修改表数据的命令都会请求表的ROW EXCLUSIVE锁。
SHARE UPDATE EXCLUSIVE
这个模式保护一个表的模式不被并发修改，以及禁止在目标表上执行垃圾回收命令（VACUUM ）。
VACUUM（不带FULL选项），ANALYZE，CREATE INDEX CONCURRENTLY命令会自动请求这样的锁。
SHARE
SHARE锁允许并发的查询，但是禁止对表进行修改。
CREATE INDEX（不带CONCURRENTLY选项）语句会自动请求这种锁。
SHARE ROW EXCLUSIV*
SHARE ROW EXCLUSIVE锁禁止对表进行任何的并发修改，而且是独占锁，因此一个会话中只能获取一次。
任何SQL语句都不会自动请求这个锁模式。
EXCLUSIVE
EXCLUSIVE锁允许对目标表进行并发查询，但是禁止任何其他操作。
这个模式只允许并发加ACCESS SHARE锁，也就是说，只有对表的读动作可以和持有这个锁模式的事务并发执行。
任何SQL语句都不会在用户表上自动请求这个锁模式。然而在某些操作的时候，会在某些系统表上请求它。
ACCESS EXCLUSIVE
这个模式保证其所有者（事务）是可以访问该表的唯一事务。
ALTER TABLE，DROP TABLE，TRUNCATE，REINDEX命令会自动请求这种锁。
在LOCK TABLE命令没有明确声明需要的锁模式时，它是缺省锁模式。
手动获取表级锁：
可以在事务块内部执行LOCK [ TABLE ] [ ONLY ] name [ * ] [, …] [ IN lockmode MODE ] [ NOWAIT ]命令获取指定的表级锁。
postgres=# begin;
BEGIN
postgres=*# lock table t in SHARE UPDATE EXCLUSIVE mode;
LOCK TABLE
postgres=*#
postgres=# select *from pg_locks;
locktype | database | relation | page | tuple | virtualxid | transactionid | classid | objid | objsubid | virtualtransaction | pid | mode | granted | fastpath
------------+----------+----------+------+-------+------------+---------------+---------+-------+----------+--------------------+-------+--------------------------+---------+----------
virtualxid | | | | | 3/3003 | | | | | 3/3003 | 31700 | ExclusiveLock | t | t
relation | 13580 | 12141 | | | | | | | | 4/848 | 31997 | AccessShareLock | t | t
virtualxid | | | | | 4/848 | | | | | 4/848 | 31997 | ExclusiveLock | t | t
relation | 13580 | 16977 | | | | | | | | 3/3003 | 31700 | ShareUpdateExclusiveLock | t | f
(4 rows)
行级锁
PG9.4版本开始，行级锁支持4种模式：
FOR UPDATE
FOR UPDATE会导致由SELECT语句检索到的行被锁定，就好像它们要被更新。这可以阻止它们被其他事务锁定、修改或者删除，一直到当前事务结束。也就是说其他尝试UPDATE、DELETE、SELECT FOR UPDATE、SELECT FOR NO KEY UPDATE、SELECT FOR SHARE或者SELECT FOR KEY SHARE这些行的事务将被阻塞，直到当前事务结束。
FOR NO KEY UPDATE
行为与FOR UPDATE类似，不过获得的锁较弱：这种锁将不会阻塞尝试在相同行上获得锁的SELECT FOR KEY SHARE命令。任何不获取FOR UPDATE锁的UPDATE也会获得这种锁模式。
FOR SHARE
行为与FOR NO KEY UPDATE类似，不过它在每个检索到的行上获得一个共享锁而不是排他锁。一个共享锁会阻塞其他事务在这些行上执行UPDATE、DELETE、SELECT FOR UPDATE或者SELECT FOR NO KEY UPDATE，但是它不会阻止它们执行SELECT FOR SHARE或者SELECT FOR KEY SHARE。
FOR KEY SHARE
行为与FOR SHARE类似，不过锁较弱：SELECT FOR UPDATE会被阻塞，但是SELECT FOR NO KEY UPDATE不会被阻塞。一个键共享锁会阻塞其他事务执行修改键值的DELETE或者UPDATE，但不会阻塞其他UPDATE，也不会阻止SELECT FOR NO KEY UPDATE、SELECT FOR SHARE或者SELECT FOR KEY SHARE。
手动获取行级锁：
可以在事务块内部使用SELECT…FOR { UPDATE | NO KEY UPDATE | SHARE | KEY SHARE } [ OF table_name [, …] ] [ NOWAIT | SKIP LOCKED ] […]命令获取行级锁。
postgres=# begin;
BEGIN
postgres=*# select * from t where info='a' for update;
info
------
a
(1 row)
postgres=*#
postgres=# select *from pg_locks;
locktype | database | relation | page | tuple | virtualxid | transactionid | classid | objid | objsubid | virtualtransaction | pid | mode | granted | fastpath
---------------+----------+----------+------+-------+------------+---------------+---------+-------+----------+--------------------+-------+-----------------+---------+----------
relation | 13580 | 16977 | | | | | | | | 3/3000 | 31700 | RowShareLock | t | t
virtualxid | | | | | 3/3000 | | | | | 3/3000 | 31700 | ExclusiveLock | t | t
relation | 13580 | 12141 | | | | | | | | 4/847 | 31997 | AccessShareLock | t | t
virtualxid | | | | | 4/847 | | | | | 4/847 | 31997 | ExclusiveLock | t | t
transactionid | | | | | | 1320 | | | | 3/3000 | 31700 | ExclusiveLock | t | f
(5 rows)
查询数据库中锁阻塞信息
查询视图pg_locks可以获取数据库中事务锁阻塞信息
select * from pg_locks;
postgres=# select * from pg_locks;
locktype | database | relation | page | tuple | virtualxid | transactionid | classid | objid | objsubid | virtualtransaction | pid | mode | granted | fastpath
---------------+----------+----------+------+-------+------------+---------------+---------+-------+----------+--------------------+-----+------------------+---------+----------
relation | 13580 | 12141 | | | | | | | | 6/407 | 676 | AccessShareLock | t | t
virtualxid | | | | | 6/407 | | | | | 6/407 | 676 | ExclusiveLock | t | t
relation | 13580 | 16977 | | | | | | | | 4/857 | 749 | AccessShareLock | t | t
relation | 13580 | 16977 | | | | | | | | 4/857 | 749 | RowExclusiveLock | t | t
virtualxid | | | | | 4/857 | | | | | 4/857 | 749 | ExclusiveLock | t | t
relation | 13580 | 16977 | | | | | | | | 5/72 | 548 | RowExclusiveLock | t | t
virtualxid | | | | | 5/72 | | | | | 5/72 | 548 | ExclusiveLock | t | t
relation | 13580 | 16977 | | | | | | | | 3/3032 | 747 | RowExclusiveLock | t | t
virtualxid | | | | | 3/3032 | | | | | 3/3032 | 747 | ExclusiveLock | t | t
transactionid | | | | | | 1332 | | | | 3/3032 | 747 | ShareLock | f | f
transactionid | | | | | | 1334 | | | | 3/3032 | 747 | ExclusiveLock | t | f
transactionid | | | | | | 1335 | | | | 5/72 | 548 | ExclusiveLock | t | f
transactionid | | | | | | 1332 | | | | 4/857 | 749 | ExclusiveLock | t | f
tuple | 13580 | 16977 | 0 | 1 | | | | | | 5/72 | 548 | ExclusiveLock | f | f
tuple | 13580 | 16977 | 0 | 1 | | | | | | 3/3032 | 747 | ExclusiveLock | t | f
(15 rows)
其中各列的描述如下：
locktype:被锁定对象的类型,共11种。
| Lock 等待事件 | 描述 |
|---|---|
| advisory | 等待获取咨询用户锁。 |
| extend | 等待扩展关系。 |
| frozenid | 等待更新 pg_database.datfrozenxid 和 pg_database.datminmxid。 |
| object | 等待获取对非关系数据库对象的锁定。 |
| page | 等待获取关系页面上的锁。 |
| relation | 等待获取关系上的锁。 |
| spectoken | 等待获取推测性插入锁。 |
| transactionid | 等待交易完成。 |
| tuple | 等待获取元组上的锁。 |
| userlock | 等待获取用户锁。 |
| virtualxid | 等待获取虚拟事务 ID 锁。 |
database:被锁定对象所在数据库的OID。
relation:被锁定对象的OID。
page:被锁定对象的页面编号。
tuple:被锁定对象的页面里边的行编号。
virtualxid:事务的虚拟ID。
transactionid:事务的ID。
classid:包含该被锁定对象的系统表的OID。
objid:被锁定对象在其系统表内的OID。
objsubid:对于表的一个字段，这是字段编号；对于其他对象类型，这个字段是零。
virtualtransaction:持有此锁或者在等待此锁的事务的虚拟ID。
pid:持有或者等待这个锁的服务器线程的逻辑ID。
mode:这个线程持有的或者是期望的锁模式。
granted: 如果锁是持有锁，则为TRUE。如果锁是等待锁，则为FALSE。
fastpath:如果通过fast-path获得锁，则为TRUE；如果通过主要的锁表获得，则为FALSE。
阻塞会话查询：
select pid,pg_blocking_pids(pid),wait_event_type,wait_event,query from pg_stat_activity ;
postgres=# select pid,pg_blocking_pids(pid),wait_event_type,wait_event,query from pg_stat_activity ;
pid | pg_blocking_pids | wait_event_type | wait_event | query
-------+------------------+-----------------+---------------------+-------------------------------------------------------------------------------------------
18732 | {} | Activity | LogicalLauncherMain |
18729 | {} | Activity | AutoVacuumMain |
747 | {749} | Lock | transactionid | update t set info='c' where info='a';
749 | {} | Client | ClientRead | select * from t;
548 | {747} | Lock | tuple | update t set info='c' where info='a';
676 | {} | | | select pid,pg_blocking_pids(pid),wait_event_type,wait_event,query from pg_stat_activity ;
18727 | {} | Activity | BgWriterHibernate |
18726 | {} | Activity | CheckpointerMain |
18728 | {} | Activity | WalWriterMain |
(9 rows)
pg_blocking_pids(int)函数，返回阻塞指定进程ID的服务器会话的进程 ID 。
手动kill掉锁阻塞会话
postgres=# select pg_terminate_backend(749);
pg_terminate_backend
----------------------
t
(1 row)
pg_terminate_backend(int)函数，用于中止一个后端进程。
锁等待超时：
当申请锁的操作等待时间操作数据库lock_timeout参数的设定值时，系统会报错。lock_timeout参数默认0,即锁等待无超时时间限制。
死锁检测：
PostgreSQL数据库能够自动侦测到死锁，然后退出其中一个事务，从而允许其他事物执行。
当申请锁的等待时间超过数据库参数deadlock_timeout的设定值时，系统会检查是否产生了死锁。deadlock_timeout参数默认值为1s。
测试：
测试前修改数据库参数log_lock_waits参数值为on，控制当一个会话为获得一个锁等到超过deadlock_timeout时，产生一个日志消息。默认是off不记录。
#会话1：
postgres=# begin;
BEGIN
postgres=*# update t set info = 'c' where info ='a';
UPDATE 1
postgres=*#
#会话2：
postgres=# show lock_timeout;
lock_timeout
\--------------
1min
(1 row)
postgres=# begin;
BEGIN
postgres=*# update t set info = 'c' where info ='a';
ERROR: canceling statement due to lock timeout
CONTEXT: while updating tuple (0,1) in relation "t"
postgres=!#
#日志：会话2执行update语句时，发生锁等待，等待时间达到1S时，系统进行死锁检测。锁等待时间达到lock_timeout设置的60S时，会话2执行的update操作报错退出，提示锁等待超时。
2021-12-10 16:26:24.981 CST,"postgres","postgres",32018,"[local]",61b30f2c.7d12,3,"UPDATE waiting",2021-12-10 16:26:20 CST,5/66,1318,LOG,00000,"process 32018 still waiting for ShareLock on transaction 1317 after 1000.132 ms","Process holding the lock: 31700. Wait queue: 32018.",,,,"while updating tuple (0,1) in relation ""t""","update t set info = 'c' where info ='a';",,,"psql","client backend"
2021-12-10 16:26:48.099 CST,,,18726,,619a5f18.4926,47,,2021-11-21 23:00:40 CST,,0,LOG,00000,"checkpoint starting: time",,,,,,,,,"","checkpointer"
2021-12-10 16:26:48.219 CST,,,18726,,619a5f18.4926,48,,2021-11-21 23:00:40 CST,,0,LOG,00000,"checkpoint complete: wrote 1 buffers (0.0%); 0 WAL file(s) added, 0 removed, 0 recycled; write=0.106 s, sync=0.002 s, total=0.120 s; sync files=1, longest=0.002 s, average=0.002 s; distance=0 kB, estimate=127 kB",,,,,,,,,"","checkpointer"
2021-12-10 16:27:23.981 CST,"postgres","postgres",32018,"[local]",61b30f2c.7d12,4,"UPDATE",2021-12-10 16:26:20 CST,5/66,1318,ERROR,55P03,"canceling statement due to lock timeout",,,,,"while updating tuple (0,1) in relation ""t""","update t set info = 'c' where info ='a';",,,"psql","client backend"
