当前位置: 首页>数据库>正文

lightdb数据库超时相关控制参数

  在业务开发中,通常因为代码不规范、中间件缺陷、DBA误提交批量SQL等原因,会导致服务端连接一直存在、但是实际上并未在执行的情况,从而导致数据库连接泄露。为了防止这种异常情况积压,lightdb中包含了多个参数用于控制超时相关的行为:

  • lock_timeout:控制多久拿不到锁就自动超时并回滚事务
  • idle_in_transaction_session_timeout:设置会话在进入事务块(显示BEGIN)后,一直未退出、也未在执行SQL后多久自动结束会话
  • statement_timeout:设置语句执行最长多久,超过之后就自动超时
  • idle_session_timeout:设置会话在没有进入事务块(显示BEGIN),也没有执行SQL后多久没活动自动超时
lightdb@oradb=# set idle_session_timeout to 3;
SET
lightdb@oradb=# 
lightdb@oradb=# 
lightdb@oradb=# 
lightdb@oradb=# commit;
FATAL:  terminating connection due to idle-session timeout
server closed the connection unexpectedly
    This probably means the server terminated abnormally
    before or while processing the request.
The connection to the server was lost. Attempting reset: Succeeded.
lightdb@oradb=# show %idle%;
                name                 | setting |                                  description                                   
-------------------------------------+---------+--------------------------------------------------------------------------------
 idle_in_transaction_session_timeout | 0       | Sets the maximum allowed idle time between queries, when in a transaction.
 idle_session_timeout                | 0       | Sets the maximum allowed idle time between queries, when not in a transaction.
 tcp_keepalives_idle                 | 300     | Time between issuing TCP keepalives.
(3 rows)

lightdb@oradb=# set idle_in_transaction_session_timeout to 3;
SET
lightdb@oradb=# begin;
BEGIN
lightdb@oradb=*# select 1;
FATAL:  terminating connection due to idle-in-transaction timeout
server closed the connection unexpectedly
    This probably means the server terminated abnormally
    before or while processing the request.
The connection to the server was lost. Attempting reset: Succeeded.
lightdb@oradb=# set statement_timeout = 10000;
SET
lightdb@oradb=# select pg_sleep(10) from pg_class ;
ERROR:  canceling statement due to statement timeout

详细可见https://www.light-pg.com/docs/lightdb/current/runtime-config-client.html。


https://www.xamrdz.com/database/6h71961165.html

相关文章: