3

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.

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

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

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.

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.

What is going on here?

kspearrin
  • 10,238
  • 9
  • 53
  • 82
  • Why are you raising an error inside your loop? Wouldn't a print statement be a bit simpler? – Sean Lange Mar 01 '18 at 20:03
  • @SeanLange `RAISERROR` prints messages as they happen, vs `PRINT` buffering the messages. See https://stackoverflow.com/questions/1287878/t-sql-output-message-during-execution-in-ssms – kspearrin Mar 01 '18 at 20:06
  • Interesting. I have never needed to view messages as they happen like this. Good to know. – Sean Lange Mar 01 '18 at 20:08

1 Answers1

2

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.

Alberto Morillo
  • 13,893
  • 2
  • 24
  • 30
  • 1
    So I lied. There was a previous iteration of my batch update script that had a transaction involved with it. I think what's been happening here is that when I cancel the query in SSMS during my testing I am leaving open transactions which eventually lock the table. I will do more testing to confirm. Thanks for the info! – kspearrin Mar 02 '18 at 00:58
  • 1
    I just finished testing the scenario again and that was it. I committed all the open transactions and the table is unlocked. – kspearrin Mar 02 '18 at 03:35
  • Wonderful! Nice job. – Alberto Morillo Mar 02 '18 at 03:37