新加入的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 = 1; 执行 执行成功。 然后在B中输入 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; 执行 阻塞中。。。 然后在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，这时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_transaction and max_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; locktype | database | relation | page | tuple | virtualxid | transactionid | classid | objid | objsubid | virtualtransaction | pid | 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_lock(1); pg_advisory_lock ------------------ (1 row) digoal=> begin; BEGIN digoal=> select pg_advisory_unlock(1); pg_advisory_unlock -------------------- t (1 row) digoal=> rollback; ROLLBACK digoal=> select * from pg_locks where objid=1; locktype | database | relation | page | tuple | virtualxid | transactionid | classid | objid | objsubid | virtualtransaction | pid | 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 -------------------- t (1 row) digoal=> select * from pg_locks where objid=1; locktype | database | relation | page | tuple | virtualxid | transactionid | classid | objid | objsubid | virtualtransaction | pid | mode | granted ----------+----------+----------+------+-------+------------+---------------+---------+-------+----------+--------------------+----- --+---------------+--------- advisory | 16386 | | | | | | 0 | 1 | 1 | 2/9198 | 2598 8 | ExclusiveLock | t (1 row) digoal=> select pg_advisory_unlock(1); pg_advisory_unlock -------------------- t (1 row) digoal=> select * from pg_locks where objid=1; locktype | database | relation | page | tuple | virtualxid | transactionid | classid | objid | objsubid | virtualtransaction | pid | 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; locktype | database | relation | page | tuple | virtualxid | transactionid | classid | objid | objsubid | virtualtransaction | pid | 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 -------------------- t (1 row) 这里释放了SESSION A 的SESSION ADVISORY LOCK。 digoal=> select * from pg_locks where objid=1; locktype | database | relation | page | tuple | virtualxid | transactionid | classid | objid | objsubid | virtualtransaction | pid | 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