Postgresql几个和执行语句相关的timeout参数-摩杜云开发者社区

在执行相关SQL的时候，相关的几个timeout说明一下，以免在生产环境中误用

statement_timeout  #语句执行时间超过这个设置时间，终止执行SQL,0为禁用
idle_in_transaction_session_timeout  #一个空闲的事物中，空闲时间超过这个值，将视为超时，0为禁用
lock_timeout #获取一个表，索引，行上的锁超过这个时间，直接报错，不等待，0为禁用
deadlock_timeout #死锁时间超过这个值将直接报错，不会等待，默认设置1s

statement_timeout设置实例：

statement_timeout设置为1，参数单位是ms
postgres=# show statement_timeout;
 statement_timeout 
-------------------
 1ms
postgres=# \d tb1
ERROR:  canceling statement due to statement timeout
查看日志
tail -f postgresql-2020-06-15_165900.csv
2020-06-15 17:00:47.054 CST,"postgres","postgres",15267,"[local]",5ee73856.3ba3,22,"SELECT",2020-06-15 16:59:02 CST,3/36,0,ERROR,57014,"canceling statement due to statement timeout",,,,,,"SELECT ...

可见设置为1ms,表结构的查询都无法执行，statement_timeout参数个人不建议设置，当然可以根据需求自行设定

idle_in_transaction_session_timeout设置实例

同样设置设置为1ms,开启一个事物，在事物中查看表tb1
postgres=# show idle_in_transaction_session_timeout;
 idle_in_transaction_session_timeout 
-------------------------------------
 1ms
(1 row)
postgres=# begin;
BEGIN
postgres=*# select * from tb1;
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.
查看日志已经报以下错误：
2020-06-15 17:12:49.880 CST,"postgres","postgres",15601,"[local]",5ee73b73.3cf1,2,"idle in transaction",2020-06-15 17:12:19 CST,3/18,0,FATAL,25P03,"terminating connection due to idle-in-transaction timeout",,,,,,,,,"psql","client backend"

lock_timeout设置实例

设置lock_timeout为1ms，建表，然后在不同事物更新同一行
session A:
postgres=# create table tb1 (a int,b name);
CREATE TABLE
postgres=# insert into tb1 values (1,'hank'),(2,'dazuiba');
INSERT 0 2
postgres=# show lock_timeout;
 lock_timeout 
--------------
 1ms
(1 row)
postgres=# begin;
BEGIN
postgres=*# update tb1 set b='dazuiba' where a=1;
UPDATE 1

session B:
postgres=# update tb1 set b='dazuiba' where a=1;
ERROR:  canceling statement due to lock timeout
CONTEXT:  while updating tuple (0,13) in relation "tb1"
查看日志报错内容如下
2020-06-15 17:22:43.212 CST,"postgres","postgres",15995,"[local]",5ee73dc3.3e7b,1,"UPDATE",2020-06-15 17:22:11 CST,4/2,500,ERROR,55P03,"canceling statement due to lock timeout",,,,,"while updating tuple (0,13) in relation ""tb1""","update tb1 set b='dazuiba' where a=1;",,,"psql","client backend"

deadlock_timeout实例

设置deadlock_timeout为1s,死锁检测需要开启两个session，接下来看以下步骤：
postgres=# show deadlock_timeout;
 deadlock_timeout 
------------------
 1s
(1 row)
session A:
postgres=# begin;
BEGIN
postgres=*# update tb1 set b='dazuiba' where a=1;   #执行第一个UPDATE
UPDATE 1
postgres=*# update tb1 set b='hank' where a=2;     #执行第三个UPDATE，在第四个UPDATE之前这里是等待状态，第四个UPDATE报死锁错误后，这里UPDATE成功
UPDATE 1


session B:
postgres=# begin;
BEGIN
postgres=*# update tb1 set b='dazuiba' where a=2;    #执行第二个UPDATE
UPDATE 1
postgres=*# update tb1 set b='hank' where a=1;       #执行第四个UPDATE造成死锁报错
ERROR:  deadlock detected
DETAIL:  Process 16212 waits for ShareLock on transaction 501; blocked by process 16211.
Process 16211 waits for ShareLock on transaction 502; blocked by process 16212.
HINT:  See server log for query details.
CONTEXT:  while updating tuple (0,13) in relation "tb1"

日志报错如下：
2020-06-15 17:34:10.485 CST,"postgres","postgres",16212,"[local]",5ee7404f.3f54,1,"UPDATE",2020-06-15 17:33:03 CST,4/2,502,ERROR,40P01,"deadlock detected","Process 16212 waits for ShareLock on transaction 501; blocked by process 16211.
Process 16211 waits for ShareLock on transaction 502; blocked by process 16212.
Process 16212: update tb1 set b='hank' where a=1;
Process 16211: update tb1 set b='hank' where a=2;","See server log for query details.",,,"while updating tuple (0,13) in relation ""tb1""","update tb1 set b='hank' where a=1;",,,"psql","client backend"

以上就是四个参数的实例说明解释，稍微复杂的就是deadlock_timeout实例，如果明白死锁的概念，这里也很好理解。