正确的方式来选择和更新SQL [英] Proper way to SQL select and update

问题描述

我正在使用github.com/bmizerany/pq来对付Postgres数据库。我需要选择todos表中的所有行，并为每一行检查条件并相应地更新行。伪码：

  rows，_：= dbConn.Query（SELECT id，condition，task FROM todos）
 
 for rows.Next（）{
 var Id int 
 var条件int 
 var任务字符串
 rows.Scan（& Id，& Condition& amp; ;任务）
如果条件== 0 {
 UpdateTask（Id，Task）
} 
} 
  

UpdateTask（）函数将发出一条SQL更新语句来更新行。

将在SELECT中发出SQL更新查询锁定数据库？这是做这种更新的正确方法吗？

解决方案

首先， minimum 你应该做一个 SELECT .. 。FOR UPDATE 这样你就可以锁定行与其他 SELECT ... FOR [共享|更新] 访问。您必须在事务内部执行此操作，并保留该事务，直到您更新最后一行并 commit 。

$ b

您正在锁定的行 SELECT ... FOR UPDATE C $ C>选择;对于其他未使用 FOR UPDATE 或 FOR SHARE 的其他交易，他们仍然可以读取。

更好的是，尝试将整个事件改写为 UPDATE ... FROM 或其他基于集合的操作，在单个查询中工作。它通常会比 SELECT ... FOR UPDATE 大得多，后面是 UPDATE s。 / p>

I'm using github.com/bmizerany/pq against a Postgres database. I need to select all the rows in the "todos" table, and for each row check for condition and update the row accordingly. Pseudo codes:

rows, _ := dbConn.Query("SELECT id, condition, task FROM todos")

for rows.Next() {
    var Id int
    var Condition int
    var Task string
    rows.Scan(&Id, &Condition, &Task)
    if Condition == 0 {
           UpdateTask(Id, Task)
    }
}

The UpdateTask() function will issue a SQL update statement to update the row.

Will issuing SQL update within a SELECT query lock the database? Is this the proper way of doing such update?

解决方案

First, at minimum you should be doing a SELECT ... FOR UPDATE so you lock the rows against other SELECT ... FOR [SHARE|UPDATE] access. You must do this inside a transaction and hold that transaction until you update the last row and commit.

The rows you SELECT ... FOR UPDATE not locked against normal SELECT; they're still readable to other transactions that aren't using FOR UPDATE or FOR SHARE.

Better still, try to rephrase the whole thing as an UPDATE ... FROM or other set-based operation where you do all the work in a single query. It'll generally perform massively better than a SELECT ... FOR UPDATE followed by a stream of UPDATEs.

这篇关于正确的方式来选择和更新SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！