postgresql Advisory Locks

新加入的Advisory Locks似乎是个不错的特性

http://www.postgresql.org/docs/8.2/interactive/explicit-locking.html#ADVISORY-LOCKS
http://www.postgresql.org/docs/8.2/interactive/functions-admin.html#FUNCTIONS-ADVISORY-LOCKS

这里有一篇介绍的blog
http://merlinmoncure.blogspot.com/

个人的一些实验：
pgamdin III 1.6.1
Postgresql 8.2
windows XP
建立一个测试表
CREATE TABLE tests
(
id serial NOT NULL,
name character varying(32),
CONSTRAINT tests_pkey PRIMARY KEY(id)
)
WITHOUT OIDS;

测试数据
insert into tests(name) values ('dafei'),('feifei'),('afei');

[B]实验一：[/B]
分别打开两个SQL查询窗口A，B
在A中输入
select pg_advisory_lock(id),* from tests where id = 1;
执行
执行成功。

然后在B中输入
select pg_advisory_lock(id),* from tests where id = 1;
执行
阻塞中。。。

然后在A中，输入
select pg_advisory_unlock(id),from tests where id = 1;
执行成功，返回 t
这个时候，B中语句执行成功，解除了阻塞。

[B]实验二:[/B]
在A中输入
select pg_advisory_lock(id),* from tests where id = 1;
执行两次，
均执行成功。

然后在B中输入
select pg_advisory_lock(id),* from tests where id = 1;
执行
阻塞中。。。
然后在A中，输入
select pg_advisory_unlock(id),from tests where id = 1;
执行成功，返回 t
但是B仍阻塞，
再次执行
select pg_advisory_unlock(id),from tests where id = 1;
执行成功，返回 t
这个时候，B中语句执行成功，解除了阻塞。

[B]实验三:[/B]

在A中输入
select pg_advisory_lock(id),* from tests where id = 1;
执行
执行成功。

然后在B中输入
select pg_advisory_unlock(id),from tests where id = 1;
执行成功，返回 f
然后执行
select pg_advisory_lock(id),* from tests where id = 1;
执行阻塞中。。。

然后在A中，输入
select pg_advisory_unlock(id),from tests where id = 1;
执行成功，返回 t
这个时候，B中语句执行成功，解除了阻塞。

[B]实验四:[/B]

在A中输入
select pg_advisory_lock(id),from tests where id = 2;
执行
执行成功。

[B]实验五:[/B]

在A中输入
select pg_advisory_lock(id),* from tests where id = 1;
执行
执行成功。
然后再输入
select * from tests where id = 1;
执行成功

然后在B中输入
select * from tests where id = 1;
执行成功

[B]实验六：[/B]
在A中输入
begin;
select pg_advisory_lock(id),* from tests where id = 1;
commit;
执行
执行成功。

然后在B中输入
select pg_advisory_lock(id),from tests where id = 1;
执行成功，返回 t
这个时候，B中语句执行成功，解除了阻塞。

[B]实验七：[/B]
在A中输入
select pg_advisory_lock(id),* from tests where id = 1;
执行
执行成功。

然后在B中输入
select pg_advisory_lock(id),* from tests where id = 1;
执行
阻塞中。。。

关闭A，这时B仍在阻塞中。

停止运行B，重新执行
select pg_advisory_lock(id),* from tests where id = 1;
执行成功。

[B]实验八：[/B]
在A中输入
select pg_advisory_lock(id),* from tests where id = 1;
执行
执行成功。

然后关闭A

然后在B中输入
select pg_advisory_lock(id),* from tests where id = 1;
执行
执行成功。

[B]对比实验一[/B]
使用相近的for update语句
在A中输入
select * from tests where id = 1 for update;
执行
执行成功。

在B中输入
select * from tests where id = 1 for update;
执行
执行成功。

[B]对比实验二[/B]

在A中输入
begin;
select * from tests where id = 1 for update;
执行
执行成功。

在B中输入
select * from tests where id = 1 for update;
执行
阻塞中。。。
然后在A中执行 commit;
执行成功，这个时候，B中语句执行成功，解除了阻塞。

