这个简单的代码产生死锁。这个简单例子包括 [英] This simple code produces deadlock. Simple Example Program included

问题描述

代码，注意值的顺序是不同的。

Code, notice the order of the values is different. So it alternates between locking rows:

static void Main( string[] args )
        {
            List<int> list = new List<int>();

            for(int i = 0; i < 1000; i++ )
                list.Add( i );

            Parallel.ForEach( list, i =>
            {
                using( NamePressDataContext db = new NamePressDataContext() )
                {
                    db.ExecuteCommand( @"update EBayDescriptionsCategories set CategoryId = Ids.CategoryId from EBayDescriptionsCategories 
                        join (values (7276, 20870),(240, 20870)) as Ids(Id,CategoryId) on Ids.Id = EBayDescriptionsCategories.Id" );

                    db.ExecuteCommand( @"update EBayDescriptionsCategories set CategoryId = Ids.CategoryId from EBayDescriptionsCategories 
                        join (values (240, 20870),(7276, 20870)) as Ids(Id,CategoryId) on Ids.Id = EBayDescriptionsCategories.Id" );
                }

            } ); 
        }

表DEF：

Table def:

CREATE TABLE [dbo].[EDescriptionsCategories](
    [CategoryId] [int] NOT NULL,
    [Id] [int] NOT NULL,
 CONSTRAINT [PK_EDescriptionsCategories] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)

例外：

Transaction (Process ID 80) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

中的代码只能使用WITH（TABLOCK）提示。它是可能没有锁定整个表只是为了更新只是平行的两行？

The code works only with WITH (TABLOCK) hint. Is it possible not to lock the whole table just to update just those 2 rows in parallel?

推荐答案

您两个语句收购不同的行锁秩序。这是死锁的经典案例。您可以通过确保所采取的锁的顺序总是在一些全球秩序（例如，通过ID排序）。你或许应该合并两个更新修复这个语句成一个，并把它发送到SQL Server之前排序的客户端ID列表。对于许多典型的更新计划在实际工作正常（不保证，虽然）。

Your two statements acquire row locks in different order. That's a classic case for deadlocks. You can fix this by ensuring that the order of locks taken is always in some global order (for example, ordered by ID). You should probably coalesce the two UPDATE statements into one and sort the list of IDs on the client before sending it to SQL Server. For many typical UPDATE plans this actually works fine (not guaranteed, though).

或者，您可以添加重试逻辑如果你检测到死锁（ SqlException.Number == 1205 ）。这是更优雅，因为它不需要较深的代码的改变。但僵局会影响性能所以只能低僵局率做到这一点。

Or, you add retry logic in case you detect a deadlock (SqlException.Number == 1205). This is more elegant because it requires no deeper code changes. But deadlocks have performance implications so only do this for low deadlock rates.

如果您的并行处理产生大量的更新，你可以插入所有这些更新到一个临时表（可同时进行），当你做了你执行一个大的更新那份所有单个更新记录到主表。你只需要改变你的样本查询联接源。

If your parallel processing generates lots of updates, you could INSERT all those updates into a temp table (which can be done concurrently) and when you are done you execute one big UPDATE that copies all the individual update records to the main table. You just change the join source in your sample queries.

这篇关于这个简单的代码产生死锁。这个简单例子包括的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！