使 SQL 语句序列原子化
我需要在存储过程原子中执行以下步骤序列。这是一个近似简化的示例：
Customesrs 表 (CustomerId,..,OrderMax)
产品表（ProductId，...）
可用产品视图（ProductId 和其他属性）
订单 (CustomerId,OrderId)
- 从 Customers 表中选择
@OrderMax - 从 AvailableProducts 视图中选择
TOP @Ordermax - 根据步骤 2
- 插入订单 的结果集更新 Products 中的一些属性插入订单表（基于步骤 2 的结果集）
- 返回/选择插入的订单
据我了解，必须有一个关于整个事物和 UPDLOCK 的事务。必须保护的是用于更新的产品表和用于插入的订单表。但是，这些行是从由这两个表构建的视图中查询的。
使该序列原子且安全地更新并插入上表的正确方法是什么？
I need to make the following sequence of steps in a sproc atomic. Here is an approximate simplified example:
Customesrs table (CustomerId,..,OrderMax)
Products table (ProductId,...)
AvailableProducts view (ProductId and other properties)
Orders (CustomerId,OrderId)
- select
@OrderMaxfrom the Customers table - select
TOP @Ordermaxfrom AvailableProducts view - update some properties in the Products based on the result set of step 2
- insert orders into Orders table (based on the result set of step 2)
- return/select orders that were inserted
As I understand, there has to be a transaction on the whole thing and UPDLOCK. What has to be secured is Products table for update and Orders table for insert. However, the rows are queried from the view that is constructed from both of these tables.
What is the right way to make this sequence atomic and secure update and insert on the above tables?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
需要将所有逻辑包装在开始事务、提交事务中。更新/插入并不真正关心数据是否来自联接，除非它以某种方式创建了无法回滚事务的情况，但创建这样的情况必须变得非常混乱。如果 3. 和 4. 有复杂的逻辑，您可能会被迫使用游标或 .NET（但您可以使用常规查询执行一些相当复杂的逻辑）。
Need to wrap all you logic in a Begin Transaction, Commit Transaction. The update / insert does not really care if the data came from a join unless it somehow created a situation where it could not roll back the transaction but it would have to get real messy to create a situation like that. If 3. and 4. have complex logic you may be forced into a cursor or .NET (but you can do some pretty complex logic with regular queries).