Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
    
In my experience, it is so very much cheaper and easier to give every developer their own sandbox database to work with. –  AlexKuznetsov Jun 12 '12 at 15:24
    
@AlexKuznetsov: so you say it's essentially a bad idea to do DDL and queries concurrently in SQL Server? (The scenario not only happens for developers so giving each a sandbox does not entirely solve the problem) –  a_horse_with_no_name Jun 12 '12 at 15:27
    
Nope - concurrent DDL from multiple connections is probably not the best idea. DDL from only one connection and DML from other might be quite all right. –  AlexKuznetsov Jun 12 '12 at 16:07
    
@AlexKuznetsov: even with 2012? I thought those "single user mode" times were long gone. Well I guess I will have to live with it then. Doesn't make SQL Server look good though. –  a_horse_with_no_name Jun 12 '12 at 16:23
    
I don't mean technical limitations - just developers are much more productive when they do not step on each other's toes. –  AlexKuznetsov Jun 12 '12 at 16:52
show 3 more comments

2 Answers

up vote 6 down vote accepted

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.

share|improve this answer
    
Thanks. Not one the answer I was looking for though. I can't believe SQL Server's transaction handling is that bad... –  a_horse_with_no_name Jun 12 '12 at 19:44
3  
@a_horse_with_no_name - I've never really found it a problem. TBH even if snapshot isolation did work in this case having developers that forget to commit transactions indefinitely would likely cause problems in preventing version store cleanup and running out of space in tempdb anyway. –  Martin Smith Jun 12 '12 at 19:50
3  
+1 - This is a behavioral issue not a technical issue. –  JNK Jun 12 '12 at 19:56
    
It's just that I have never worked with a DB before that would block a (read-only) SELECT just because of inserts (or updates) to a table. This is very surprising at least. –  a_horse_with_no_name Jun 12 '12 at 20:25
add comment

An idea: schedule a job every 15 seconds to look for idle uncommited DDLs and terminate sessions and rool them back.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.