Sql-server – SQL Server (2012) non-blocking DDL

concurrencyddlsql-server-2012

I'm not very experienced with SQL Server, so maybe I'm missing something

My situation is as follows:

  • Session 1 runs a CREATE TABLE (or other CREATE statements) with autocommit off and the CREATE is not being comitted.
  • Session 2 runs a sp_table statement but hangs as long as session 1 is not committing the DDL

The scenario where this happens is developers working on the database. Some of them browsing the tables, some of them doing DDL. If one user forgets to commit the DDL all other sessions that want to list the tables are blocked. Note that the sp_tables is issued e.g. by the SQL client (through the JDBC driver API), so it's not something that can be changed.

The database I'm working with has snapshot_isolation enabled and the isolation level is set to read committed (SET ALLOW_SNAPSHOT_ISOLATION ON and SET READ_COMMITTED_SNAPSHOT ON)

My assumption was that these settings should make SQL Server behave better with regards to locking in concurrent sessions (e.g. like PostgreSQL and Oracle where SELECTs are never blocked by any writer) – but apparently this is not the case.

So, is there any way to make SQL Server more friendly against concurrent read/write situations with regards to DDL? (apart from submitting DDL only in auto-commit mode).

Best Answer

No there's no way of configuring SQL Server to do what you want to do.

Under snapshot isolation the call to sp_tables gets blocked waiting for a shared key lock on one of the system base tables (sysschobjs) when doing a SELECT from sys.all_objects

The Using Row Versioning-based Isolation Levels topic in BOL does say:

SQL Server does not keep multiple versions of system metadata. Data definition language (DDL) statements on tables and other database objects (indexes, views, data types, stored procedures, and common language runtime functions) change metadata.

Even under TRANSACTION ISOLATION LEVEL READ UNCOMMITTED the call to sp_tables ends up blocking however, whilst the straight forward SELECT ... FROM sys.all_objects is no longer blocked the same query references the HAS_PERMS_BY_NAME function in the WHERE clause. This appears to start a system transaction (CMetadataAccessor::CMetadataAcce) at a higher isolation level and ends up getting blocked waiting for a shared key lock on sysschobjs again.

Related Question