Adapter trying to use 'read commited snapshot' isolation level

[beirigo]

I'm using this statement to change my database isolation level: ALTER DATABASE database_name SET READ_COMMITTED_SNAPSHOT ON ALTER DATABASE database_name SET ALLOW_SNAPSHOT_ISOLATION ON

The application is retuninrg the following error:

TinyTds::Error: Incorrect syntax near 'snapshot'.: SET TRANSACTION ISOLATION LEVEL read committed snapshot Why is it trying to use the database isolation level in the transaction isolation level? What am i missing?

[metaskills]

Not a lot to go on here. Let me give you some more context and let's see if we can get to where the issue lies. First would be to understand where you are issuing that statement? Is this in a migration or are you setting this when a client connects as described in the README under the "Configure Connection..." section?

Also, did you know there is a run_with_isolation_level method in the adapter.

https://github.com/rails-sqlserver/activerecord-sqlserver-adapter/blob/master/lib/active_record/connection_adapters/sqlserver/database_statements.rb#L175

This method takes a block and allows you to change the isolation level of the client connection for the duration of the block. Great for controllers that need not allow other locks to interfere for read/search actions.

[beirigo]

I ran the SET READ_COMMITTED_SNAPSHOT ON directly on the database, I'll try to use run_with_isolation_level with snapshot on my application controller. I just would like to know why the driver is trying to force the isolation level i set directly on the database. Thanks for the help!

[metaskills]

The adapter or raw connection is not doing any forcing or setting of an isolation level.

[beirigo]

Maybe tiny_tds? I'll look into it.

[metaskills]

Nope, it's gonna have to be in your code somewhere. Perhaps a gem/plugin/etc? Maybe someone was using #run_with_isolation_level already too. But I guarantee, it is in your code somewhere.

[metaskills]

Closing this issue. Please let us know if you resolved it due to code on your end. Else, I can re-open if you know this is an issue with the adapter.

[PunkMaldito]

I was working with marcosbeirigo in this issue and we solved the problem. But actually, I found something that may be a bug. When you use the command dbcc useroptions on a SQL Server 2005 and the isolation level of the table is READ COMMITTED SNAPSHOT, the server will return 'snapshot' in the isolation level value. But it doesn't works the same with the SQL Server 2008. In the SQL Server 2008, after running dbcc useroptions it will return 'read committed snapshot' in the isolation level value, what results in this syntax error. In our case, we have to set the isolation level of the table as READ UNCOMMITTED for other reasons and that solves the problem, but I guess that it's a current issue, right?

[metaskills]

and the isolation level of the table is READ COMMITTED SNAPSHOT

Can you define what this means? I had no idea there was a way to set an isolation level on a specific table. I thought it was just at the connection level using our #run_with_isolation_level method which uses SQL Server's SET TRANSACTION ISOLATION LEVEL.

Wait, your talking about when you set the database using SET READ_COMMITTED_SNAPSHOT ON right? OK, so what is the issue then and in the adapter? Are you using out #user_options method which uses dbcc useroptions behind the scenes? If that is not returning something that makes sense, perhaps use our #user_options_isolation_level method which uses [sys].[dm_exec_sessions], see here.

[PunkMaldito]

Sorry, I wrote table instead of database. My bad...

But if am I not using SQL Server Azure? The #user_options_isolation_level method will return the result from #user_options method, right?

I'm using the #run_with_isolation_level method and a stand-alone instance of SQL Server 2008.

[metaskills]

Yea, Azure will default back to our #user_options implementation. If there is a bug and dbcc useroptions is not the best source for specific SQL Server versions, I am willing to take patches to strengthen it.

[PunkMaldito]

Thanks a lot, man! This project is so helpfull! =)