震惊！这种问题也问得出来？“能不能并发更新同一条记录”这不是常识吗?
P
digoal约 7 分钟
digoal约 7 分钟文中参考文档在github需点击阅读原文打开, 同时推荐2个学习环境:
1、懒人Docker镜像, 已打包200+插件:《最好的PostgreSQL学习镜像》
天呐, “能不能并发更新同一条记录”这个问题还需要回答吗? 这不应该是常识吗?
昨天一位兄弟问我, 不同的会话可以同时更新同一条记录的不同字段吗?
我的第一反应和你一样: 老兄, 这个问题还需要回答吗? 这不是常识吗? 数据库最小颗粒的锁是行锁, 同一行的行级别排他锁在一个时刻只能被一个会话持有, 其他会话想持有是需要等待的.
我们可以简单进行验证, 就使用我的宇宙第一学习镜像吧.
1、建表, 写入一条测试记录
create table t (id int primary key, info text, ts timestamp);
insert into t values (1,'test',now());
2、启动会话1, 开启事务并更新id=1的记录的info字段.
postgres=# select pg_backend_pid();
pg_backend_pid
----------------
90
(1 row)
postgres=# begin;
BEGIN
postgres=*# update t set info='abc' where id=1;
UPDATE 1
postgres=*#
3、启动会话2, 开启事务并更新id=1的记录的ts字段.
postgres=# select pg_backend_pid();
pg_backend_pid
----------------
98
(1 row)
postgres=# begin;
BEGIN
postgres=*# update t set ts=now() where id=1;
4、显然会话2被堵塞了, 对于这种锁等待的实时排查, 可以参考我这篇文档.
《PostgreSQL 锁等待排查实践 - 珍藏级 - process xxx1 acquired RowExclusiveLock on relation xxx2 of database xxx3 after xxx4 ms at xxx》
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 ;
等待链条被清晰的表达了:
-[ RECORD 1 ]-+------------------------------------------------------------------------------------------------------------------------------------------------------
locktype | transactionid
datname | postgres
relation |
page |
tuple |
virtualxid |
transactionid | 735
classid |
objid |
objsubid |
lock_conflict | Pid: 90 +
| Lock_Granted: true , Mode: ExclusiveLock , FastPath: false , VirtualTransaction: 3/40 , Session_State: idle in transaction +
| Username: postgres , Database: postgres , Client_Addr: NULL , Client_Port: -1 , Application_Name: psql +
| Xact_Start: 2024-04-16 02:51:46.42059+00 , Query_Start: 2024-04-16 02:51:56.778718+00 , Xact_Elapse: 00:01:19.967108 , Query_Elapse: 00:01:09.60898 +
| SQL (Current SQL in Transaction): +
| update t set info='abc' where id=1; +
| -------- +
| Pid: 98 +
| Lock_Granted: false , Mode: ShareLock , FastPath: false , VirtualTransaction: 4/3 , Session_State: active +
| Username: postgres , Database: postgres , Client_Addr: NULL , Client_Port: -1 , Application_Name: psql +
| Xact_Start: 2024-04-16 02:51:59.859851+00 , Query_Start: 2024-04-16 02:52:09.847601+00 , Xact_Elapse: 00:01:06.527847 , Query_Elapse: 00:00:56.540097+
| SQL (Current SQL in Transaction): +
| update t set ts=now() where id=1;
如果你设置了log_lock_waits = on 和 deadlock_timeout = 1s 还会看到类似如下日志:
2024-04-16 06:41:44.670 UTC,"postgres","postgres",98,"[local]",661de78a.62,2,"UPDATE waiting",2024-04-16 02:50:50 UTC,4/4,737,LOG,00000,"process 98 still waiting for ShareLock on transaction 735 after 1001.169 ms","Process holding the lock: 90. Wait queue: 98.",,,,"while updating tuple (0,1) in relation ""t""","update t set ts=now() where id=1;",,,"psql","client backend",,0
文章到这里就结束了吗? NO, 作为一名PD, 我更想说的是:
创新就是被“喜闻乐见”禁锢的.
哲学就是对“常识、不证自明的公理”的不断追问.
如果要解决本文提到的这个问题, 我们反思一下有哪几种方法?
1 业务可以拆表(把需要经常并行更新的字段, 拆到多个表, 使用PK关联起来).
2 业务把对同一行的更新合并到一个会话/事务里面进行更新.
3 业务把对同一行不同字段的更新分配到同一个会话的不同或相同事务中执行.
4 产品改进: 为什么我们就不能把锁粒度再往下一层呢?
锁粒度为什么只能最小化到行级别呢? 为什么不能字段级别呢? 甚至JSON、array、tsvector这种多值类型 为什么锁粒度不能到元素级别呢?
多版本控制为什么只能行级别呢? 为什么不能字段级别呢? 甚至JSON、array、tsvector这种多值类型 为什么多版本不能到元素级别呢?
欢迎关注我的github (https://github.com/digoal/blog) , 学习数据库不迷路.
近期正在写公开课材料, 未来将通过视频号推出, 欢迎关注视频号:
文章中的参考文档请点击阅读原文获得.