[B]实验结果分析[/B]
从对比实验来看
for update所使用的锁 Row Share，会在事务结束时释放。
从实验六来看 advisory lock 不会在事务结束时释放（实验6）。
从实验来看，advisory lock 会在用户当前Session中有效，当会话结束时，会释放所有的advisory lock（实验七,实验八）
不过，从实验七来看，似乎如果其他会话仍在请求同一资源的话，并不回感应到锁定该资源的会话退出导致的advisory lock 释放，
必须下次请求时，才会感应到，不知道这个是不是一个bug（这一点我的测试结果与本文原作者有些不同。假设A、B两个窗口进行测试，如果A先LOCK，然后B再LOCK，这时B会HANG住。这时如果A使用/q正常退出，那么B是会感应到的并且执行成功；但如果我使用CTRL+Z在A窗口强行退出，那么B是感知不到的，仍然阻塞，这时通过pg_locks仍然可以看到原先A窗口相应的进程在持有锁，通过OS命令KILL掉相应的进程，B就感知到了并执行LOCK成功。）

从实验来看advisory lock 不受事务的影响，在同意个会话中 advisory lock 可以多次lock，但是必须与unlock成对使用（实验二）。
advisory lock 不会造成同一个表被锁定（实验四）
advisory lock 不会阻塞一般的查询，包括for update语句。（两个窗口同时执行select ... for update还是会互锁的）（实验五）

个人感觉advisory lock 最大的优势在于与事务无关，或者跨事务锁定。
对于 pg_advisory_lock_shared 没有进行详尽实验，
在两个会话中可以同时执行
select pg_advisory_lock_shared(id),* from tests;
但是某个会话中执行 pg_advisory_lock_shared后，其他会话中执行pg_advisory_lock 会阻塞。

##########下面是转的另一篇关于ADVISORY LOCK的文章########

Postgresql 9.1 新增了一个事务级的advisory lock，原来只有SESSION级的。

事务级别的advisory lock不能显性的释放。

advisory lock和系统的MVCC不是一个概念，基本上完全不相干。锁的数量由 max_locks_per_transactionandmax_connections决定。

advisory lock可以在pg_locks视图里面看到。

SESSION级的advisory lock一旦获取，需要手工释放或者SESSION 结束后自动释放。SESSION级别的advisory lock还有一个和事务锁不一样的风格。

如:

1. 在一个begin; end;之间获取SESSION级别的advisory lock时，事务被回滚或者事务Failed 的话SESSION advisory锁不会被回滚。

digoal=> begin;

BEGIN

digoal=> select pg_advisory_lock(1);

pg_advisory_lock

------------------

(1 row)

digoal=> rollback;

ROLLBACK

digoal=> select * from pg_locks where objid=1;

| mode | granted

----------+----------+----------+------+-------+------------+---------------+---------+-------+----------+--------------------+-----

--+---------------+---------

advisory | 16386 | | | | | | 0 | 1 | 1 | 2/9186 | 2598

8 | ExclusiveLock | t

(1 row)

2. 在一个begin; end;之间释放SESSION级别的advisory lock时，事务被回滚或者事务Failed的话SESSION advisory锁仍被释放。

digoal=> select pg_advisory_unlock(1);

pg_advisory_unlock

--------------------

| mode | granted

+------+---------

(0 rows)

3. 同一个session 级别的advisory lock可以在一个SESSION里面多次获取，但是释放也要多次释放。

如下，获取两次，释放两次。

digoal=> select pg_advisory_lock(1);

pg_advisory_lock

------------------

(1 row)

digoal=> select pg_advisory_lock(1);

pg_advisory_lock

------------------

(1 row)

digoal=> select pg_advisory_unlock(1);

pg_advisory_unlock

--------------------

(1 row)

digoal=> select * from pg_locks where objid=1;

| mode | granted

----------+----------+----------+------+-------+------------+---------------+---------+-------+----------+--------------------+-----

--+---------------+---------

advisory | 16386 | | | | | | 0 | 1 | 1 | 2/9198 | 2598

8 | ExclusiveLock | t

(1 row)

digoal=> select pg_advisory_unlock(1);

pg_advisory_unlock

--------------------

(1 row)

digoal=> select * from pg_locks where objid=1;

| mode | granted

----------+----------+----------+------+-------+------------+---------------+---------+-------+----------+--------------------+-----

+------+---------

(0 rows)

4. session和transaction 级别的lock在同一个SESSION中共用锁空间，所以在TRANSACTION中获取到了锁，在同一个SESSION中也可以获取到。另一个SESSION就获取不到。TRANSACTION ADVISORY LOCK不可以显性释放，会自动在事务结束（包含提交或回滚，正常或不正常）后释放。

如下

SESSION A：

digoal=> begin;

BEGIN

digoal=> select pg_advisory_xact_lock(1);

pg_advisory_xact_lock

