MS SQL Server - Configure readonly connection

[chkaufmann]

My server application uses MS SQL Server 2012 database. I have a website that causes many readonly connections to read from the database.
Then I run a background task from time to time for updates on some tables. This causes a lot of "timeout error" in my readonly tasks.
My question is, what are the best settings for my readonly connection (e.g. isolation level) in order to avoid timeouts due update locks. My data is not critical regarding transactions, so "dirty reads" wouldn't heart, but the timeouts do.
I already increased the timeout for requests to 30 seconds, but it's not enough. And I have no easy solution to replace my update commands with insert commands so I don't get locks.

Any hints for this problem would be helpfull.

cu Christian

[azyk]

We can't reproduce the issue according to your description. Please try to compose a small test sample reproducing the issue and send it to andreyz*devart*com . Include scripts for creating the test tables.

[chkaufmann]

My problem is not an error in UniDAC. I think, this describes my concurrency problem:
http://stackoverflow.com/questions/1260 ... ct-queries

Now instead of using NOLOCK in each statment I wonder if I could set the connection parameters in a way to avoid locks in select statements. Right now, each HTTP requests in my server application opens a connection and reads from there (no update, no insert, no delete). But because another thread is doing updates in the background, these selects can timeout.

cu Christian

[chkaufmann]

I think I should set my SQL Server connection to Isolationlevel "Snapshot".

Now I'm not 100% sure, if this is enough:

Code: Select all

FUniConnection.DefaultTransaction.IsolationLevel := ilSnapshot;

Because when I look at the code, the following method is never called:

TMSSQLConnection.SetIsolationLevel

and it seems that SQL Server connection does not use DefaultTransaction.

Or do I miss something? UniDac code is quite hard to understand since it goes through many levels of classes.

cu Christian

[azyk]

The provided code is enough to set transaction isolation level "Snapshot". You can make this sure using SQL Server Profiler. On execution of the TUniConnection.Connect method, the following SQL query will be executed for this connection instance:

Code: Select all

SET TRANSACTION ISOLATION LEVEL SNAPSHOT

In the described case, UniDAC uses the TOLEDBConnector.Connect method instead of TMSSQLConnection.SetIsolationLevel to set isolation level for the SQL Server provider.