有关“选择更新”的一些问题？ [英] some questions about "select for update"?

问题描述

这是使用libpq.so的伪代码；但是它不符合我的想法。

 事务开始
 
 re1 = [从表1中选择ics_time，其中c1 = c11，c2 = c22， c3 = c33，c4 = c44用于更新]； 
 
 if（re1满足条件）
 {
 re2 = [选择id，其中c1 = c11，c2 = c22，c3 = c33，c4 = c44用于更新]; 
从table1中删除，其中id = re2; 
从table2中删除，其中id = re2; 
从table3中删除，其中id = re3; 
以c1，c2，c3，c4作为主键将新记录插入到table1，table2，table3中； 
] 
提交或回滚
  

请注意，c1，c2，c3，c4都被设置为数据库中的主键，因此数据库中只有这些键的一行。

让我感到困惑的是：

1. 有两个选择更新将锁定同一行。在
   此代码中，第二条SQL语句是否等待第一条语句阻塞的排他锁
   ？但是，实际情况是
   不会发生。


2. 发生了超出我预期的事情。在日志中，我看到大量
   重复插入错误。在我看来，选择
   用于更新用唯一的键锁定行，两个进程
   依次进行。插入操作在删除之后进行。这些
   重复插入如何发生？

选择更新不是在该行中添加
排他锁，以阻止
要锁定同一行的所有其他进程吗？

解决方案

关于您的第一点：语句不持有锁，周围的事务持有锁。您的伪代码似乎对一个事务使用一个连接，而该事务又使用多个语句。因此，第二个 SELECT FOR UPDATE 不会被第一个阻塞。请阅读有关锁定的文档： / p>

[...]在更新或删除行时，将自动获取特定行的排它级锁定。就像表级锁一样，该锁将保持到事务提交或回滚为止。行级锁不影响数据查询。他们只会阻止作家到同一行。

否则，如果交易能够如此轻松地阻止自身，那就太可笑了。

p>

关于第二点：我无法回答，因为a）您的伪代码是针对此问题的伪代码，b）我不明白您所说的过程是什么意思，以及确切的用例。

Here is Pseudo code using libpq.so;but it does not go as what I think.

transaction begin

re1 = [select ics_time from table1 where c1=c11, c2=c22, c3=c33, c4=c44 for update];

if(re1 satisfies the condition)
{
   re2 = [select id where c1=c11, c2=c22, c3=c33, c4=c44 for update];
   delete from table1 where id = re2;
   delete from table2 where id = re2;
   delete from table3 where id = re3;
   insert a new record into table1,table2,table3 with the c1,c2,c3,c4 as primary keys;
]
commit or rollback

Note that c1,c2,c3,c4 are all set as the primary key in the database, so it is only one row with these keys in the database.

What confuses me is as follows:

1. There are two "select for update" which will lock the same row. In this code, does the second SQL statement wait for the exclusive lock blocked by the first statement? But, the actual situation is that it does not happen.
2. Something occurs beyond my expectation. In the log, I see a large number of duplicate insert errors. In my opinion that the "select for update " locks the row with the unique for keys, two processes go serially. The insert operation goes after a delete. How can these duplicate insertation occur? Doesn't the "select for update" add an exclusive lock to the row, which blocks all other processes that want to lock the same row?

解决方案

Regarding your first point: Locks are not held by the statement, locks are held by the surrounding transaction. Your pseudo-code seems to use one connections with one transaction which in turn uses several statements. So the second SELECT FOR UPDATE is not blocked by the first. Read the docs about locking for this:

[...]An exclusive row-level lock on a specific row is automatically acquired when the row is updated or deleted. The lock is held until the transaction commits or rolls back, just like table-level locks. Row-level locks do not affect data querying; they block only writers to the same row.

Otherwise it would be very funny, if a transaction could block itself so easily.

Regarding your second point: I cannot answer this because a) your pseudo code is to pseudo for this problem and b) I don't understand what you mean by "processes" and the exact usecase.

这篇关于有关“选择更新”的一些问题？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！