PostgreSQL（八）锁的相关操作

一、锁存在的意义

在了解 PostgreSQL 锁之前，我们需要了解锁存在的意义是啥？

当多个会话同时访问数据库的同一数据时，理想状态是为所有会话提供高效的访问，同时还要维护严格的数据一致性。那数据一致性通过什么来维护呢，就是通过 MVCC（多版本并发控制） 。

MVCC（多版本并发控制） ：每个SQL语句看到的都只是当前事务开始的数据快照，而不管底层数据的当前状态。

这样可以保护语句不会看到在相同数据上由其他连接执行更新的并发事务造成的不一致数据，为每一个数据库会话提供事务隔离。MVCC 避免了传统的数据库系统的锁定方法，将通过锁争夺最小化的方法来达到多会话并发访问时的性能最大化目的。

PostgreSQL 提供了多种锁模式用于控制对表中数据的并发访问，其中最主要的是表级锁与行级锁，除此之外还有页级锁、咨询锁等等，接下来主要介绍表级锁与行级锁。

二、表级锁

表级锁通常会在执行各种命令执行时自动获取，或者通过在事务中使用LOCK语句显示获取。

每种锁都有自己的冲突集合。

表级锁：两个事务在同一时刻不能在同一个表上持有互相冲突的锁，但是可以同时持有不冲突的锁。

表级锁共有八种模式，其存在于PG的共享内存中，可以通过 pg_locks 系统视图查阅。

1.ACCESS SHARE

SELECT 命令在被引用的表上会获得一个这种模式的锁。通常，任何只读取表而不修改它的查询都将获取这种表模式。

2.ROW SHARE

SELECT FOR UPDATE 和 SELECT FOR SHARE 命令在目标表上会获得一个这种模式的锁。（加上在被引用但没有选择 FOR UPDATE / FOR SHARE 的任何其他表上的 ACCESS SHARE 锁。）

3.ROW EXCLUSIVE

UPDATE、DELETE 和 INSERT 命令在目标表上会获得一个这种模式的锁。（加上在任何其他被引用表上的 ACCESS SHARE锁。）通常，这种锁模式将被任何修改表中数据的命令取得。

4.SHARE UPDATE EXCLUSIVE

VACUUM（不带FULL）、ANALYZE、CREATE INDEX CONCURRENTLY、REINDEX CONCURRENTLY、CREATE STATISTICS 命令以及某些 ALTER INDEX 和 ALTER TABLE 命令的变体会获得。这种模式保护一个表不受并发模式改变和 VACUUM 运行的影响。

5.SHARE

CREATE INDEX（不带CONCURRENTLY） 命令会获得。

这种模式保护一个表不受并发数据改变的影响。

6.SHARE ROW EXCLUSIVE

CREATE TRIGGER 命令和某些形式的 ALTER TABLE 命令会获得。

这种模式保护一个表不受并发数据修改所影响，并且是自排他的，这样在同一个时刻只能有一个会话持有它。

7.EXCLUSIVE

REFRESH METERIALIZED VIEW CONCURRENTLY 命令会获得。

这种模式只允许并发的ACCESS SHARE锁，即只有来自于表的读操作可以与一个持有该锁模式的事务并行处理。

8.ACCESS EXCLUSIVE

ALTER TABLE、DROP TABLE、TRUNCATE、REINDEX、CLUSTER、VACUUM FULL 和 REFRESH MATERIALIZED VIEW（不带CONCURRENTLY）命令会获得。很多形式的 ALTER INDEX 和 ALTER TABLE 也在这个层面上获得锁。这也是未显式指定模式的 LOCK TABLE 命令的默认锁模式。

这种模式与所有模式的锁冲突。这种模式保持者是访问该表的唯一事务。

9.表级锁模式冲突表

（注：X表示冲突。）

怎么去看上面这个图呢？我们下面举两个例子说明一下：

10.示例一

当一个会话运行了 update 语句，此时会话表上的锁模式为 ROW EXCLUSIVE，从上图我们可以看出 ROW EXCLUSIVE 与 SHARE、SHARE ROW、ROW EXCLUSIVE、EXCLUSIVE 和 ACCESS EXCLUSIVE 锁模式冲突。

也就是说在这个会话未提交事务释放锁之前，我们不能做申请 SHARE、SHARE ROW、ROW EXCLUSIVE、EXCLUSIVE 和 ACCESS EXCLUSIVE 锁模式相关的操作，例如 CREATE INDEX（不带CONCURRENTLY）、ALTER TABLE、DROP TABLE、TRUNCATE、REINDEX、CLUSTER、VACUUM FULL 和 REFRESH MATERIALIZED VIEW（不带CONCURRENTLY）等。

我们先创建一个测试数据库：

