防止在事务中锁定数据库

I have query for updating and inserting large amount of data:

我有查询更新和插入大量数据:

update [TableA]
set ColumnA1 = 'ValueA1'

DECLARE @MyCursor CURSOR;
DECLARE @MyField char(16);
BEGIN
    SET @MyCursor = CURSOR FOR
    select  ColumnA1 from [TableA]
        Where ColumnA2 = ValueA2  

    OPEN @MyCursor 
    FETCH NEXT FROM @MyCursor 
    INTO @MyField

    WHILE @@FETCH_STATUS = 0
    BEGIN

      FETCH NEXT FROM @MyCursor 
      INTO @MyField 

      Insert Into TableB(ColumnB1, ColumnB2, ColumnB3, ColumnB4, ColumnB5, ColumnB6)
      Values(@MyField, ValueB1, ValueB2, ValueB3, ValueB4, ValueB5)
    END; 

    CLOSE @MyCursor ;
    DEALLOCATE @MyCursor;
END;

As you notice first I update TableA and then I use updated TableA records to insert some logs information to TableB. I want do whole sql command in transaction manner. I scare that using begin transaction locks entire sql server database. Already I use Entity Framework and I didn't know how it handles this sort of commands without locking sql server.

正如您首先注意到的那样,我更新了TableA,然后使用更新的TableA记录将一些日志信息插入到TableB中。我想以事务方式执行整个sql命令。我吓到使用begin transaction锁定整个sql server数据库。我已经使用Entity Framework了,我不知道如何在不锁定sql server的情况下处理这种命令。

1 个解决方案

#1

As per Hamlets comment, there is no "whole database" lock. The closest would probably be to put the database into single user mode.

根据Hamlets评论,没有“整个数据库”锁定。最接近的可能是将数据库置于单用户模式。

However, holding extensive (exclusive) locks on rows or tables on highly contended data will impair the performance of other queries concurrently attempting to read committed data, or other writers.

但是,在高度竞争的数据上对行或表保持广泛(独占)锁定将损害同时尝试读取已提交数据或其他编写器的其他查询的性能。

e.g. Since the update below potentially changes all rows, it will almost certainly TABLOCK on TableA, so if you wrap your entire posted SQL in a transaction, TableA will also be locked for the duration of the cursor.

例如由于下面的更新可能会更改所有行,因此几乎可以肯定TableA上的TABLOCK,因此如果将整个发布的SQL包装在事务中,TableA也将在游标持续时间内被锁定。

UPDATE [TableA]
SET ColumnA1 = 'ValueA1';

It appears that the cursor itself is unnecessary and undesirable - wherever possible, replace RBAR approaches with set based approaches. The below insert should be equivalent:

看起来光标本身是不必要的并且是不可取的 - 只要有可能,用基于集合的方法替换RBAR方法。以下插入应该是等效的:

INSERT Into TableB(ColumnB1, ColumnB2, ColumnB3, ColumnB4, ColumnB5, ColumnB6)
SELECT ColumnA1, ValueB1, ValueB2, ValueB3, ValueB4, ValueB5
FROM [TableA]
WHERE ColumnA2 = ValueA2;

To answer your final question Entity Framework wraps the .SaveChanges() call into a transaction by default - up until this point, all changes made to the DbContext are tracked in memory only.

为了回答您的最终问题,实体框架默认将.SaveChanges()调用包装到事务中 - 直到此时,对DbContext所做的所有更改仅在内存中进行跟踪。

IMO, ORMS like EntityFramework aren't ideal for large bulk / batch operations such as the sql you've presented here, given that you have little control over the resultant Sql.

IMO,ORMS之类的EntityFramework对于大型批量/批处理操作(例如您在此处提供的sql)并不理想,因为您几乎无法控制生成的Sql。

If you are going to move this bulk job into .Net, can I suggest

如果你打算将这个批量工作转移到.Net,我可以建议吗?

Your mass update can be done via a basic SqlCommand

您的批量更新可以通过基本的SqlCommand完成

Bulk inserts can be done efficiently using SqlBulkCopy

可以使用SqlBulkCopy高效地完成批量插入

You'll be able to control a transaction over the above with a shared SqlConnection (SqlBulkCopy has a constructor taking a SqlConnection).

您将能够使用共享的SqlConnection控制上面的事务(SqlBulkCopy有一个构造函数采用SqlConnection)。

As an aside, if your requirement didn't require that you do the entire sequence of operations under a transaction, you could consider batching your updates + inserts into batches of < 5000 per commit, to avoid lock escalation issues.

顺便说一句,如果您的要求不要求您在事务下执行整个操作序列,则可以考虑将更新+插入批处理为每次提交<5000次批处理,以避免锁定升级问题。

#1