Sql Server事务-使用建议
我不仅在一处看到这句话：
“事务应该尽可能短，以避免并发问题并实现最大数量的积极提交。”
这真的是什么意思？
现在这让我很困惑，因为我想在我的应用程序中使用事务，在正常使用中，该应用程序将同时处理来自许多客户端的数百行插入。
例如，我有一个公开方法的服务：AddObjects(List，当然这些对象包含其他嵌套的不同对象。
我正在考虑为客户端的每个调用启动一个事务，执行适当的操作（每个对象及其嵌套对象的一堆插入/更新/删除）。 EDIT1：我的意思是整个“AddObjects”调用的事务，以防止未定义的状态/行为。
我是不是走错了方向？如果是，您会如何做以及您的建议是什么？
编辑2：另外，我知道批量操作的交易速度很快，但它与引用的句子有些矛盾。结论是什么？
提前致谢！
I saw this sentence not only in one place:
"A transaction should be kept as short as possible to avoid concurrency issues and to enable maximum number of positive commits."
What does this really mean?
It puzzles me now because I want to use transactions for my app which in normal use will deal with inserting of hundreds of rows from many clients, concurrently.
For example, I have a service which exposes a method: AddObjects(List<Objects>) and of course these object contain other nested different objects.
I was thinking to start a transaction for each call from the client performing the appropriate actions (bunch of insert/update/delete for each object with their nested objects). EDIT1: I meant a transaction for entire "AddObjects" call in order to prevent undefined states/behaviour.
Am I going in the wrong direction? If yes, how would you do that and what are your recommendations?
EDIT2: Also, I understood that transactions are fast for bulk oeprations, but it contradicts somehow with the quoted sentence. What is the conclusion?
Thanks in advance!
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
事务必须涵盖业务特定的工作单元。它与通用“对象”无关，它必须始终以领域特定术语表示：“帐户 X 的借方和帐户 Y 的贷方必须在交易中”、“库存项目的减去和销售必须在交易中” '等等等等。所有必须一起成功或一起失败的事情都必须在一个事务中。如果您走的是“将对象添加到列表是一个事务”的抽象路径，那么是的，您走错了路。由对象保存触发的所有插入/更新/删除都在事务中这一事实并不是目的，而是副作用。正确的语义应该是“对象 X 的更新和对象 Y 的更新必须在事务中”。即使是更新单个“对象”的退化情况，仍然应该根据领域特定术语来考虑。
A transaction has to cover a business specific unit of work. It has nothing to do with generic 'objects', it must always be expressed in domain specific terms: 'debit of account X and credit of account Y must be in a transaction', 'subtract of inventory item and sale must be in a transaction' etc etc. Everything that must either succeed together or fail together must be in a transaction. If you are down an abstract path of 'adding objects to a list is a transaction' then yes, you are on a wrong path. The fact that all inserts/updates/deletes triggered by a an object save are in a transaction is not a purpose, but a side effect. The correct semantics should be 'update of object X and update of object Y must be in a transaction'. Even a degenerate case of a single 'object' being updated should still be regarded in terms of domain specific terms.
该建议最好理解为“不允许用户在事务中进行交互”。如果您需要在事务期间询问用户，请回滚，询问并再次运行。
除此之外，只要需要确保原子性，请务必使用事务。
它们可能导致“并发问题”，这并不是事务的问题，而是数据库可能需要更多的考虑、一组更好的索引或更标准化的数据访问顺序。
That recommendation is best understood as Do not allow user interaction in a transaction. If you need to ask the user during a transaction, roll back, ask and run again.
Other than that, do use transaction whenever you need to ensure atomicity.
It is not a transactions' problem that they may cause "concurrency issues", it is the fact that the database might need some more thought, a better set of indices or a more standardized data access order.
“事务应尽可能短，以避免并发问题并实现最大数量的积极提交。”
事务保持打开状态的时间越长，它就越有可能锁定其他事务所需的资源。这种阻塞将导致其他并发事务等待资源（或失败，具体取决于设计）。
Sql Server 通常设置为自动提交模式。这意味着每个 sql 语句都是一个不同的事务。很多时候您想要使用多语句事务，以便可以提交或回滚多个更新。更新花费的时间越长，其他事务发生冲突的可能性就越大。
"A transaction should be kept as short as possible to avoid concurrency issues and to enable maximum number of positive commits."
The longer a transaction is kept open the more likely it will lock resources that are needed by other transactions. This blocking will cause other concurrent transactions to wait for the resources (or fail depending on the design).
Sql Server is usually setup in Auto Commit mode. This means that every sql statement is a distinct transaction. Many times you want to use a multi-statement transaction so you can commit or rollback multiple updates. The longer the updates take, the more likely other transactions will conflict.