英文:
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 Condition int
var Task string
rows.Scan(&Id, &Condition, &Task)
if Condition == 0 {
UpdateTask(Id, Task)
}
}
UpdateTask()函数将发出SQL更新语句来更新行。
在SELECT查询中发出SQL更新语句会锁定数据库吗？这是执行此类更新的正确方式吗？
英文:
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?
答案1
得分: 6
首先，至少你应该使用SELECT ... FOR UPDATE来锁定行，以防止其他SELECT ... FOR [SHARE|UPDATE]访问。你必须在事务内执行此操作，并保持该事务直到更新最后一行并提交。
你使用SELECT ... FOR UPDATE锁定的行不会对普通的SELECT操作产生影响；它们仍然可以被其他未使用FOR UPDATE或FOR SHARE的事务读取。
更好的做法是尝试将整个操作重构为UPDATE ... FROM或其他基于集合的操作，在单个查询中完成所有工作。这通常比先执行SELECT ... FOR UPDATE，然后执行一系列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.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论