PostgreSQL开发与实战（8.2）PG的锁2

作者：太阳

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 detected
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.
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 detected
2020-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)

更多技术信息请查看云掣官网https://yunche.pro/?t=yrgw