我正在参加「掘金·启航计划」

连接列表管理

🏭我们呢可能时不时地会遇到一些想要终止数据库连接的情况，比如执行了写得很糟糕的SQL语句，把系统资源耗光。发生这个呢，我们当然是希望能结束这些操作或者彻底干脆终止这个连接。

想要终止正在执行的语句并杀掉连接，就要使用以下步骤：

1. 首先呢就是要查出活动连接列表及其进程ID，使用下面的语句

SELECT * FROM pg_stat_activity;

pg_stat_activity这个视图包含每个连接上在最近一次执行的语句，使用的用户名（usrname字段），所在的databases名（datname字段）以及语句开始执行的时间。通过查询该视图可以找到需要终止的会话所对应的进程ID。

1. 停止连接（假设对应的进程号为1234）上的活动查询

   SELECT pg_cancel_backend(1234);
   

   该操作不会终止连接本身

2. 终止该连接

   SELECT pg_terminate_backend(1234);
   

   有时候会需要终止这个连接，特别是执行数据库恢复之前。如果仅仅是停止了正在执行的语句而没有彻底杀掉连接，客户端是可以立即重新执行刚刚被终止掉的语句的，这又会导致系统陷入之前的状态。为了避免此种情况的发生，可以采用直接终止连接的方式。如果未停止某个连接上正在执行的语句就直接去终止该连接，那么这些语句也会被停止掉。

因为Postgresql支持在select查询语句中调用函数。因此，尽管pg_terminate_backend和SELECT pg_cancel_backend一次仅能处理一个连接，但你可以通过select语句中调用函数的方式实现一次处理多个连接。

📦比如说希望一次性终止某个用户的所有连接，那么可以执行以下语句。

SELECT pg_terminate_backend(pid) FROM pg_stat_activity
where  usename  = 'some_role';

Postgresql有一些语句参数可以用来控制语句的运行状态，一旦语句运行期间的某些状态值超过了这些运行参数所限定的范围，该语句会被系统自动杀掉。这些参数可以在服务实例级，databases级，用户级，会话级和函数级设置。参数值设为0代表禁用。

• deadlock_timeout

  该参数表示阿紫进行死锁检测之前需要等待多久。默认是1000毫秒。如果你的业务系统中有大量更新操作，那么建议增大该值以减少死锁检测的次数。

  当然，与其依赖这个参数就解决死锁，其实是更建议在UPDATE语句中加NOWAIT子句来避免死锁。

  例如：

  SELECT FOR UPDATE NOWAIT 。。。
  

• statement_timeout

  该参数可以控制一个语句能够执行的最长时间，超出限定的时间后该语句会被自动终止。该参数默认值为0，即无限制。

  但是为了防止误杀，最好不要把这个参数设置为全局级别，仅在要控制的函数的定义中针对该函数自身设置一下既可。

• lock_timeout

  该参数控制锁等待的最长时间，超出限定时间后等待锁的语句就会被自动终止。对于执行数据更新的语句来说，该参数有较大的价值，因为每次更新数据之前都必须先获取待修改的记录上的排他锁，所以更新语句之间是最容易发生锁等待的。lock_timeout的值应该设得比statement_timeout小，否则就是总会语句先超时，这样lock_timeout就毫无意义了。

死锁检测是很昂贵的操作，因此系统不会每次发生锁等待都去做死锁检测

• idle_in_transaction_session_timeout

  该参数表示一个事务可以处于idle状态的最长时间，超过限定的时间后该事务会被自动回滚。该参数的默认值为0，表示事务可以永久处于idle状态。该参数是9.6版本引入的。可以起到两个作用：防止一个空闲事务占着记录锁一致不释放从而阻塞别的事务继续运行，还可以防止一个数据库连接被一个空闲事务永远占用。

查看被阻塞语句的情况：

在pg的9.6版本中，pg_stat_activity视图中的waiting字段变为了wait_event_type和wait_event的字段，其中记录了当前会话上的语句在等待什么资源。