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_tablestatement 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).