DO.Net support staff,

We are using DO.Net v4.4 with a schema residing on MS SQL Server 2008. We are attempting to leverage the built in hints such as Select * from Authors with (NOLOCK). After studying the .Lock methods I assumed that using a Lock(Shared, Default) would have given me the (NOLOCK) hint on my SQL, instead I see a With (ROWLOCK). In short, we are attempting to leverage a dirty read.

How can I leverage DO.Net to give me a dirty read on SQL Server?

What is the proper usage pattern to accomplish this?

Thanks for your help.

asked Jan 31 '12 at 00:03

kcrismon's gravatar image

kcrismon
5222

I just posted an answer describing much better solution to a typical problem with locking on SQL Server. But if you really need dirty reads (your case can be different), please notify us here, we'll try to help.

(Jan 31 '12 at 04:27) Alex Yakunin Alex%20Yakunin's gravatar image

One Answer:

Have you tried to use snapshot isolation on SQL Server 2008? MVCC is much safer option in comparison to any of these hints.

All you need to do is:

1) Enable MVCC for your DB:

ALTER DATABASE "X"
SET ALLOW_SNAPSHOT_ISOLATION ON;

2) Use SNAPSHOT isolation level by default for all of your sessions. Use a code like this to achieve this:

    public static Domain Build()
    {
        var config = LoadDomainConfiguration(); // Our own method
        config.Types.Register(typeof(ContentInitializationStatusRecord).Assembly);

        var defaultSessionConfig = config.Sessions.Default;
        if (defaultSessionConfig == null) {
            defaultSessionConfig = new SessionConfiguration(WellKnown.Sessions.Default);
            config.Sessions.Add(defaultSessionConfig);
        }

        // !!! The next line does all you need
        defaultSessionConfig.DefaultIsolationLevel = IsolationLevel.Snapshot;
        defaultSessionConfig.DefaultCommandTimeout = 60; // 1 min.

        var domain = Domain.Build(config);
        return domain;
    }

    private static DomainConfiguration LoadDomainConfiguration()
    {
        return (
            from name in new[] {
                AppSettings.ConfigrationSelectorVariableName==null 
                    ? null 
                    : Environment.GetEnvironmentVariable(AppSettings.ConfigrationSelectorVariableName),
                Environment.GetEnvironmentVariable("ComputerName"),
                "Default"
            }
            where !name.IsNullOrEmpty()
            let config = TryGetDomainConfiguration(name)
            where config != null
            select config
            ).First();
    }

    private static DomainConfiguration TryGetDomainConfiguration(string domainConfigurationName)
    {
        try {
            return DomainConfiguration.Load(domainConfigurationName);
        }
        catch (InvalidOperationException) {
            return null;
        }
    }

answered Jan 31 '12 at 04:21

Alex%20Yakunin's gravatar image

Alex Yakunin
29714412

I'd suggest read_committed_snapshot isolation, but it seems we have already had this discussion :)

(Jan 31 '12 at 05:23) xumix xumix's gravatar image
Your answer
Please start posting your answer anonymously - your answer will be saved within the current session and published after you log in or create a new account. Please try to give a substantial answer, for discussions, please use comments and please do remember to vote (after you log in)!
toggle preview

Subscription:

Once you sign in you will be able to subscribe for any updates here

Tags:

×1
×1

Asked: Jan 31 '12 at 00:03

Seen: 3,519 times

Last updated: Jan 31 '12 at 05:23

Related questions

powered by OSQA