基於postgresql行級鎖for update測試

Posted on by

創建表：

CREATE TABLE db_user
(
 id character varying(50) NOT NULL,
 age integer,
 name character varying(100),
 roleid character varying,
 CONSTRAINT db_user_pkey PRIMARY KEY (id)
)

隨便插入幾條數據即可。

一、不加鎖演示

1、打開一個postgreSQL的SQL Shell或pgAdmin的SQL編輯器窗口，執行：

begin;

select * from db_user where name=’lisi’;

輸出結果：

2、再打開一個postgreSQL的SQL Shell或pgAdmin的SQL編輯器窗口，執行：

begin;

select * from db_user where name=’lisi’;

輸出結果：

二、加鎖演示（for update）

1、打開一個postgreSQL的SQL Shell或pgAdmin的SQL編輯器窗口，執行：

begin;

select * from db_user where name=’lisi’ for update;

輸出結果：

2、再打開一個postgreSQL的SQL Shell或pgAdmin的SQL編輯器窗口，執行：

begin;

select * from db_user where name=’lisi’ for update;

輸出結果：

查詢一直處於執行中狀態。

3、第一個窗口執行：

commit;

第二個窗口立即執行查詢操作，結果如下：

第二個窗口記得提交commit;。

三、加鎖演示（for update nowait）

1、打開一個postgreSQL的SQL Shell或pgAdmin的SQL編輯器窗口，執行：

begin;

select * from db_user where name=’lisi’ for update nowait;

輸出結果：

2、再打開一個postgreSQL的SQL Shell或pgAdmin的SQL編輯器窗口，執行：

begin;

select * from db_user where name=’lisi’ for update nowait;

輸出結果：

不會進行資源等待，返回錯誤信息。

3、第一個窗口執行：

commit;

提交成功，資源鎖釋放。

總結：

for update nowait和 for update 都會對所查詢到得結果集進行加鎖，所不同的是，如果另外一個線程正在修改結果集中的數據，for update nowait 不會進行資源等待，隻要發現結果集中有些數據被加鎖，立刻返回 “55P03錯誤，內容是無法在記錄上獲得鎖.

命令說明：

begin;–開啟事務

begin transaction;–開啟事務

commit;–提交

rollback;–回滾

set lock_timeout=5000;–設置超時時間

註意：

連表查詢加鎖時，不支持單邊連接形式，例如：

select u.*,r.* from db_user u left join db_role r on u.roleid=r.id for update;

支持以下形式，並鎖住瞭兩個表中關聯的數據：

select u.*,r.* from db_user u, db_role r where u.roleid=r.id for update;

補充：PostgreSQL select for update指定列(兼容oracle)

我們可以使用select for update語句來指定鎖住某一張表，在oracle中我們可以在for update語句後指定某一列，用來單獨鎖定指定列的數據。

oracle例子：

建表：

SQL> create table t1(id int, c2 varchar(20), c3 int, c4 float, c5 float); 
Table created.
SQL> create table t2(id int, c6 int); 
Table created.
SQL> insert into t1 values (1, 'SA_REP', 1, 100, 1); 
1 row created.
SQL> insert into t1 values (1, 'SA_REP123', 1, 100, 1); 
1 row created.
SQL> insert into t2 values (1, 2500);
1 row created.

查詢：

我們使用下列查詢用來隻鎖住c4列。

SQL> SELECT e.c3, e.c4, e.c5 
 2   FROM t1 e JOIN t2 d 
  USING (id) 
  WHERE c2 = 'SA_REP' 
  AND c6 = 2500 
 3  4  5  6   FOR UPDATE OF e.c4 
 7   ORDER BY e.c3; 
    C3     C4     C5
---------- ---------- ----------
     1    100     1

PostgreSQL兼容方法：

建表：

create table t1(id int, c2 text, c3 int, c4 float, c5 float); 
create table t2(id int, c6 int); 
insert into t1 values (1, 'SA_REP', 1, 100, 1); 
insert into t1 values (1, 'SA_REP123', 1, 100, 1); 
insert into t2 values (1, 2500);

pg中使用方法和oracle類似，隻是需要將order by語法放到前面，並且將列名換成表名。

bill=# SELECT e.c3, e.c4, e.c5 
bill-#  FROM t1 e JOIN t2 d 
bill-#  USING (id) 
bill-#  WHERE c2 = 'SA_REP' 
bill-#  AND c6 = 2500 
bill-#  ORDER BY e.c3
bill-#  FOR UPDATE OF e ;
 c3 | c4 | c5 
----+-----+----
 1 | 100 | 1
(1 row)

驗證：

我們可以驗證下pg中是否隻鎖定瞭指定的行。

1、安裝pgrowlocks插件

bill=# create extension pgrowlocks;

CREATE EXTENSION

2、觀察

t1表被鎖：

bill=# select * from pgrowlocks('t1'); 
 locked_row | locker | multi | xids |   modes   | pids 
------------+--------+-------+--------+----------------+--------
 (0,1)   |  1037 | f   | {1037} | {"For Update"} | {2022}
(1 row)

t2表沒有被鎖：

bill=# select * from pgrowlocks('t2');  
 locked_row | locker | multi | xids | modes | pids 
------------+--------+-------+------+-------+------
(0 rows)

我們還可以再看看t1表中具體被鎖住的數據：

bill=# SELECT * FROM t1 AS a, pgrowlocks('t1') AS p 
bill-#  WHERE p.locked_row = a.ctid; 
 id |  c2  | c3 | c4 | c5 | locked_row | locker | multi | xids |   modes   | pids 
----+--------+----+-----+----+------------+--------+-------+--------+----------------+--------
 1 | SA_REP | 1 | 100 | 1 | (0,1)   |  1037 | f   | {1037} | {"For Update"} | {2022}
(1 row)

除此之外，pg中for update子句還有其它的選項：

UPDATE – 當前事務可以改所有字段

NO KEY UPDATE – 當前事務可以改除referenced KEY以外的字段

SHARE – 其他事務不能改所有字段

KEY SHARE – 其他事務不能改referenced KEY字段

以上為個人經驗，希望能給大傢一個參考，也希望大傢多多支持WalkonNet。如有錯誤或未考慮完全的地方，望不吝賜教。

推薦閱讀：