We are trying to build a High-Volume Orders Record System. There are three primary tables: 1. Orders 2. OrderDetails 3. OrderShipment
我们正在尝试建立一个大批量订单记录系统。有三个主要表:1。订单2.订单详细信息3.订单发货
The Shipment table contains n record per order and any record shipment entry can be changed before the Customer accepts th order, after which it is frozen. (A business requirement)
Shipment表包含每个订单的n条记录,并且在客户接受订单之前可以更改任何记录货件条目,之后它将被冻结。 (业务要求)
Although this may not happen in real world scenarios... during our load tests, we are getting System.Data.Linq.ChangeConflictException exceptions. Wrapping up the submit inside a transacion is not helping either. Can't we force LINQ to get a lock on the row for the entire duration of the update operation?
虽然在现实世界的场景中可能不会发生这种情况......在我们的加载测试期间,我们得到了System.Data.Linq.ChangeConflictException异常。在交易中包含提交也无济于事。我们不能强制LINQ在更新操作的整个持续时间内锁定行吗?
Is there any other way to get over this?
有没有其他方法来克服这个?
3 个解决方案
#1
7
If you are having genuine issues with concurrent updates on the same data, then you might consider performing the entire operation in a transaction - i.e. getting the data and committing it. As long as you treat the get/update/commit as a short-lived, atomic operation (i.e. you don't pause for user-input in the middle) it should be OK.
如果您对同一数据的并发更新存在真正的问题,那么您可以考虑在事务中执行整个操作 - 即获取数据并提交数据。只要你将get / update / commit视为一个短暂的原子操作(即你不在中间暂停用户输入),它应该没问题。
In particular, with a serializable isolation level, nobody can update data that you have a read lock on (i.e. anything you have queried). The only problem is that this might lead to deadlock scenarios if different queries are reading data in different orders. AFAIK, there is no way to get LINQ-to-SQL to issue the (UPDLOCK) hint, which is a shame.
特别是,对于可序列化的隔离级别,没有人可以更新您具有读锁定的数据(即您查询的任何内容)。唯一的问题是,如果不同的查询以不同的顺序读取数据,这可能会导致死锁情况。 AFAIK,没有办法让LINQ-to-SQL发出(UPDLOCK)提示,这是一种耻辱。
Either a TransactionScope or a SqlTransaction would do, as long as they are set as serializable isolation (which is the default for TransactionScope).
只要将TransactionScope或SqlTransaction设置为可序列化隔离(这是TransactionScope的默认设置),它就可以执行。
#2
1
you may want to look into Entity Framework which executes everything as a transaction. Here are two podcasts which can also be interesting about Entity Framework.
您可能希望查看实体框架,它将所有内容作为事务执行。这里有两个播客,对于Entity Framework也很有趣。
DNRTV - 第1部分 - 第2部分
#3
1
For this kind of situations, i.e. where more than one user may want to make changes to the same record/customer/order/whatever it is better to build "locking" into the application logic rather than use database locks.
对于这种情况,即不止一个用户可能想要对同一记录/客户/订单进行更改/无论如何更好地构建“锁定”到应用程序逻辑而不是使用数据库锁。
Using DB locks to solve logical locking of data is going to present you with a heap of new issues. A better solution is to have columns and/or tables where you can indicate that an order/customer/etc is being edited [by a user], until when it is locked etc. Query that table (or columns) to check if the customer/order/thing is available for editing before allowing another user to edit it.
使用数据库锁来解决数据的逻辑锁定会给你带来一堆新问题。更好的解决方案是使用列和/或表格,您可以指示[由用户]正在编辑订单/客户/等,直到它被锁定等。查询该表(或列)以检查客户是否/ order / thing可供编辑,然后允许其他用户编辑它。
See: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=3984968&SiteID=1
#1
7
If you are having genuine issues with concurrent updates on the same data, then you might consider performing the entire operation in a transaction - i.e. getting the data and committing it. As long as you treat the get/update/commit as a short-lived, atomic operation (i.e. you don't pause for user-input in the middle) it should be OK.
如果您对同一数据的并发更新存在真正的问题,那么您可以考虑在事务中执行整个操作 - 即获取数据并提交数据。只要你将get / update / commit视为一个短暂的原子操作(即你不在中间暂停用户输入),它应该没问题。
In particular, with a serializable isolation level, nobody can update data that you have a read lock on (i.e. anything you have queried). The only problem is that this might lead to deadlock scenarios if different queries are reading data in different orders. AFAIK, there is no way to get LINQ-to-SQL to issue the (UPDLOCK) hint, which is a shame.
特别是,对于可序列化的隔离级别,没有人可以更新您具有读锁定的数据(即您查询的任何内容)。唯一的问题是,如果不同的查询以不同的顺序读取数据,这可能会导致死锁情况。 AFAIK,没有办法让LINQ-to-SQL发出(UPDLOCK)提示,这是一种耻辱。
Either a TransactionScope or a SqlTransaction would do, as long as they are set as serializable isolation (which is the default for TransactionScope).
只要将TransactionScope或SqlTransaction设置为可序列化隔离(这是TransactionScope的默认设置),它就可以执行。
#2
1
you may want to look into Entity Framework which executes everything as a transaction. Here are two podcasts which can also be interesting about Entity Framework.
您可能希望查看实体框架,它将所有内容作为事务执行。这里有两个播客,对于Entity Framework也很有趣。
DNRTV - 第1部分 - 第2部分
#3
1
For this kind of situations, i.e. where more than one user may want to make changes to the same record/customer/order/whatever it is better to build "locking" into the application logic rather than use database locks.
对于这种情况,即不止一个用户可能想要对同一记录/客户/订单进行更改/无论如何更好地构建“锁定”到应用程序逻辑而不是使用数据库锁。
Using DB locks to solve logical locking of data is going to present you with a heap of new issues. A better solution is to have columns and/or tables where you can indicate that an order/customer/etc is being edited [by a user], until when it is locked etc. Query that table (or columns) to check if the customer/order/thing is available for editing before allowing another user to edit it.
使用数据库锁来解决数据的逻辑锁定会给你带来一堆新问题。更好的解决方案是使用列和/或表格,您可以指示[由用户]正在编辑订单/客户/等,直到它被锁定等。查询该表(或列)以检查客户是否/ order / thing可供编辑,然后允许其他用户编辑它。
See: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=3984968&SiteID=1