这个简单的代码会产生死锁.包括简单的示例程序 [英] 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" );
                }

            } ); 
        }

表定义:

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) 提示.是否可以不锁定整个表只是为了并行更新这 2 行?

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 排序)来解决此问题.您可能应该将两个 UPDATE 语句合并为一个，并在将客户端上的 ID 列表发送到 SQL Server 之前对其进行排序.对于许多典型的 UPDATE 计划，这实际上工作正常(但不能保证).

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.

如果您的并行处理产生大量更新，您可以INSERT将所有这些更新放入一个临时表中(可以同时完成)，当您完成时，您执行一个大的UPDATE 将所有单独的更新记录复制到主表.您只需更改示例查询中的连接源.

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屋！