HGDB（pg）行锁现象

会话1：
highgo=# set application_name='lockingclient';
SET
highgo=# begin;
BEGIN
highgo=# select * from test for update;
 id | name  
----+-------
  1 | aaaaa
  2 | bbbbb
(2 rows)

会话2：
highgo=# set application_name='waitingclient';
SET
highgo=# select * from test for update;
---此时hang住

会话3:
highgo=# select pid,waiting,query,state,application_name from pg_stat_activity;
 pid  | waiting |                                 query                                  |        state        | application_name 
------+---------+------------------------------------------------------------------------+---------------------+------------------
 6367 | f       | select * from test for update;                                         | idle in transaction | lockingclient
 6313 | f       | select pid,waiting,query,state,application_name from pg_stat_activity; | active              | psql
 6353 | t       | select * from test for update;                                         | active              | waitingclient
(3 rows)

highgo=#  select pl.locktype,pl.mode,pc.relname,pl.pid,psa.state,psa.query from pg_locks pl join pg_class pc on pl.relation=pc.oid join pg_stat_activity psa on pl.pid=psa.pid where relname='test';
 locktype |        mode         | relname | pid  |        state        |             query              
----------+---------------------+---------+------+---------------------+--------------------------------
 relation | RowShareLock        | test    | 6353 | active              | select * from test for update;
 relation | RowShareLock        | test    | 6367 | idle in transaction | select * from test for update;
 tuple    | AccessExclusiveLock | test    | 6353 | active              | select * from test for update;
(3 rows)

highgo=# select waiting1.pid as waiting_pid,waiting2.usename as waiting_user,waiting2.query as waiting_statment,blocking1.pid as blocking_pid,blocking2.usename as blocking_user,blocking2.query as blocking_statement from pg_locks waiting1 join pg_stat_activity waiting2 on waiting1.pid=waiting2.pid join pg_locks blocking1 on waiting1.transactionid=blocking1.transactionid and waiting1.pid != blocking1.pid join pg_stat_activity blocking2 on blocking1.pid = blocking2.pid where not waiting1.granted;
 waiting_pid | waiting_user |        waiting_statment        | blocking_pid | blocking_user |       blocking_statement       
-------------+--------------+--------------------------------+--------------+---------------+--------------------------------
        6353 | highgo       | select * from test for update; |         6367 | highgo        | select * from test for update;
(1 row)


[root@sourcedb ~]# ps -ef |grep 6353
highgo    6353  1948  0 15:19 ?        00:00:00 postgres: highgo highgo [local] SELECT waiting
root      6474  6445  0 15:37 pts/3    00:00:00 grep 6353
[root@sourcedb ~]# ps -ef |grep 6367
highgo    6367  1948  0 15:21 ?        00:00:00 postgres: highgo highgo [local] idle in transaction
root      6478  6445  0 15:37 pts/3    00:00:00 grep 6367


会话3结束持锁的进程：
highgo=# select pg_terminate_backend(6492);
 pg_terminate_backend 
----------------------
 t
(1 row)


会话1：
highgo=# 致命错误:  由于管理员命令中断联接


会话2能给查出执行结果：
highgo=# set application_name='waitingclient';
SET
highgo=# select * from test for update;
 id | name  
----+-------
  1 | aaaaa
  2 | bbbbb
(2 rows)


highgo=# 




**********************************************************************
如若在会话3：
[highgo@sourcedb ~]$ kill -9 6543     此种做法，会断开所有有关会话；导致持锁和被锁的会话都回滚


会话1：
highgo=# 警告:  PID 6492 不是 PostgreSQL 服务器进程
日志:  服务器进程 (PID 6543) 被信号 9 中断: Killed
详细信息:  失败进程:select * from test for update;正在运行
日志:  中断任何其它已激活的服务器进程
警告:  中断联接, 因为其它服务器进程崩溃
详细信息:  Postmaster 命令此服务器进程回滚当前事物并退出, 因为其它服务器进程不正常的退出可能毁坏了共享内存.
提示:  一会儿你将可以重联接数据库并且重复你的命令.
上下文:  当锁定关系"test"的元组(0, 1)时
警告:  中断联接, 因为其它服务器进程崩溃
详细信息:  Postmaster 命令此服务器进程回滚当前事物并退出, 因为其它服务器进程不正常的退出可能毁坏了共享内存.
提示:  一会儿你将可以重联接数据库并且重复你的命令.
日志:  所有的服务器进程被中止; 重新初始化
日志:  数据库系统中断；上一次的启动时间是在2017-05-10 15:42:53 CST
致命错误:  数据库系统在恢复模式中
日志:  数据库系统没有正确的关闭; 处于自动恢复状态中
日志:  record with zero length at 0/22E8940
日志:  不需要 redo
日志:  MultiXact member wraparound protections are now enabled
日志:  数据库系统准备接受连接
日志:  已启动autovacuum

会话2：
highgo=# select * from test for update;
警告:  中断联接, 因为其它服务器进程崩溃
DETAIL:  Postmaster 命令此服务器进程回滚当前事物并退出, 因为其它服务器进程不正常的退出可能毁坏了共享内存.
HINT:  一会儿你将可以重联接数据库并且重复你的命令.
CONTEXT:  当锁定关系"test"的元组(0, 1)时
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: Failed.
!> 

会话4：---与锁无关的会话也会断开
highgo=# select * from a.test;
 id 
----
  4
  3
  5
  6
(4 rows)


highgo=# select * from a.test;
警告:  中断联接, 因为其它服务器进程崩溃
DETAIL:  Postmaster 命令此服务器进程回滚当前事物并退出, 因为其它服务器进程不正常的退出可能毁坏了共享内存.
HINT:  一会儿你将可以重联接数据库并且重复你的命令.
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.
highgo=# 

---此时数据库服务并未重启，只是所有连接断开。


****************************************************************
使用如下方式Kill相关会话仅中断需要kill的会话。其他会话不会被kill掉：
[highgo@db1 ~]$ pg_ctl kill INT 9474
或者
highgo=# select pg_cancel_backend(9474);
 pg_cancel_backend 
-------------------
 t
(1 row)
******************************************************************************************