PostgreSQL 9.3 新增服务端配置参数 lock_timeout，此参数用来 cancel 超过指定时间的等待 SQL，由于这个参数会主动探测长时间处于等待状态(通常因为申请不到相关锁)的SQL，并终止超过指定时间的 SQL，故不推荐使用，这里仅简单说明这个参数，在某些场合确实需要时可以考虑使用。

手册上的解释

lock_timeout (integer)
Abort any statement that waits longer than the specified number of milliseconds while attempting to acquire a lock on a table, index, row, or other database object. The time limit applies separately to each lock acquisition attempt. The limit applies both to explicit locking requests (such as LOCK TABLE, or SELECT FOR UPDATE without NOWAIT) and to implicitly-acquired locks. If log_min_error_statement is set to ERROR or lower, the statement that timed out will be logged. A value of zero (the default) turns this off.
备注：手册上解释的比较清楚了，下面通过简单的例子演示下。

测试SQL超时场景

2.1 修改参数
设置 postgresql.conf 中的以下参数，并 reload。

1

lock_timeout = 10000

设置好后，到数据库里查下：

1
2
3
4
5

francs=> show lock_timeout;
 lock_timeout 
--------------
 10s
(1 row)

备注：这里设置了 10 秒，便于测试。

2.2 创建测试表

1
2
3
4
5
6

[pg93@redhatB ~]$ psql francs francs
psql (9.3beta1)
Type "help" for help.
  
francs=> create table test_lock (id int4 primary key ,name character varying (32),create_time timestamp without time zone );
CREATE TABLE

2.3 开启会话 1

1
2
3
4
5
6

francs=> begin;
BEGIN
francs=> insert into test_lock values (1,'a',clock_timestamp());
INSERT 0 1
francs=> 
.....事务未提交

备注：此时会话 1 仍然还在事务中，事务未结束。

2.4 开启会话 2

1
2
3
4
5
6
7
8
9
10
11
12
13

[pg93@redhatB tf]$ psql francs francs
psql (9.3beta1)
Type "help" for help.
  
francs=> select pg_backend_pid();
 pg_backend_pid 
----------------
          22962
(1 row
  
francs=> alter table test_lock alter column name type character varying(64);
ERROR:  canceling statement due to lock timeout
Time: 10017.376 ms

备注：会话 2 尝试给表 test_lock 的 name 字段扩长，这时处于等待状态，并且在等待超过 10 秒时 PostgreSQL 会 cancel 这个 SQL，关于 PostgreSQL 锁分, 什么情况下被锁，参考之前 BLOG: Postgresql 锁浅析

2.5 查看进程

1
2
3
4

[pg93@redhatB ~]$ ps -ef | grep 22962
pg93     22962  2758  0 13:46 ?        00:00:00 postgres: francs francs [local] idle                     
pg93     23202 23088  0 13:51 pts/4    00:00:00 grep 22962
[pg93@redhatB ~]$

备注：OS 系统上进程 22962 还存在，说明之前 PostgreSQL 仅 cancel SQL 语句，而没有杀掉会话。

2.6 查看PostgreSQL 日志

1

 2013-05-20 13:48:52.626 CST,"francs","francs",22962,"[local]",5199b8b7.59b2,2,"ALTER TABLE",2013-05-20 13:46:31 CST,3/4214,1891,ERROR,57014,"canceling statement due to lock timeout",,,,,,"alter table test_lock alter column name type character varying(64);",,,"psql"

备注：如果 log_min_error_statement 参数设置成 ERROR 或更低级别，则这些信息会记录到日志中。以上 lock_timeout 参数实验完成了，此时你可能还会想到另一个类似参数，该参数为 statement_timeout ，不要混淆这两参数，因为 statement_timeout 参数用来控制已运行 SQL ，可以杀掉运行时间超长的 SQL ，关于该参数参考BLOG： PostgreSQL: Autoabort user’s statement that takes over the specified time

参考

• PostgreSQL 锁浅析
• PostgreSQL: Autoabort user’s statement that takes over the specified time
• Lock_timeout (integer)
• http://www.postgresql.org/docs/9.3/static/runtime-config-logging.html