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会abrot掉锁等待事务，并报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会abrot掉锁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=#

知识总结

1.lockwait_timeout：控制单个锁的最长等待时间。当申请的锁等待时间超过设定值时，MogDB会报Lock wait timeout。

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

3.在oracle中，对锁等待,并没有超时自动abort等待事务的参数控制，对从oracle迁移至MogDB的系统,建议参考应用系统中事务处理时长，设置合适的锁等待时间超时间.

如果在一个事务中，包括的SQL语句需要等待其他事务完成的时间更长，则可以增加 lock_wait_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=#