锁定Postgres中的特定行 [英] Locking a specific row in postgres

问题描述

我对Postgres足够陌生，并且试图弄清楚如何锁定表的特定行。

I'm new enough to Postgres, and I'm trying to figure out how to lock a specific row of a table.

例如，我有一个用户表：

As an example, I have a table of users:

Name: John, Money: 1
Name: Jack, Money: 2

在我的后端，我想选择John，并确保在我的交易完成之前，没有其他电话可以更新（甚至可能选择）John的行。

In my backend, I want to select John and make sure that no other calls can update (or even select possibly) John's row until my transaction is complete.

我认为我需要排他性锁定我在网上阅读的内容？我似乎找不到一个很好的示例，可以在线锁定一个表中的仅一行，有任何想法吗？

I think I need an exclusive lock from what I've read up online? I can't seem to find a good example of locking just 1 row from a table online, any idea?

编辑-我应该在@这样的方法级别上进行此操作吗？ SqlUpdate（或某种形式-使用org.skife.jdbi.v2）还是在查询本身中？

Edit - Should I be doing it at method level like @SqlUpdate (or some form of that - using org.skife.jdbi.v2) or in the query itself?

推荐答案

如果要将表锁定在特定的选定行中，则需要 LOCK FIRST code>使用 FOR UPDATE / FOR SHARE 语句。
例如，如果您需要锁定第一行，请执行以下操作：

If you want to lock the table in a specific selected row you need to LOCK FIRST them use the FOR UPDATE / FOR SHARE statement. For example, in your case if you need to lock the first row you do this:

BEGIN;

LOCK TABLE person IN ROW EXCLUSIVE MODE;

-- BLOCK 1

SELECT * FROM person WHERE name = 'John' and money = 1 FOR UPDATE;

-- BLOCK 2

UPDATE person set name = 'John 2' WHERE name = 'John' and money = 1;

END;

在<$ c $ BLOCK1 之前的 SELECT 语句只告诉数据库嘿，我将在此表中执行某些操作，所以在执行此操作时，请以此模式锁定此表。您可以选择/更新/删除任何行。

In the BLOCK1 before the SELECT statement you are doing nothing only telling the database "Hey, I will do something in this table, so when I do, lock this table in this mode". You can select / update / delete any row.

但是在 BLOCK2 中，当您使用对于更新，您可以将该行锁定到特定模式的其他事务（请阅读文档以获取更多详细信息）。

But in BLOCK2 when you use the FOR UPDATE you lock that row to other transactions to specific modes(read the doc for more details). Will be locked until that transaction ends.

如果您需要一个示例，请进行测试，然后尝试执行另一个 SELECT ... FOR UPDATE 结束第一笔交易之前，在 BLOCK2 中的code>。

If you need a example do a test and try to do another SELECT ... FOR UPDATE in BLOCK2 before end the first transaction. It will be waiting the first transaction to end and will select right after it.

只有一个ACCESS EXCLUSIVE锁会阻止SELECT（不包含FOR $）。 b $ b UPDATE / SHARE）语句。

Only an ACCESS EXCLUSIVE lock blocks a SELECT (without FOR UPDATE/SHARE) statement.

我在函数中使用它来控制子序列，这很棒。希望你喜欢。

I am using it in a function to control subsequences and it is great. Hope you enjoy.

这篇关于锁定Postgres中的特定行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！