SQL Azure – 一个会话锁定更新和插入的整个数据库

我有一个问题,在我们的(asp.net)网站上显示为以下异常：

Timeout expired. The timeout period elapsed prior to completion of
the operation or the server is not responding. The statement has been
terminated.

它还导致更新和插入语句从未在SMSS中完成.查询时不存在任何X或IX锁：sys.dm_tran_locks,而在查询sys.dm_tran_active_transactions或sys.dm_tran_database_transactions时没有事务.

数据库中的每个表都存在问题,但同一实例上的其他数据库不会导致问题.问题的持续时间可以是2分钟到2小时的任何时间,并且不会在每天的特定时间发生.

数据库不满.

有一点,这个问题没有解决,但是我能够通过查询sys.dm_exec_connections查找最长的运行会话来解决问题,然后杀死它.奇怪的是,连接时间是15分钟,但锁定问题已经存在了3个多小时.

还有什么我可以检查的吗？

编辑

按照保罗在下面的回答.在他回答之前,我实际上已经跟踪了这个问题.我会把我以前用来制作的步骤发布在下面,以防他们帮助别人.

当出现“超时时间”时,运行以下查询.

select *  from sys.dm_exec_requests

我们可以看到,所有WAIT请求正在等待会话1021,这是复制请求！ TM请求指示DTC事务,我们不使用分布式事务.您还可以看到SE_REPL_COMMIT_ACK的wait_type,这再次暗示了复制.

select * from  sys.dm_tran_locks

再次等待会话1021

SELECT * FROM sys.dm_db_wait_stats ORDER BY wait_time_ms desc

是的,SE_REPL_CATCHUP_THROTTLE的总等待时间为8094034
ms,那是134.9分钟！

有关此问题的详细信息,请参阅以下论坛.
http://social.technet.microsoft.com/Forums/en-US/ssdsgetstarted/thread/c3003a28-8beb-4860-85b2-03cf6d0312a8

I’ve been given the following answer in my communication with
Microsoft (we’ve seen this issue with 4 of our 15 databases in the EU
data center):

Question: Have there been changes to these soft
throttling limits in the last three weeks ie since my problems
started?

Answer: No,there has not.

Question: Are there ways we can
prevent or be warned we are approaching a limit?

Answer: No. The issue
may not be caused by your application but can be caused by other
tenants relying on the same physical hardware. In other words,your
application can have very little load and still run into the problem.
In other words,your own traffic may be a cause of this problem,but
it can just as well be caused by other tenants relying on the same
physical hardware. There’s no way to know beforehand that the issue
will soon occur – it can occur at any time without warning. The SQL
Azure operations team does not monitor this type of error,so they
won’t automatically try to solve the problem for you. So if you run
into it you have two opitions:

1. Create a copy of your db and use that and hope the db is placed on another server with less load.

2. Contact Windows Azure Support and inform the about the problem and let them do Option 1 for you

解决方法

当您遇到超时时,请尝试检查等待请求的等待类型：

> SE_REPL_SLOW_SECONDARY_THROTTLE
> SE_REPL_COMMIT_ACK

运行以下内容来检查当前连接的等待类型：

SELECT TOP 10 r.session_id,r.plan_handle,r.sql_handle,r.request_id,r.start_time,r.status,r.command,r.database_id,r.user_id,r.wait_type,r.wait_time,r.last_wait_type,r.wait_resource,r.total_elapsed_time,r.cpu_time,r.transaction_isolation_level,r.row_count
FROM sys.dm_exec_requests r

您还可以通过运行以下步骤检查各种历史记录：

SELECT * FROM sys.dm_db_wait_stats
ORDER BY wait_time_ms desc

如果您看到很多SE_REPL *等待类型,并且这些等待类型在您的连接上保持任何时间长度,那么基本上您已经搞砸了.
微软意识到这个问题,但是现在我们已经有一个星期的支持票打开了,而且他们还在努力工作.

当Sql Azure复制奴隶落后时,SE_REPL *等待发生.
基本上整个db在复制时挂起查询：/

所以本质上使得Sql Azure高度可用的方面正在导致数据库变得无法使用.
如果不杀死我,我会笑起讽刺意味的.

看看这个线程的细节：
http://social.technet.microsoft.com/Forums/en-US/ssdsgetstarted/thread/c3003a28-8beb-4860-85b2-03cf6d0312a8

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!