# 创建测试用户
create user root password 'root';
# 创建测试数据库
create database mydb owner root encoding UTF8;
# 创建和测试用户同名Schema
create schema AUTHORIZATION CURRENT_USER;

我们创建一张测试表 t_user，并插入一条测试数据：

create table "t_user" (
	"id" bigserial not null,
	"username" varchar (64) not null,
	constraint t_user_pk primary key (id)
);
insert into t_user values(1, 'ACGkaka');

会话一： 执行 update 语句。

begin;
update t_user set username='ACGkaka1' where id=1;

会话二： 执行 alter table 语句，这时会处于等待状态。

alter table t_user add dept_no int;

执行SQL，查看锁等待情况：（SQL参考附录一）

注：Lock_Granted: true即为堵塞源。

直到“会话一”结束，“会话二”语句才执行成功。

11.示例二

当一个会话运行了 truncate 语句，此时会话表上的锁模式为 ACCESS EXCLUSIVE，从图上我们可以看到这种模式和所有的锁模式都冲突。这意味着在当前会话未结束之前，这个表上的其他操作都做不了。

会话一： 执行 truncate 语句。

会话二： 执行 select 语句时处于等待状态。

执行SQL，查看锁等待情况：（SQL参考附录一）

注：Lock_Granted: true即为堵塞源。

直到“会话一”结束，“会话二”语句才执行成功。

通过上面2个示例，应该都比较了解各种锁模式冲突的情况了，接下来我们介绍行级锁。

三、行级锁

行级锁：同一个事务可能会在相同的行上保持冲突的锁，甚至是在不同的子事务中。但是除此之外，两个事务永远不可能在相同的行上持有冲突的锁。

行级锁不影响数据查询，它们只阻塞对同一行的写入者和加锁者。行级锁在事务结束时或保存点回滚的时候释放，就像表级锁一样。下面是常用的行级锁模式：

1.FOR UPDATE

FOR UPDATE 会导致由 SELECT 语句检索到的行被锁定，就好像它们要被更新。这可以阻止它们被其他事务锁定、修改或者删除，直到当前事务结束。

也就是说其他尝试 UPDATE、DELETE、SELECT FOR UPDATE、SELECT FOR NO KEY UPDATE、SELECT FOR SHARE 或者 SELECT FOR KEY SHARE 这些行的事务将被阻塞，直到当前事务结束。

反过来，SELECT FOR UPDATE 将等待已经在相同行上运行以上这些命令的并发事务，并且接着锁定并且返回被更新的行（或者没有行，因为行可能已被删除）。

2.FOR NO KEY UPDATE

行为与 FOR UPDATE 类似，不过获得的锁较弱，这种锁将不会阻塞尝试在相同行上获得锁的 SELECT FOR KEY SHARE 命令。任何不获取 FOR UPDATE 锁的 UPDATE 也会获得这种锁模式。

3.FOR SHARE

行为与 FOR NO KEY UPDATE 类似，不过它在每个检索到的杭上获得一个共享锁而不是排他锁。

一个共享锁会阻塞其他食物在这些行上执行 UPDATE、DELETE、SELECT FOR UPDATE 或者 SELECT FOR NO KEY UPDATE，但是它不会阻止它们执行 SELECT FOR SHARE 或者 SELECT FRO KEY SHARE。

4.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。

5.表级锁模式冲突表

（注：X表示冲突。）

四、4个执行语句timeout参数

1.lock_timeout

lock_timeout：获取一个表，索引，行上的锁超过这个时间，直接报错，不等待，0为禁用。

2.statement_timeout

statement_timeout：当SQL语句的执行时间超过这个设置时间，终止执行SQL，0为禁用。

3.idle_in_transaction_session_timeout

idle_in_transaction_session_timeout：在一个空闲的事务中，空闲时间超过这个值，将视为超时，0为禁用。

4.deadlock_timeout

dealdlock_timeout：死锁时间超过这个值将直接报错，不会等待，默认设置为1s。

五、手动锁住整张表

1.lock 命令

在 PostGreSQL 中，可以使用 lock 语句来锁住整张表。语法如下：

lock table 表名 in 表级锁 mode;

表级锁就是我们第二部分所提到的内容：

注意： lock 命令需要在事务中执行才会锁表，否则就自动提交了。

2.示例一

例如：我们用 lock 命令锁住 t_user 表，表级锁就选级别最高的 access exclusive：

我们可以看到，事务中我们 成功将 t_user 表锁住了。接下来我们再开一个会话去 查询 t_user 表 验证下：

可以发现 t_user 表已经被成功锁死，无法查询。

只有输入 end; 提交事务后，被锁卡住的 select 语句才能正常执行：

六、附录

附录一：查看锁等待情况SQL

