Mogdb锁等待超时参数实践测试

原作者：陈坤

客户的业务代码中有使用到select for update/nowait/wait n 等参数，为明确mogdb中 lockwait_timeout,update_lockwait_timeout 对 select for update/nowait/wait n 语句的影响，对参数设置不同值的行为进行了测试。

提前声明：测试版本为Mogdb 5.0.1，已将测试问题反馈给研发部门，不排除后续版本中修复某些问题，相同语句行为发生改变的情况。参考本文档时，可根据文档的测试方案对当前使用的版本在进行一次测试。

参数说明：

lockwait_timeout
控制单个锁的最长等待时间。当申请的锁等待时间超过设定值时，系统会报错。

update_lockwait_timeout
允许并发更新参数开启情况下，该参数控制并发更新同一行时单个锁的最长等待时间。当申请的锁等待时间超过设定值时，系统会报错。

修改和查询参数：

gs_guc check -I all -N all -c “lockwait_timeout”
gs_guc check -I all -N all -c “update_lockwait_timeout”

gs_guc reload -I all -N all -c “lockwait_timeout=0”
gs_guc reload -I all -N all -c “update_lockwait_timeout=0”

准备测试环境

创建一个oracle兼容数据库

create user ora sysadmin identified by ‘Enmo#2023’;
create database oradb owner=ora DBCOMPATIBILITY ‘A’;

gsql -r -Uora -WEnmo#2023 oradb

CREATE TABLE staff(
ID INT PRIMARY KEY,
NAME CHAR(50) UNIQUE,
AGE INT NOT NULL,
ADDRESS TEXT,
SALARY DECIMAL(5,2),
CONSTRAINT staff2_key2 check(AGE > 0 and name is not null)
);

insert into staff VALUES
(1, ‘susi’, 26, ‘Hongkong’, 200),
(2, ‘bob’, ‘30’, ‘shanghai’, 600),
(3, ‘jim’, 23, ‘beijing’, 100),
(4, ‘he’, 36, ‘Hongkong’, 500),
(5, ‘yun’, 16, ‘shanghai’, 400);

场景1:lockwait_timeout=0,update_lockwait_timeout=0

gs_guc reload -I all -N all -c “lockwait_timeout=0”
gs_guc reload -I all -N all -c “update_lockwait_timeout=0”

session1:
begin;
select * from staff where id=5 for update;

session2:

update staff SET ADDRESS = ‘Hongkong’ WHERE id = 5;
一直阻塞，无法执行

nowait 不等待，马上报错
oradb=> select * from staff where id=5 for update nowait;
ERROR: could not obtain lock on row in relation “staff”

wait n
select * from staff where id=5 for update wait 3;
一直阻塞，无法执行
不符合预期

session1:
commit;

场景2:lockwait_timeout=0,update_lockwait_timeout=3000

gs_guc reload -I all -N all -c “update_lockwait_timeout=3000”
gs_guc reload -I all -N all -c “lockwait_timeout=0”

gsql -r -Uora -WEnmo#2023 oradb
show update_lockwait_timeout;

session1:
begin;
select * from staff where id=5 for update;

session2:
select * from staff where id=5 for update;
update staff SET ADDRESS = ‘Hongkong’ WHERE id = 5;
语句一直等待，并不受 update_lockwait_timeout 控制

nowait语句正常报错
oradb=> select * from staff where id=5 for update nowait;
ERROR: could not obtain lock on row in relation “staff”

wait n 语句一直等待
select * from staff where id=5 for update wait 2;

session1:
commit;

场景3:lockwait_timeout=3000,update_lockwait_timeout=0

gs_guc reload -I all -N all -c “lockwait_timeout=3000”
gs_guc reload -I all -N all -c “update_lockwait_timeout=0”

oradb=> show lockwait_timeout;
lockwait_timeout
------------------
3s
(1 row)

oradb=> show update_lockwait_timeout;
update_lockwait_timeout
-------------------------
0
(1 row)

session1:
begin;
select * from staff where id=5 for update;

session2:
反复修改lockwait_timeout时间，发现无论改成多少，语句
delete from staff where id=5;
select * from staff where id=5 for update;
update staff SET ADDRESS = ‘Hongkong’ WHERE id = 5;
，都是2秒后超时。

oradb=> update staff SET ADDRESS = ‘Hongkong’ WHERE id = 5;
ERROR: Lock wait timeout: thread 47058121590528 on node dn_6001 waiting for ShareLock on transaction 23285 after 2001.419 ms
DETAIL: blocked by hold lock thread 47058085873408, statement <select * from staff where id=5 for update;>, hold lockmode ExclusiveLock.

nowait 语句正常报错
oradb=> select * from staff where id=5 for update nowait;
ERROR: could not obtain lock on row in relation “staff”

wait n 语句正常
oradb=> select * from staff where id=5 for update wait 1;
ERROR: could not obtain lock on row in relation,waitSec = 1

session1:
commit;

场景4:lockwait_timeout=10000,update_lockwait_timeout=5000

lockwait_timeout=5;
修改update_lockwait_timeout=10

gs_guc reload -I all -N all -c “lockwait_timeout=10000”
gs_guc reload -I all -N all -c “update_lockwait_timeout=5000”

将两者时间交换，lockwait_timeout < update_lockwait_timeout 不影响测试结果。
gs_guc reload -I all -N all -c “lockwait_timeout=5000”
gs_guc reload -I all -N all -c “update_lockwait_timeout=10000”

oradb=> show lockwait_timeout;
lockwait_timeout
------------------
5s
(1 row)

oradb=> show update_lockwait_timeout;
update_lockwait_timeout
-------------------------
10s
(1 row)

session1:
begin;
select * from staff where id=5 for update;

session2:
select * from staff where id=5 for update;
update staff SET ADDRESS = ‘Hongkong’ WHERE id = 5;
delete from staff where id=5;
语句5秒后超时报错
oradb=> update staff SET ADDRESS = ‘Hongkong’ WHERE id = 5;
ERROR: Lock wait timeout: thread 47036404860672 on node dn_6001 waiting for ShareLock on transaction 24673 after 5001.549 ms
DETAIL: blocked by hold lock thread 47036127717120, statement <select * from staff where id=5 for update;>, hold lockmode ExclusiveLock.

nowait 语句正常报错
oradb=> select * from staff where id=5 for update nowait;
ERROR: could not obtain lock on row in relation “staff”

wait n 语句正常
oradb=> select * from staff where id=5 for update wait 3;
ERROR: could not obtain lock on row in relation,waitSec = 3

当n > update_lockwait_timeout

select * from staff where id=5 for update wait 6;

当 n > lockwait_timeout

select * from staff where id=5 for update wait 12;

均以wait n设置的时间到期为准超时

session1:
commit;

无论lockwait_timeout 大于还是小于 update_lockwait_timeout，除nowait,wait n 外，都以update_lockwait_timeout的时间作为超时时间。

总结：

测试结果见以下表格：

所以如果想要让所有行为符合预期，目前可以按照参数默认值，将lockwait_timeout和update_lockwait_timeout分别设置为20分钟和2分钟。

在客户当前业务场景中，有希望select for update 长时间等待的需求，也有要保证wait n 准确按照语句设置进行等待。所以目前的解决方案是，将lockwait_timeout和update_lockwait_timeout设置为靠近上限大小的数值1728000000毫秒，等于20天。