I noticed in another thread that the NOLOCK hint might be available as an instance member. Currently I am accessing the hint using the static member which causes problems when I am running more than a single instance. Is this available or can someone give me a hint for using the NOLOCK hint in a multi-connection environment?

Thanks, Dale

Hi Dale,

Could you please elaborate more about your problem? Also as far as I know that property is static, so that would be possible indirectly but that might be some extra work for you.

daelmo wrote:

Hi Dale,

Could you please elaborate more about your problem? Also as far as I know that property is static, so that would be possible indirectly but that might be some extra work for you.

Hello,

I'm attempting to access the database through LLBLGEN from within a web-service. I've completed coding and my db transaction works great when a single user is accessing the web-service. When a second user calls the web-service a second instance of my assembly is created which then creates an instance of the DataAccessAdapter and so on. During execution there are a couple of database queries that I've determined need the NOLOCK hint. So the nolock static property is set to true, the query run and then the property is reset to false. I need guarantee the value of the NOLOCK hint for each user. Since it is a static property I don't see a way to do it. When searching for an answer to this problem I saw this:

Rogelio: There is any way to set it at conections level? I do not like the idea to set it globaly, because it could affect other users doing transactions. This would be a nice feature to use when doing Select for report data.

Otis: Not at the moment as the DQE is a class with static methods. For 1.0.2005.1 this is refactored to instance classes, which could allow you to set it on a per-call basis (adapter). I'll see if I can refactor it into the instance (without breaking current code)

in this thread:
http://llblgen.com/tinyforum/Messages.aspx?ThreadID=2482

Thanks, Dale

I need guarantee the value of the NOLOCK hint for each user.

+1

We have run into situations where we would like to be able to set this at a more discrete level as well.

NOLOCK isn't something which should be encouraged, as it leads to unverifyable results: reads of non-committed data fall through and you therefore can have logic in your code which makes decisions based on non-committed data (which can be rolled back).

NOLOCK therefore is only necessary if you have a lot of deadlocks in your code, and deadlocks can be avoided with a different structuring of your code.

In a webservice scenario, I don't really see why nolock is necessary, could you elaborate a bit on that?

It's technically possible to make per-call hints in adapter, with subclassing the DQE classes, I'll get back to you on how to do that. Though I'd appreciate it if you (both) could elaborate a bit on why you would need NOLOCK and take the unverifyable data for granted.

Frans,

In our case, it's exactly as you stated--we have situations in our code where we occasionally get deadlocks and/or (potentially) long waits. There are some specific queries that could be doing dirty reads, which would improve performance. Having all queries do dirty reads (via the static NOLOCK setting) would not work, because some queries SHOULD wait.

For the most part, we have worked through these issues and the request for more discrete control of the NOLOCK hint is far from urgent, but at the time when we were working through these problems, it would have made a difference.

So, to be honest, it's unlikely that we will go back and change anything in our current project regardless of how easy the DQE solution is, but I still think it would be useful to know that this is possible, and how to accomplish it for future project.

Thanks,

Phil

Otis wrote:

NOLOCK isn't something which should be encouraged, as it leads to unverifyable results: reads of non-committed data fall through and you therefore can have logic in your code which makes decisions based on non-committed data (which can be rolled back).

NOLOCK therefore is only necessary if you have a lot of deadlocks in your code, and deadlocks can be avoided with a different structuring of your code.

In a webservice scenario, I don't really see why nolock is necessary, could you elaborate a bit on that?

It's technically possible to make per-call hints in adapter, with subclassing the DQE classes, I'll get back to you on how to do that. Though I'd appreciate it if you (both) could elaborate a bit on why you would need NOLOCK and take the unverifyable data for granted.

Hello,

Thanks for the response.

We are tying a new posting procedure to a complex legacy process. Our requirement is that both our posting and the legacy process must succeed before committing changes to the database. Unfortunately, it is impossible for us to make the legacy process aware of the existence of this new processing. So our strategy has been as follows:

