4.5、行锁在事务中的运用

4.5.1、环境准备

postgres=# create table t_row_lock(id int,mc text,primary key (id));
NOTICE:  Replica identity is needed for shard table, please add to this table through "alter table" command.
CREATE TABLE
postgres=# 
postgres=# insert into t_row_lock values(1,'tbase'),(2,'pgxz');       
INSERT 0 2
postgres=# select * from t_row_lock;
 id |  mc   
----+-------
  1 | tbase
  2 | pgxz
(2 rows)

4.5.2、直接update获取

session1

postgres=# begin;
BEGIN
postgres=# set lock_timeout to 1;
SET
postgres=# update t_row_lock set mc='postgres' where mc='pgxz';
UPDATE 1
postgres=# 

session2

postgres=# begin;
BEGIN
postgres=# set lock_timeout to 1;
SET
postgres=#  update t_row_lock set mc='postgresql' where mc='tbase';        
UPDATE 1
postgres=# 

上面session1与session2分别持有mc=pgxz行和mc=tbase的行锁

4.5.3、select…for update获取

session1

postgres=#  
BEGIN
postgres=# set lock_timeout to 1; 
SET
postgres=# select * from t_row_lock where mc='pgxz' for update;
 id |  mc  
----+------
  2 | pgxz
(1 row)

session2

postgres=# begin;
BEGIN
postgres=# set lock_timeout to 1; 
SET
postgres=# select * from t_row_lock where mc='tbase' for update;
 id |  mc  
----+------
  2 | pgxz
(1 row)

上面session1与session2分别持有mc=pgxz行和mc=tbase的行锁

4.5.4、排它锁检查

postgres=# select pid,pg_blocking_pids(pid),wait_event_type,query from pg_stat_activity where wait_event_type = 'Lock' and pid!=pg_backend_pid()