with
	t_wait as
	(
	select a.mode,a.locktype,a.database,a.relation,a.page,a.tuple,a.classid,a.granted,
		a.objid,a.objsubid,a.pid,a.virtualtransaction,a.virtualxid,a.transactionid,a.fastpath,
		b.state,b.query,b.xact_start,b.query_start,b.usename,b.datname,b.client_addr,b.client_port,b.application_name
	from pg_locks a,pg_stat_activity b where a.pid=b.pid and not a.granted
	),
	t_run as
	(
	select a.mode,a.locktype,a.database,a.relation,a.page,a.tuple,a.classid,a.granted,
		a.objid,a.objsubid,a.pid,a.virtualtransaction,a.virtualxid,a.transactionid,a.fastpath,
		b.state,b.query,b.xact_start,b.query_start,b.usename,b.datname,b.client_addr,b.client_port,b.application_name
	from pg_locks a,pg_stat_activity b where a.pid=b.pid and a.granted
	),
	t_overlap as
	(
	select r.* from t_wait w join t_run r on
		(
		r.locktype is not distinct from w.locktype and
		r.database is not distinct from w.database and
		r.relation is not distinct from w.relation and
		r.page is not distinct from w.page and
		r.tuple is not distinct from w.tuple and
		r.virtualxid is not distinct from w.virtualxid and
		r.transactionid is not distinct from w.transactionid and
		r.classid is not distinct from w.classid and
		r.objid is not distinct from w.objid and
		r.objsubid is not distinct from w.objsubid and
		r.pid <> w.pid
		)
	),
	t_unionall as
	(
	select r.* from t_overlap r
	union all
	select w.* from t_wait w
	)
	select locktype,datname,relation::regclass,page,tuple,virtualxid,transactionid::text,classid::regclass,objid,objsubid,
		string_agg(
			'Pid: '||case when pid is null then 'NULL' else pid::text end||chr(10)||
			'Lock_Granted: '||case when granted is null then 'NULL' else granted::text end||' , Mode: '||case when mode is null then 'NULL' else mode::text end||' , FastPath: '||case when fastpath is null then 'NULL' else fastpath::text end||' , VirtualTransaction: '||case when virtualtransaction is null then 'NULL' else virtualtransaction::text end||' , Session_State: '||case when state is null then 'NULL' else state::text end||chr(10)||
			'Username: '||case when usename is null then 'NULL' else usename::text end||' , Database: '||case when datname is null then 'NULL' else datname::text end||' , Client_Addr: '||case when client_addr is null then 'NULL' else client_addr::text end||' , Client_Port: '||case when client_port is null then 'NULL' else client_port::text end||' , Application_Name: '||case when application_name is null then 'NULL' else application_name::text end||chr(10)||
			'Xact_Start: '||case when xact_start is null then 'NULL' else xact_start::text end||' , Query_Start: '||case when query_start is null then 'NULL' else query_start::text end||' , Xact_Elapse: '||case when (now()-xact_start) is null then 'NULL' else (now()-xact_start)::text end||' , Query_Elapse: '||case when (now()-query_start) is null then 'NULL' else (now()-query_start)::text end||chr(10)||
			'SQL (Current SQL in Transaction): '||chr(10)||
			case when query is null then 'NULL' else query::text end,
			chr(10)||'--------'||chr(10)
		order by
			( case mode
			when 'INVALID' then 0
			when 'AccessShareLock' then 1
			when 'RowShareLock' then 2
			when 'RowExclusiveLock' then 3
			when 'ShareUpdateExclusiveLock' then 4
			when 'ShareLock' then 5
			when 'ShareRowExclusiveLock' then 6
			when 'ExclusiveLock' then 7
			when 'AccessExclusiveLock' then 8
			else 0
			end  ) desc,
			(case when granted then 0 else 1 end)
		) as lock_conflict
	from t_unionall
	group by
		locktype,datname,relation,page,tuple,virtualxid,transactionid::text,classid,objid,objsubid ;

输出结果格式如下：

附录二：查看阻塞会话，并生成kill sql

• SELECT pg_cancel_backend(pid); – session还在，事物回退;
• SELECT pg_terminate_backend(pid); --session消失，事物回退