-----------------------

(1 row)

这里获取到事务级的ADVISORY 锁。

digoal=> select pg_advisory_lock(1);

pg_advisory_lock

------------------

(1 row)

这里获取到SESSION ADVISORY LOCK。

digoal=> end;

COMMIT

这里自动释放了TRANSACTION ADVISORY LOCK。

SESSION B:

digoal=> select pg_advisory_lock(1);

等待中。。。

SESSION A:

digoal=> select * from pg_locks where objid=1;

| mode | granted

----------+----------+----------+------+-------+------------+---------------+---------+-------+----------+--------------------+-----

--+---------------+---------

advisory | 16386 | | | | | | 0 | 1 | 1 | 2/9202 | 2598

8 | ExclusiveLock | t

advisory | 16386 | | | | | | 0 | 1 | 1 | 4/76 | 2645

0 | ExclusiveLock | f

(2 rows)

看到SESSION B在等待这个锁。

digoal=> select pg_advisory_unlock(1);

pg_advisory_unlock

--------------------

(1 row)

这里释放了SESSION A 的SESSION ADVISORY LOCK。

digoal=> select * from pg_locks where objid=1;

| mode | granted

----------+----------+----------+------+-------+------------+---------------+---------+-------+----------+--------------------+-----

--+---------------+---------

advisory | 16386 | | | | | | 0 | 1 | 1 | 4/0 | 2645

0 | ExclusiveLock | t

(1 row)

可以看到B已经获得了这个锁。

由于advisory lock和MVCC不相干。所以不存在和MVCC锁的冲突。适合特殊场景的应用，降低锁冲突或者长时间持锁带来的数据库（如VACUUM释放空间,这个我很久前的BLOG有写过）压力。

advisory lock的应用场景举例(应用控制的锁)：

比如数据库里面存储了文件和ID的对应关系，应用程序需要长时间得获得一个锁，然后对文件进行修改，再释放锁。

测试数据:

digoal=> create table tbl_file_info (id int primary key,file_path text);

NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "tbl_file_info_pkey" for table "tbl_file_info"

CREATE TABLE

digoal=> insert into tbl_file_info values (1,'/home/postgres/advisory_lock_1.txt');

INSERT 0 1

digoal=> insert into tbl_file_info values (2,'/home/postgres/advisory_lock_2.txt');

INSERT 0 1

digoal=> insert into tbl_file_info values (3,'/home/postgres/advisory_lock_3.txt');

INSERT 0 1

SESSION A:

digoal=> select pg_advisory_lock(id),file_path from tbl_file_info where id=1;

pg_advisory_lock | file_path

------------------+------------------------------------

| /home/postgres/advisory_lock_1.txt

(1 row)

应用程序对/home/postgres/advisory_lock_1.txt文件进行编辑之后，再释放这个advisory锁。

SESSION B:

当SESSIONA在编辑 /home/postgres/advisory_lock_1.txt这个文件的时候，无法获得这个锁，所以可以确保不会同时编辑这个文件。

如果不使用advisory lock,改用MVCC,来看看如何 :

仍旧使用以上的测试数据，

SESSION A:

digoal=> begin;

BEGIN

digoal=> select file_path from tbl_file_info where id=1 for update;

file_path

------------------------------------

/home/postgres/advisory_lock_1.txt

(1 row)

应用程序对/home/postgres/advisory_lock_1.txt文件进行编辑之后，再释放这个锁。

digoal=> end;

COMMIT

因此这个SESSION在编辑文件的时候，需要保持这个事务，一方面降低了数据库使用的并发性，另一方面带来了长事务，会有回收不了DEAD TUPLE空间的问题，参考我以前写过的BLOG。

http://blog.163.com/digoal@126/blog/static/163877040201011162912604/

DETAIL: xxxx dead row versions cannot be removed yet.

SESSION B:

当SESSIONA在编辑 /home/postgres/advisory_lock_1.txt 这个文件的时候，如果需要更改同一个文件，同样，先获得锁然后操作。

digoal=> select file_path from tbl_file_info where id=1 for update;

等待SESSION A释放锁。

【参考】

http://www.postgresql.org/docs/9.1/static/functions-admin.html#FUNCTIONS-ADVISORY-LOCKS

http://www.postgresql.org/docs/9.1/static/explicit-locking.html#ADVISORY-LOCKS

原文链接：https://www.f2er.com/postgresql/196981.html

postgresql Advisory Locks

猜你在找的Postgre SQL相关文章