我应该在事务中包含SELECT吗？ [英] Should I include SELECTs in a transaction?
问题描述
当使用数据库事务对多个更新进行分组时，是否应该在事务中包含SELECTs？例如，让我说：
When using a database transaction to group multiple updates, should I include SELECTs inside the transaction as well? For instance, lets say I:
- 获取记录
- 检查该记录的编辑权限， 更新一些记录
- 更新其他记录
- get a record
- check edit permissions for that record, using data from the record
- update some records
- update some other records
我应该在获取记录阶段之前开始事务，还是只是在更新周围？
Should I start the transaction before the "get a record" stage, or just around the updates?
我使用Postgres / Django transaction.atomic（）但我认为这不重要。
I'm using Postgres/Django transaction.atomic() but I don't think it matters here.
推荐答案
p>简短版本：这取决于。
The short version: "It depends".
长版本：
一个读 - 修改 - 写循环，那么不仅必须在事务中，而且必须 SELECT ... FOR UPDATE 您以后打算修改的任何记录。否则，你将面临失去写作的风险，在那里你覆盖了你在读取记录时和写入更新时所做的更新。
If you're doing a read-modify-write cycle, then not only must it be in a transaction, but you must SELECT ... FOR UPDATE any records you later intend to modify. Otherwise you're going to risk lost writes, where you overwrite an update someone else made between when you read the record and when you wrote the update.
SERIALIZABLE 事务隔离也可以帮助这个。
SERIALIZABLE transaction isolation can also help with this.
你真的需要理解并发和隔离。不幸的是，唯一的简单，容易的只是做X的答案没有理解它是通过锁定所有的表涉及到开始每个事务。大多数人不想这样做。
You really need to understand concurrency and isolation. Unfortunately the only simple, easy "just do X" answer without understanding it is to begin every transaction by locking all the tables involved. Most people don't want to do that.
我建议阅读（或两，三或四 - 这是硬的材料） tx隔离文档。尝试使用并发的 psql 会话（多个终端）来创建竞争条件和冲突。
I suggest a read (or two, or three, or four - it's hard material) of the tx isolation docs. Experiment with concurrent psql sessions (multiple terminals) to create race conditions and conflicts.
这篇关于我应该在事务中包含SELECT吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
