PostgreSQL 9.3 add lock_timeout parameter

背景

Add configuration variable lock_timeout to limit lock wait duration

测试

1. 隐锁

SESSION A :

digoal=# begin;  
BEGIN  
digoal=# insert into test values (1, 'info');  
INSERT 0 1  

SESSION B :

digoal=# set client_min_messages='notice';  
SET  
digoal=# set lock_timeout='1s';  
SET  
digoal=# \set VERBOSITY verbose  
digoal=# truncate test;  
-- 1秒后  
ERROR:  57014: canceling statement due to lock timeout  
LOCATION:  ProcessInterrupts, postgres.c:2899  

2. 显锁

接上例,

SESSION B :

digoal=# lock table test in exclusive mode;  
-- 1秒后  
ERROR:  57014: canceling statement due to lock timeout  
LOCATION:  ProcessInterrupts, postgres.c:2899  

SESSION A :

digoal=# begin;  
BEGIN  
digoal=# update test set info='test' where id=1;  
UPDATE 1  

SESSION B :

digoal=# select * from test where id=1 for update;  
ERROR:  57014: canceling statement due to lock timeout  
LOCATION:  ProcessInterrupts, postgres.c:2899  
-- nowait不会等1秒.  
digoal=# select * from test where id=1 for update nowait;  
ERROR:  55P03: could not obtain lock on row in relation "test"  
LOCATION:  heap_lock_tuple, heapam.c:4269  

其他

1. statement_timeout会比lock_timeout先触发, 所以配置lock_timeout大于等于statement_timeout是没有意义的.

2. 不建议将lock_timeout配置在postgresql.conf中, 因为这样会对所有会话生效. 例如备份也很有可能失败.

3. lock_timeout对隐锁和显锁都会生效.

4. 行锁级别信息

src/include/access/heapam.h

/*  
 * Possible lock modes for a tuple.  
 */  
typedef enum LockTupleMode  
{  
        /* SELECT FOR KEY SHARE */  
        LockTupleKeyShare,  
        /* SELECT FOR SHARE */  
        LockTupleShare,  
        /* SELECT FOR NO KEY UPDATE, and UPDATEs that don't modify key columns */  
        LockTupleNoKeyExclusive,  
        /* SELECT FOR UPDATE, UPDATEs that modify key columns, and DELETE */  
        LockTupleExclusive  
} LockTupleMode;  

参考

1. http://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=d43837d03067487560af481474ae985df894f786

2. http://www.postgresql.org/docs/devel/static/runtime-config-client.html#GUC-LOCK-TIMEOUT

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.  
  
Unlike statement_timeout, this timeout can only occur while waiting for locks.   
  
Note that if statement_timeout is nonzero, it is rather pointless to set lock_timeout to the same or larger value,   
since the statement timeout would always trigger first.  
  
Setting lock_timeout in postgresql.conf is not recommended because it would affect all sessions.  

3. http://blog.163.com/digoal@126/blog/static/16387704020131172754749/

4. http://blog.163.com/digoal@126/blog/static/16387704020130312271679/

5. http://blog.163.com/digoal@126/blog/static/163877040201291575523922/

6. http://blog.163.com/digoal@126/blog/static/163877040201210134586363/

digoal’s 大量PostgreSQL文章入口