锁定行，直到下一个选择postgres [英] lock the rows until next select postgres

问题描述

postgres中是否有一种方法可以锁定行，直到从同一系统执行下一个选择查询为止。还有一件事是 在锁定的行上将没有更新过程。
的情况是这样的

如果表1包含这样的数据

  id | txt 
 ------------------- 
 1 |世界
 2 |文字
 3 |爬虫
 4 |解决方案
 5 |国家
 6 |低于
 7 |填充
 8 |结算
 9 |开始
 10 |大
 11 |某人
 12 |跳舞
  

如果sys1执行

 从表1中选择*，ID限制为5； 
  

然后，对于其他同时执行select语句的系统，它应该将ID从1锁定为5的行。 / p>

稍后，如果sys1再次执行另一个选择查询，例如

  select * from表1，其中ID＞ 10的ID限制为5； 
  

然后应该释放以前锁定的行。

解决方案

我认为这是不可能的。您不能阻止对表的只读访问（除非选择已完成 FOR UPDATE ）

我可以说，您唯一的机会就是使用 pg_advisory_lock（）函数。

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

但这需要手册释放通过它获得的锁。

要锁定行，您需要类似以下内容：

 
 select pg_advisory_lock（id），* 
 from 
（
 select * table1 id id order 5 
）t 
 

（请注意LIMIT部分使用派生表。有关说明，请参见我发布的手册链接）

然后，您需要存储检索到的ID，以后再为每个ID调用 pg_advisory_unlock（）。

如果每个进程始终一次发布所有 ID，则可以简单地使用 pg_advisory_unlock_all（）。然后，您将不需要存储检索到的ID。

请注意，这将不阻止其他人使用正常选择读取行。仅当访问该表的每个进程使用相同的获取锁的模式时，它才起作用。

Is there a way in postgres to lock the rows until the next select query execution from the same system.And one more thing is there will be no update process on locked rows. scenario is something like this

If the table1 contains data like

id |    txt
-------------------
1 |    World 
2 |    Text
3 |    Crawler
4 |    Solution
5 |    Nation
6 |    Under
7 |    Padding
8 |    Settle
9 |    Begin
10 |    Large
11 |    Someone
12 |    Dance

If sys1 executes

select * from table1 order by id limit 5;

then it should lock row from id 1 to 5 for other system which are executing select statement concurrently.

Later if sys1 again execute another select query like

select * from table1 where id>10 order by id limit 5;

then pereviously locked rows should be released.

解决方案

I don't think this is possible. You cannot block a read only access to a table (unless that select is done FOR UPDATE)

As far as I can tell, the only chance you have is to use the pg_advisory_lock() function.
http://www.postgresql.org/docs/current/static/functions-admin.html#FUNCTIONS-ADVISORY-LOCKS

But this requires a "manual" release of the locks obtained through it. You won't get an automatic unlocking with that.

To lock the rows you would need something like this:

select pg_advisory_lock(id), * 
from 
( 
  select * table1 order by id limit 5
) t

(Note the use of the derived table for the LIMIT part. See the manual link I posted for an explanation)

Then you need to store the retrieved IDs and later call pg_advisory_unlock() for each ID.

If each process is always releasing all IDs at once, you could simply use pg_advisory_unlock_all() instead. Then you will not need to store the retrieved IDs.

Note that this will not prevent others from reading the rows using "normal" selects. It will only work if every process that accesses that table uses the same pattern of obtaining the locks.

这篇关于锁定行，直到下一个选择postgres的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！