1. Run the new process. If the process succeeds, hold the transaction open. (We don't like this any more than do you.)
2. If the new process succeeds allow the legacy process to run. If the new process fails, run the legacy process in a status only mode.
3. If the legacy process succeeds, commit the new process. If the legacy process fails, rollback the new process.

That's it. In some instances, the new process must use the NOLOCK hint so that the legacy process may proceed.

We cannot run the legacy process first because we have no way of rolling it back after it has committed its changes. We have considered running the legacy process twice (the first in status only mode) but there are several problems with this strategy. Performance suffers, a slight chance of failure during second processing exists and the legacy code isn't conducive to running it consecutively in this way.

BTW, all of this processing usually happens in less than a second.

Thanks, Dale

Thanks guys, I see both of you have no real workarounds at your disposal to avoid these problems.

You've to make a couple of changes by creating subclasses. - First you'll create a subclass of the SqlServer DQE's DynamicQueryEngine class. In the subclass, add a property which will contain the value if nolock should be emitted by a select created by this dqe, e.g. a boolean flag. - In the property setter of the flag, be sure you set the DynamicQueryEngine.Creator.UseNoLockHintsForObjectNames to the value of the new flag in the DQE. Once this flag is set, the creator (which produces the table names and also the hints (because it's a hint provider too :) ) will produce NOLOCK hints for you. - You now have to make sure the adapter creates instances of your DQE instead of the original. In a subclass of the DataAccessAdapter class, override the routine CreateDynamicQueryEngine and create an instance of your DQE class and return it. - in the subclass of the adapter, add a flag to set the nolock hint and in your override of the CreateDynamicQueryEngine routine, set the flag you added to the derived DQE class on the DQE instance to return.

that should be it, also for subqueries.

Please let me know if something didn't work.

Update, 01-sep-2009, this doesn't seem to work, though there's a better workaround, please see: http://www.llblgen.com/tinyforum/GotoMessage.aspx?MessageID=92356&ThreadID=16508

Hello,

Thanks for these instructions. I've incorporated the suggested changes into the projects and everything fits together well. I haven't tested any of this yet and will let you know if I run into any issues with these changes.

BTW, since the creator object is declared using IDbSpecificCreator the assignment to the SQLServerSpecificCreator's UseNoLockHintsForObjectNames property was made by casting the Creator property with: ((SqlServerSpecificCreator)Creator).UseNoLockHintsForObjectNames

Thanks again, Dale

garisenda wrote:

Hello,

Thanks for these instructions. I've incorporated the suggested changes into the projects and everything fits together well. I haven't tested any of this yet and will let you know if I run into any issues with these changes.

BTW, since the creator object is declared using IDbSpecificCreator the assignment to the SQLServerSpecificCreator's UseNoLockHintsForObjectNames property was made by casting the Creator property with: ((SqlServerSpecificCreator)Creator).UseNoLockHintsForObjectNames

Thanks again, Dale

Ah, indeed, a cast is necessary.

Hello,

I am using LL2.6.

I have a need to set a NOLOCK on a specific connection as well, not globally, due to some legacy code issues. Can anyone post a sample of their implementation of solution that Frans suggested in this thread?

I can't seem to get it to work.

For the subclassed dynamic query engine, I have created a new class in the database specific adapter project:

class CustomNoLockDynamicQueryEngine : SD.LLBLGen.Pro.DQE.SqlServer.DynamicQueryEngine
    {
        public CustomNoLockDynamicQueryEngine(): base() 
        {
            ((SqlServerSpecificCreator)Creator).UseNoLockHintsForObjectNames = true;
        }
    }

I have then also created a separate NoLock DataAccessAdapter class for testing:

    public class DataAccessAdapterNoLock : OneShop.Mc.DAL.DatabaseConnector.DataAccessAdapter
    {
        /// <summary>
        /// CTor
        /// </summary>
        /// <param name="connectionString">The connection string to use when connecting to the database.</param>
        public DataAccessAdapterNoLock(string connectionString)
            : base(connectionString)
        {
        }

        protected override SD.LLBLGen.Pro.ORMSupportClasses.DynamicQueryEngineBase CreateDynamicQueryEngine()
        {
            //return base.CreateDynamicQueryEngine();
            return new OneShop.Mc.DAL.DatabaseConnector.CustomNoLockDynamicQueryEngine();
        }
    }

My consuming code is then creating an instance of the new data access adapter class:

OneShop.Mc.DAL.DatabaseConnector.DataAccessAdapterNoLock  dataAccessAdapter = new DataAccessAdapterNoLock(connectionString);

ScheduleEntity scheduleEntity = new ScheduleEntity(18646);
dataAccessAdapter.FetchEntity(scheduleEntity);

When I do this, data is retrieved, but when I put tracing in the app to check the sql that is emitted, it does not contain NOLOCk?

Help, anyone?

Thanks.

Can1

Please open new threads rather than hijacking old ones.

In reference to your question - if you step through the code, does your custom NoLock DQE actually get returned ?

Matt