with recursive 
tmp_lock as (
	select distinct
		--w.mode w_mode,w.page w_page,
		--w.tuple w_tuple,w.xact_start w_xact_start,w.query_start w_query_start,
		--now()-w.query_start w_locktime,w.query w_query
		w.pid as id,--w_pid,
		r.pid as parentid--r_pid,
		--r.locktype,r.mode r_mode,r.usename r_user,r.datname r_db,
		--r.relation::regclass,
		--r.page r_page,r.tuple r_tuple,r.xact_start r_xact_start,
		--r.query_start r_query_start,
		--now()-r.query_start r_locktime,r.query r_query,
	from (
		select a.mode,a.locktype,a.database,
		a.relation,a.page,a.tuple,a.classid,
		a.objid,a.objsubid,a.pid,a.virtualtransaction,a.virtualxid,
		a.transactionid,
		b.query as query,
		b.xact_start,b.query_start,b.usename,b.datname
		from pg_locks a,
		pg_stat_activity b
		where a.pid=b.pid
		and not a.granted
	) w,
	(
		select a.mode,a.locktype,a.database,
		a.relation,a.page,a.tuple,a.classid,
		a.objid,a.objsubid,a.pid,a.virtualtransaction,a.virtualxid,
		a.transactionid,
		b.query as query,
		b.xact_start,b.query_start,b.usename,b.datname
		from pg_locks a,
		pg_stat_activity b -- select pg_typeof(pid) from pg_stat_activity
		where a.pid=b.pid
		and a.granted
	) r
	where 1=1
		and r.locktype is not distinct from w.locktype
		and r.database is not distinct from w.database
		and r.relation is not distinct from w.relation
		and r.page is not distinct from w.page
		and r.tuple is not distinct from w.tuple
		and r.classid is not distinct from w.classid
		and r.objid is not distinct from w.objid
		and r.objsubid is not distinct from w.objsubid
		and r.transactionid is not distinct from w.transactionid
		and r.pid <> w.pid
	),
tmp0 as (
	select *
	from tmp_lock tl
	union all
	select t1.parentid,0::int4
	from tmp_lock t1
	where 1=1
	and t1.parentid not in (select id from tmp_lock)
	),
tmp3 (pathid,depth,id,parentid) as (
	SELECT array[id]::text[] as pathid,1 as depth,id,parentid
	FROM tmp0
	where 1=1 and parentid=0
	union
	SELECT t0.pathid||array[t1.id]::text[] as pathid,t0.depth+1 as depth,t1.id,t1.parentid
	FROM tmp0 t1, tmp3 t0
	where 1=1 and t1.parentid=t0.id
)
select distinct
	'/'||array_to_string(a0.pathid,'/') as pathid,
	a0.depth,
	a0.id,a0.parentid,lpad(a0.id::text, 2*a0.depth-1+length(a0.id::text),' ') as tree_id,
	--'select pg_cancel_backend('||a0.id|| ');' as cancel_pid,
	--'select pg_terminate_backend('||a0.id|| ');' as term_pid,
	case when a0.depth =1 then 'select pg_terminate_backend('|| a0.id || ');' else null end  as term_pid,
	case when a0.depth =1 then 'select cancel_backend('|| a0.id || ');' else null end  as cancel_pid
	,a2.datname,a2.usename,a2.application_name,a2.client_addr,a2.wait_event_type,a2.wait_event,a2.state
	--,a2.backend_start,a2.xact_start,a2.query_start
from tmp3 a0
left outer join (select distinct '/'||id||'/' as prefix_id,id
	from tmp0
	where 1=1 ) a1
on position( a1.prefix_id in '/'||array_to_string(a0.pathid,'/')||'/' ) >0
left outer join pg_stat_activity a2 -- select * from pg_stat_activity
on a0.id = a2.pid
order by '/'||array_to_string(a0.pathid,'/'),a0.depth;

输出结果格式如下：

附录三：查询当前执行时间超过60s的sql

select
	pg_stat_activity.datname,
	pg_stat_activity.pid,
	pg_stat_activity.query,
	pg_stat_activity.client_addr,
	clock_timestamp() - pg_stat_activity.query_start
from
	pg_stat_activity pg_stat_activity
where
	(pg_stat_activity.state = any (array['active'::text,
	'idle in transaction'::text]))
	and (clock_timestamp() - pg_stat_activity.query_start) > '00:00:60'::interval
order by
	(clock_timestamp() - pg_stat_activity.query_start) desc;

输出结果格式如下：

附录四：查询所有已经获取锁的SQL

SELECT pid, state, usename, query, query_start 
from pg_stat_activity 
where pid in (
  select pid from pg_locks l  join pg_class t on l.relation = t.oid 
  and t.relkind = 'r' 
);

输出结果格式如下：

整理完毕，完结撒花~ 🌻

参考地址：

1.PostgreSQL锁浅析，https://www.modb.pro/db/480864

2.Postgresql几个和执行语句相关的timeout参数，https://blog.csdn.net/dazuiba008/article/details/106766191

3.postgresql锁表查询与处理，https://blog.csdn.net/jiahao1186/article/details/122255173

4.MySQL与PostgreSQL的 SELECT FOR UPDATE，https://blog.csdn.net/zljjava/article/details/51087568文章来源地址https://www.toymoban.com/news/detail-498286.html

到了这里，关于PostgreSQL（八）锁的相关操作的文章就介绍完了。如果您还想了解更多内容，请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章，希望大家以后多多支持TOY模板网！