作者：太阳 1、死锁PostgreSQL自动检测死锁情况并会自动回滚其中一个事务进行处理，从而其他事务完成。 db1=# select * from t1 where id in (1,2,3); id | id2 | info | crt_time----+------+----------------------------------+---------------------------- 2 | 327 | aaaaaaaaa | 2020-10-12 16:20:50.278564 3 | 6098 | 8dc6d07062720d35f7824dceea5a3ef9 | 2020-10-12 17:20:50.278572(2 rows)事务一 事务二 begin; begin; update t1 set info='bbbbbbbbb' where id=3; update t1 set info='bbbbbbbbb' where id=2; - update t1 set info='cccccccc' where id=3;//被夯住 update t1 set info='cccccccc' where id=2; //返回报错 update执行成功 commit //自动rollback commit; ## 事务一update报错内容ERROR: deadlock detectedDETAIL: Process 26174 waits for ShareLock on transaction 1738606; blocked by process 26178.Process 26178 waits for ShareLock on transaction 1738607; blocked by process 26174.HINT: See server log for query details.CONTEXT: while updating tuple (51,123) in relation "t1"## 事务二提交后数据变更结果db1=# select * from t1 where id in (1,2,3); id | id2 | info | crt_time----+------+-----------+---------------------------- 2 | 327 | bbbbbbbbb | 2020-10-12 16:20:50.278564 3 | 6098 | cccccccc | 2020-10-12 17:20:50.278572(2 rows)## server log详细内容报错2020-10-15 16:53:03.688 CST [26174] ERROR: deadlock detected2020-10-15 16:53:03.688 CST [26174] DETAIL: Process 26174 waits for ShareLock on transaction 1738606; blocked by process 26178.Process 26178 waits for ShareLock on transaction 1738607; blocked by process 26174.Process 26174: update t1 set info='cccccccc' where id=2;Process 26178: update t1 set info='cccccccc' where id=3;2020-10-15 16:53:03.688 CST [26174] HINT: See server log for query details.2020-10-15 16:53:03.688 CST [26174] CONTEXT: while updating tuple (51,123) in relation "t1"2020-10-15 16:53:03.688 CST [26174] STATEMENT: update t1 set info='cccccccc' where id=2;2、咨询锁2.1 什么是咨询锁咨询锁是一种具有应用层面的锁，数据库中一般不会主动调用相关咨询锁资源，需要开发同学在应用层决定是否需要使用这类锁。咨询锁使用的模型是悲观锁模型，相对于传统意义上的锁，咨询锁更快，可以避免表膨胀，并在会话结束时由服务器自动清除。 咨询锁可分为会话/事务级别： 会话级别的锁需要显式释放，随着连接的关闭自动释放事务级别的锁也需要显式释放，或者会随着事务的结束（提交或者回滚）一并释放锁级别： _lock ： 会话级别锁_xact_lock ：事务级别锁排它锁/共享锁的获取以及释放： _lock() : 获取指定参数下的会话/事务级别排它锁资源_lock_shared() : 获取指定参数下的会话/事务级别共享锁资源_unlock() ： 释放指定参数下的会话级别排它锁资源_unlock_shared() ： 释放指定参数下的会话级别共享锁资源_unlock_all() ： 释放当前会话下持有的所有会话级别锁资源锁获取模式： pg_try_advisory ： 若无法获取资源则一直等待pg_advisory ： 若无法获取资源，直接返回false，不会一直等待2.2 咨询锁相关函数函数名称 返回类型 描述 pg_advisory_lock(key bigint) void session级别排他锁 pg_advisory_lock(key1 int, key2 int) void session级别排他锁 pg_advisory_lock_shared(key bigint) void session级别共享锁 pg_advisory_lock_shared(key1 int, key2 int) void session级别共享锁 pg_advisory_unlock(key bigint) boolean 释放session级别排他锁 pg_advisory_unlock(key1 int, key2 int) boolean 释放session级别排他锁 pg_advisory_unlock_all() void 释放本session持有的所有session级别的锁 pg_advisory_unlock_shared(key bigint) boolean 释放session级别共享锁 pg_advisory_unlock_shared(key1 int, key2 int) boolean 释放session级别共享锁 pg_advisory_xact_lock(key bigint) void 获取事务级别排他锁 pg_advisory_xact_lock(key1 int, key2 int) void 获取事务级别排他锁 pg_advisory_xact_lock_shared(key bigint) void 获取事务级别共享锁 pg_advisory_xact_lock_shared(key1 int, key2 int) void 获取事务级别共享锁 pg_try_advisory_lock(key bigint) boolean 试图获取session级别排他锁，成功返回true，否则返回false pg_try_advisory_lock(key1 int, key2 int) boolean 试图获取session级别排他锁，成功返回true，否则返回false pg_try_advisory_lock_shared(key bigint) boolean 试图获取session级别共享锁，成功返回true，否则返回false pg_try_advisory_lock_shared(key1 int, key2 int) boolean 试图获取session级别共享锁，成功返回true，否则返回false pg_try_advisory_xact_lock(key bigint) boolean 试图获取事务级别排他锁，成功返回true，否则返回false pg_try_advisory_xact_lock(key1 int, key2 int) boolean 试图获取事务级别排他锁，成功返回true，否则返回false pg_try_advisory_xact_lock_shared(key bigint) boolean 试图获取事务级别共享锁，成功返回true，否则返回false pg_try_advisory_xact_lock_shared(key1 int, key2 int) boolean 试图获取事务级别共享锁，成功返回true，否则返回false 锁分为64位和32位，bigint表示64位、int表示32位。对于同一行记录，若分为使用64位、32位资源锁进行加锁，是不会互相干预的，他们是不同空间上的锁。 2.3 资源锁测试1、库粒度的资源锁 ## 会话一 请求对t2表的id=2进行添加锁，加锁成功。由于pg_try_advisory_lock指定参数为id，其实时在数据库级别添加了锁资源db1=# select pg_try_advisory_lock(id),id from t2 where id=2; pg_try_advisory_lock | id----------------------+---- t | 2(1 row)## 会话二 请求对t1表的id=2进行加锁，加锁失败db1=# select pg_try_advisory_lock(id),id from t1 where id=2; pg_try_advisory_lock | id----------------------+---- f | 2(1 row)## 会话一 释放已申请锁资源db1=# select pg_advisory_unlock(id),id from t2 where id = 2; pg_advisory_unlock | id--------------------+---- t | 2(1 row)## 会话二 再次尝试申请t1表id=2的锁资源，加锁成功db1=# select pg_try_advisory_lock(id),id from t1 where id=2; pg_try_advisory_lock | id----------------------+---- t | 2(1 row)2、行粒度的资源锁 ## 会话一尝试对t1表的id=2记录获取资源锁，获取成功db1=# select pg_try_advisory_lock(cast('t1'::regclass::oid as int),id),id from t1 where id=2; pg_try_advisory_lock | id----------------------+---- t | 2(1 row)## 会话二尝试对t1表的id=2记录获取资源锁，获取失败db1=# select pg_try_advisory_lock(cast('t1'::regclass::oid as int),id),id from t1 where id=2; pg_try_advisory_lock | id----------------------+---- f | 2(1 row)## 会话二尝试对t1表的id=2记录获取资源锁，获取成功db1=# select pg_try_advisory_lock(cast('t1'::regclass::oid as int),id),id from t1 where id=3; pg_try_advisory_lock | id----------------------+---- t | 3(1 row)更多技术信息请查看云掣官网云掣YunChe - 可观测运维专家 | 大数据运维托管 | 云MSP服务