为什么我的Azure SQL数据库表被永久锁定? [英] Why Is My Azure SQL Database Table Permanently Locked?

问题描述

我有一个隔离的Azure SQL测试数据库，除了通过SSMS和开发Web应用程序实例的开发计算机之外，没有活动连接.我是唯一使用此数据库的人.

I have an isolated Azure SQL test database that has no active connections except my development machine through SSMS and a development web application instance. I am the only one using this database.

我正在对〜1M条记录的表进行一些测试，我们需要对几乎所有〜1M条记录中的数据进行大的UPDATE操作.

I am running some tests on a table of ~1M records where we need to do a large UPDATE to data in nearly all of the ~1M records.

DECLARE @BatchSize INT = 1000

WHILE @BatchSize > 0
BEGIN
    UPDATE TOP (@BatchSize)
        [MyTable]
    SET
        [Data] = [Data] + ' a change'
    WHERE
        [Data] IS NOT NULL

    SET @BatchSize = @@ROWCOUNT
    RAISERROR('Updated %d records', 0, 1, @BatchSize) WITH NOWAIT
END

此查询工作正常，每隔几秒钟一次即可看到我的数据正在更新1000条记录.

This query works fine, and I can see my data being updated 1000 records at a time every few seconds.

在MyTable上执行附加的INSERT/UPDATE/DELETE命令似乎在一定程度上受到此批处理查询运行的影响，但是这些操作在运行时会在几秒钟内执行.我认为这是因为对MyTable进行了锁定，而我的其他命令将在批处理查询的锁定/循环迭代之间执行.

Performing additional INSERT/UPDATE/DELETE commands on MyTable seem to be somewhat affected by this batch query running, but these operations do execute within a few seconds when ran. I assume this is because locks are being taken on MyTable and my other commands will execute in between the batch query's locks/looping iterations.

所有这种行为都是预期的.

This behavior is all expected.

但是，在批处理查询运行时，我经常注意到MyTable上的其他INSERT/UPDATE/DELETE命令将不再执行.他们总是超时/永远不会完成.我假设MyTable上已发生某种类型的锁定，但似乎从未释放过该锁定.此外，即使我取消了长时间运行的更新批处理查询，我仍然无法再在MyTable上运行任何INSERT/UPDATE/DELETE命令.即使在数据库坐了10-15分钟后仍然没有任何反应，我也无法在MyTable上执行写命令.我发现从发生的一切中释放"数据库的唯一方法是将其向上和向下扩展到新的定价层.我认为此定价层更改是在回收/重新启动实例或其他东西.

However, every so often while the batch query is running I notice that additional INSERT/UPDATE/DELETE commands on MyTable will no longer execute. They always time out/never finish. I assume some type of lock has occurred on MyTable, but it seems that the lock is never being released. Further, even if I cancel the long-running update batch query I can still no longer run any INSERT/UPDATE/DELETE commands on MyTable. Even after 10-15 minutes of the database sitting stale with nothing happening on it anymore I cannot execute write commands on MyTable. The only way I have found to "free up" the database from whatever is occurring is to scale it up and down to a new pricing tier. I assume that this pricing tier change is recycling/rebooting the instance or something.

今天我在测试中多次重现了这种行为.

I have reproduced this behavior multiple times during my testing today.

这是怎么回事?

推荐答案

向上/向下扩展层回滚所有打开的事务并断开服务器登录.

Scaling up/down the tier rollback all open transactions and disconnect server logins.

关于您所看到的似乎是锁升级.尝试使用锁定提示.

About what you are seeing it seems is lock escalation. Try to serialize access to the database using sp_getapplock. You can also try lock hints.

这篇关于为什么我的Azure SQL数据库表被永久锁定?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！