Databases
StackOverflow2013
Postsdbo
POSQL Azure - One session locking entire DB for Update and Insert
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POSQL Azure - One session locking entire DB for Update and Insert
    primarykey
    data
    text
    <p>SQL Azure issue.</p> <p>I've got an issue that manifests as the following exception on our (asp.net) site:</p> <blockquote> <p>Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. The statement has been terminated.</p> </blockquote> <p>It also results in update and insert statements never completing in SMSS. There aren't any X or IX locks present when querying: <code>sys.dm_tran_locks</code> and there are no transactions when querying <code>sys.dm_tran_active_transactions</code> or <code>sys.dm_tran_database_transactions</code>.</p> <p>The problem is present for every table in the database but other databases on the same instance don't cause the problem. The duration of the issue can be anywhere from 2 minutes to 2 hours and doesn't happen at any specific times of day.</p> <p>The database is not full.</p> <p>At one point this issue didn't resolve itself but I was able to resolve the issue by querying <code>sys.dm_exec_connections</code> finding the longest running session, and then killing it. The odd thing is, that the connection was 15 minutes old, but the lock issue had been present for over 3 hours.</p> <p>Is there anything else I can check?</p> <p><strong>EDIT</strong></p> <p>As per Paul's answer below. I'd actually tracked down the problem before he answered. I will post the steps I used to figure this out below, in case they help anyone else.</p> <p>The following queries were run when a "timeout period" was present.</p> <pre><code>select * from sys.dm_exec_requests </code></pre> <p><img src="https://i.stack.imgur.com/wzWlM.png" alt="Request Stats"></p> <p>As we can see, all the WAIT requests are waiting on session 1021 which is the replication request! The <code>TM Request</code> indicates a DTC transaction and we don't use distributed transactions. You can also see the wait_type of <code>SE_REPL_COMMIT_ACK</code> which again implicates replication.</p> <pre><code>select * from sys.dm_tran_locks </code></pre> <p><img src="https://i.stack.imgur.com/AyBo2.png" alt="enter image description here"></p> <p>Again waiting on session 1021</p> <pre><code>SELECT * FROM sys.dm_db_wait_stats ORDER BY wait_time_ms desc </code></pre> <p><img src="https://i.stack.imgur.com/QZnKl.png" alt="enter image description here"></p> <p>And yes, <code>SE_REPL_CATCHUP_THROTTLE</code> has a total wait time of 8094034 ms, that is 134.9minutes!!!</p> <p>Also see the following forum for details on this issue. <a href="http://social.technet.microsoft.com/Forums/en-US/ssdsgetstarted/thread/c3003a28-8beb-4860-85b2-03cf6d0312a8" rel="noreferrer">http://social.technet.microsoft.com/Forums/en-US/ssdsgetstarted/thread/c3003a28-8beb-4860-85b2-03cf6d0312a8</a></p> <blockquote> <p>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): </p> <p>Question: Have there been changes to these soft throttling limits in the last three weeks ie since my problems started? </p> <p>Answer: No, there has not. </p> <p>Question: Are there ways we can prevent or be warned we are approaching a limit? </p> <p>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:</p> <ol> <li><p>Create a copy of your db and use that and hope the db is placed on another server with less load. </p></li> <li><p>Contact Windows Azure Support and inform the about the problem and let them do Option 1 for you</p></li> </ol> </blockquote>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USSam Shiles
    1. USSam Shiles
    plurals
    1. This table or related slice is empty.
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POSQL Azure - One session locking entire DB for Update and Insert
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POSQL Azure - One session locking entire DB for Update and Insert
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POSQL Azure - One session locking entire DB for Update and Insert
      1. USDenis Pitcher
      1. VTFavorite
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload