Concurrency Challenges Around Schema Changes

  • Comments posted to this topic are about the item Concurrency Challenges Around Schema Changes

  • This is an interesting discussion in the context of database object deployments.

    SQL Server will take schema stability locks when running a SELECT query (or a DML, DDL, etc operation) which will block another session's attempts to DROP or ALTER a table. That's the behaviour most of us expect and want.

    Often times what's blocking schema changes on a table off-hours isn't actually user or application queries but rather some non-critical polling process that's designed to handle interruptions. So, depending on the circumstances, when I'm deploying a scheduled change to the database, I will temporarily set the database to RESTRICTED mode WITH ROLLBACK AFTER 60 SECONDS, so any active sessions are given time to compete, and new non-sysadmin sessions are blocked until the deployment has finished. Even if new queries were allowed to run during the deployment window, they would probably get blocked anyhow and possibly end up deadlocking and aborting the deployment.

    "Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply