Table locking between two processes WCF and Website

[Paul]

Hi Everyone,

I have an issue wherein I have tables being locked.  Essentially I have data that I am parsing in my WCF service that is updating a few tables.  The method is marked with a [Transaction] attribute.  When I login to the site, the pages which use some of the data from these tables is timing out due to the transaction that is running in the WCF service (and the lock I believe it has).  The process can be long, so any user who logs in will have to wait until it stops.

I have not set any optimistic or pessimistic locks explicitly in the code for both the WCF and web site.  I am not sure at this point what and where I need to set this to avoid this.  Any help would be appreciated!

I've tried adding this line to the configuration without any luck (both config files):

<property name="connection.isolation">ReadUncommitted</property>

I found some posts in regards to SessionFactory as well as FluentNHibernate mapping.Optimistic.[type].  I tried the latter, but not the former.

Here is the NHibernate Initializers:

WCF:

public static class NHibernateInitializer

    {

        public static void Init()

        {

            if (NHibernateSession.ConfigurationCache == null)

            {

                NHibernateSession.ConfigurationCache = new NHibernateConfigurationFileCache(new[] { "company.Domain" });

            }

            if (NHibernateSession.Storage == null)

            {

                NHibernateSession.Init(new ThreadSessionStorage(),

                                       new[] { System.Web.Hosting.HostingEnvironment.MapPath("~/bin/company.Infrastructure.dll") },

                                       new AutoPersistenceModelGenerator().Generate());

            }

        }

    }

    public class ThreadSessionStorage : ISessionStorage

    {

        [ThreadStatic]

        private static ISession _session;

        public ISession Session

        {

            get

            {

                return _session;

            }

            set

            {

                _session = value;

            }

        }

        public ISession GetSessionForKey(string factoryKey)

        {

            return Session;

        }

        public void SetSessionForKey(string factoryKey, ISession session)

        {

            Session = session;

        }

        public IEnumerable<ISession> GetAllSessions()

        {

            return new List<ISession>() { Session };

        }

    }

Web site:

NHibernateSession.ConfigurationCache = new NHibernateConfigurationFileCache(new[] { "company.Domain" });

            NHibernateSession.Init(

                this.webSessionStorage,

                new[] { this.Server.MapPath("~/bin/company.Infrastructure.dll") },

                new AutoPersistenceModelGenerator().Generate());

Thanks!

[Paul]

Having had more time to think about this as well as researching the subject, do I:

- Just want to create a single SessionFactory via a static class that can be accessed from both the web application and the WCF service?

- If that is the case, how would that work if I had another windows application that uses the [UnitOfWork] attribute instead of [Transaction]?  They both have to use different Session storage methods (i.e. WebSessionStorage and ThreadSessionStorage as I recall during the NHibernateSession.Init() method

Thanks!

[Paul]

After more research and analysis, the issue boiled down to the isolation level setting (which I wasn't aware of nor considered at the beginning of this quest).  All of the transactions were utilizing the READ COMMITTED, which I changed to READ UNCOMMITTED via the following property setting in the NHibernate.config:

<property name="connection.isolation">ReadUncommitted</property>

This will allow NHibernate to set the transaction isolation level accordingly.

Once I added the setting, I could run the WCF service as well as navigate to areas in the web site that utilized the same tables as the WCF service without any issues.  I should also mention that any method that performs reads must have either a [Transaction] or [UnitOfWork] attribute as well.

I hope this helps others who may have come across this issue