循序渐进丨MogDB 中控制锁超时的参数lockwait_timeout与update_lockwait_timeout验证

MogDB 2024-11-13

107

概念描述

MogDB 使用以下两个参数控制着锁超时：lockwait_timeout和update_lockwait_timeout。

测试验证

1.查看MogDB版本

MogDB=# select version();
version                                                                      
--------------------------------------------------------------------------------------------------------------------------------------------------
(MogDB 3.0.4 build cc068866) compiled at 2023-03-03 17:47:05 commit 0 last mr   on x86_64-unknown-linux-gnu, compiled by g++ (GCC) 7.3.0, 64-bit
(1 row)
MogDB=# show server_version;
server_version
----------------
9.2.4
(1 row)
复制

2.查看参数默认值

MogDB=# show lockwait_timeout;
lockwait_timeout
------------------
20min
(1 row)
MogDB=# show update_lockwait_timeout;
update_lockwait_timeout
-------------------------
2min
(1 row)
MogDB=#
复制

3.修改参数默认值

为缩短验证试验的等待时间，调整lockwait_timeout为3分钟，update_lockwait_timeout为1分钟，并执行gs_ctl reload生效。

[omm@MogDB1 data]$ diff postgresql.conf postgresql.conf.bak
659,660c659
< lockwait_timeout = 180s # Max of lockwait_timeout and deadlock_timeout + 1s
< update_lockwait_timeout = 60s
---
> lockwait_timeout = 1200s # Max of lockwait_timeout and deadlock_timeout + 1s
[omm@MogDB1 data]$ pwd
/u01/mogdb/data
[omm@MogDB1 data]$ gs_ctl reload
[2023-08-15 22:04:01.060][71549][][gs_ctl]: gs_ctl reload ,datadir is u01/mogdb/data
server signaled
[omm@MogDB1 data]$ gsql -r
gsql ((MogDB 3.0.4 build cc068866) compiled at 2023-03-03 17:47:05 commit 0 last mr )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.
MogDB=# show lockwait_timeout
MogDB-# ;
lockwait_timeout
------------------
3min
(1 row)
MogDB=# show update_lockwait_timeout;
update_lockwait_timeout
-------------------------
1min
(1 row)
MogDB=#
复制

4.关闭gsql自动提交

为验证事务锁等待，将gsql工具的自动提交功能关闭：

MogDB=# \set AUTOCOMMIT off
MogDB=# \echo :AUTOCOMMIT
off
MogDB=#
复制

5.验证锁等待超时（lockwait_timeout）

在第一个窗口执行以下语句，对表test加锁：

MogDB=# \set AUTOCOMIT off
MogDB=# \echo :AUTOCOMMIT
off
MogDB=# select * from test;
id | value1 | value2
----+--------+--------
1 |     12 |     20
2 |    100 |    200
3 |   1000 |   2000
(3 rows)
MogDB=# select * from test for update;
id | value1 | value2
----+--------+--------
1 |     12 |     20
2 |    100 |    200
3 |   1000 |   2000
(3 rows)
MogDB=#
复制

在第二个窗口执行以下语句，申请对表test加锁，加锁申请处于等待中：

MogDB=# \set AUTOCOMMIT off
MogDB=# \echo :AUTOCOMMIT
off
MogDB=# select * from test;
id | value1 | value2
----+--------+--------
1 |     12 |     20
2 |    100 |    200
3 |   1000 |   2000
(3 rows)
MogDB=# drop table test;
复制

在等待3分钟后，MogDB 会中止掉锁等待事务，并报Lock wait timeout：

MogDB=# drop table test;
ERROR: Lock wait timeout: thread 47785292334848 on node dn_6001 waiting for AccessExclusiveLock on relation 89558 of database 15940 after 180000.900 ms
DETAIL: blocked by hold lock thread 47785166448384, statement <select * from test for update;>, hold lockmode AccessShareLock.
MogDB=#
复制

6.验证并发更新锁等待超时（update_lockwait_timeout）

在第一个窗口执行以下更新语句，对表test行加锁：

MogDB=# \set AUTOCOMMIT off
MogDB=# select * from test;
id | value1 | value2
----+--------+--------
1 |     12 |     20
2 |    100 |    200
3 |   1000 |   2000
(3 rows)
MogDB=# update test set value1=value1+1 where id=1;
UPDATE 1
MogDB=# select * from test;
id | value1 | value2
----+--------+--------
1 |     13 |     20
2 |    100 |    200
3 |   1000 |   2000
(3 rows)
MogDB=#
复制

在第二个窗口执行以下语句，对表test的同一行进行更新，更新操作处于等待中：

MogDB=# select * from test;
id | value1 | value2
----+--------+--------
1 |     12 |     20
2 |    100 |    200
3 |   1000 |   2000
(3 rows)
MogDB=# update test set value1=value1+1 where id=1;
复制

在等待1分钟后，MogDB 会中止掉锁update等待事务，并报Lock wait timeout：

MogDB=# select * from test;
id | value1 | value2
----+--------+--------
1 |     12 |     20
2 |    100 |    200
3 |   1000 |   2000
(3 rows)
MogDB=# select * from test;
id | value1 | value2
----+--------+--------
1 |     12 |     20
2 |    100 |    200
3 |   1000 |   2000
(3 rows)
MogDB=# update test set value1=value1+1 where id=1;
ERROR: Lock wait timeout: thread 47785292334848 on node dn_6001 waiting for ShareLock on transaction 1132652 after 60000.959 ms
DETAIL: blocked by hold lock thread 47785166448384, statement <select * from test;>, hold lockmode ExclusiveLock.
MogDB=#
复制

知识总结

lockwait_timeout：控制单个锁的最长等待时间。当申请的锁等待时间超过设定值时，MogDB 会报Lock wait timeout。
update_lockwait_timeout：允许并发更新参数开启情况下，该参数控制并发更新同一行时单个锁的最长等待时间。当申请的锁等待时间超过设定值时，MogDB 会报Lock wait timeout。
在 Oracle 中，对锁等待并没有超时自动中止等待事务的参数控制，对从 Oracle 迁移至 MogDB 的系统，建议参考应用系统中事务处理时长，设置合适的锁等待超时时间。

如果在一个事务中，包括的SQL语句需要等待其他事务完成的时间更长，则可以增加lockwait_timeout和update_lockwait_timeout配置值；如果太多长时间运行的事务导致锁定问题并降低繁忙系统上的并发性，则可以降低该项的值。

如下是某客户MES系统的锁等待时间设定，其中update_lockwait_timeout <= lockwait_timeout

[omm@MogDB1 data]$ diff postgresql.conf postgresql.conf.bak
659,660c659
< lockwait_timeout = 1800s              # Max of lockwait_timeout and deadlock_timeout + 1s
< update_lockwait_timeout = 1800s
---
> lockwait_timeout = 1200s              # Max of lockwait_timeout and deadlock_timeout + 1s
[omm@MogDB1 data]$ gsql -r
gsql ((MogDB 3.0.4 build cc068866) compiled at 2023-03-03 17:47:05 commit 0 last mr  )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.


MogDB=# show lockwait_timeout;
 lockwait_timeout 
------------------
 30min
(1 row)


MogDB=# show update_lockwait_timeout;
 update_lockwait_timeout 
-------------------------
 30min
(1 row)


MogDB=#
复制