Nhibernate "Pooled connection request timed out" error

[Hakan karaoğlu]

Greetings,

I have a question, I would be glad if you can help.

Consider an application that heavily handles messages coming from Rabbitmq queue. Meanwhile, it queries the database and the data is saved and updated.

My question is, it processes 500 of the 1000 incoming data, but the rest mostly gives the error I mentioned on the subject. "Pooled connection request timed out" I think the problem is from nhsession and somehow it fills the pool.

What should I do, what would you recommend?

Example configuration is below.

private static object lockObject = new object();

        private static ISessionFactory sessionFactory;

        private static FluentConfiguration configuration;

        private static ISessionFactory SessionFactory

        {

            get  { lock (lockObject)  {

              if (sessionFactory == null)

                    {

                        sessionFactory = configuration.BuildSessionFactory();

                    }

                    return sessionFactory;

                }

            }

        }

public static IServiceCollection AddSniffactorNhibernate(this IServiceCollection services, string connectionString)

        {

            Check.NotNull(connectionString);

            configuration = Fluently.Configure()

                .Database(OracleManagedDataClientConfiguration.Oracle10

                .ConnectionString(connectionString)

               Mappings(m => m.FluentMappings.AddFromAssemblyOf<ISniffactorMapping>());

            services.AddSingleton(SessionFactory);

            services.AddScoped(factory => configuration.BuildSessionFactory().OpenSession());

            services.AddScoped(typeof(IRepository<>), typeof(NhRepositoryBase<>));

            services.AddScoped(typeof(IUnitOfWork), typeof(NhUnitOfWork));

            services;

        }

And example a repo

public class KeywordDetectionViewRepository : NhRepositoryBase<KeywordDetectionView>, IKeywordDetectionViewRepository

    {

        private readonly ISession _session;

        public KeywordDetectionViewRepository(ISession session) : base(session)

        {

            _session = session;

        }

        public async Task<List<KeywordDetectionView>> GetKeywordsAsync(string source, string tag, LanguageTypes languageTypes)

        {

            var query = _session.CreateCriteria<KeywordDetectionView>();

            int keywordId = int.Parse(tag.Substring(2));

            if (tag.StartsWith("k_"))

            {

                query.Add(Restrictions.Eq("KeywordId", keywordId));

            }

            else if (tag.StartsWith("v_"))

            {

                query.Add(Restrictions.Eq("VariationId", keywordId));

            }

            query.Add(Restrictions.Eq("SourceCode", source));

            query.Add(Restrictions.Or(Restrictions.Eq("LanguageCode", languageTypes.ToString().ToLowerInvariant()), Restrictions.IsNull("LanguageCode")));

            var result = await query.SetCacheable(false).ListAsync<KeywordDetectionView>();

            return (List<KeywordDetectionView>)result;

        }

    }

[Jørn Wildt]

It seems like you are missing a session.Close() ... and perhaps also some transaction handling with a commit() or rollback() (but that is not a cause for your problem).

If you don’t close sessions and thus database connections, the database layer will close them for you - but first after some idle timeout. If you open more than you close, you eventually run out of connections.

/Jørn

--
You received this message because you are subscribed to the Google Groups "nhusers" group.
To unsubscribe from this group and stop receiving emails from it, send an email to nhusers+u...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/nhusers/ff0cbb51-f210-412e-9058-4db704eea99fn%40googlegroups.com.

--

/Jørn

[Hakan karaoğlu]

Where should I do this? 

can you give me a example?

5 Oca 2021 Sal, saat 23:11 tarihinde Jørn Wildt <j...@elfisk.dk> şunu yazdı:

To view this discussion on the web visit https://groups.google.com/d/msgid/nhusers/CALh-yk%2BPGmAjz8AFWi%2B6jGhPbOZiBHv4Jy_UZ2gAXVUcn0dS%2Bg%40mail.gmail.com.

[Jørn Wildt]

Something like this:

void HandleMessage(m)

{

  session = open_session();

  try

  {

    ... do stuff with message and database ...

  }

  finally

  {

    if (session != null)

      session.Close();

  }

}

To view this discussion on the web visit https://groups.google.com/d/msgid/nhusers/CAAhRLA1p8pe2z0HkVY9jhq-nzT8ptGSBWRBgFddpKpYsmu4zmQ%40mail.gmail.com.

[Hakan karaoğlu]

hi Jorn,

Thank you for your information, as you said, I changed the structure. But I'm not sure, I wanted to ask again. I wanted to explain a little bit about my work with the images below.

I configure nhibernate as follows

UoW configured like this

An exemplary consumer, the application is actually stuck here, as in the last image, errors are usually caused by this.

As above, the operation calls the query here

and finally, I'm sending the query to the database here with nhibernate.

In the next period, I get the following error on Rabbitmq side.

I hope I was able to explain my problem, thank you so much

8 Ocak 2021 Cuma tarihinde saat 00:01:09 UTC+3 itibarıyla jorn...@gmail.com şunları yazdı:

[Jørn Wildt]

Sorry, that just got too complex. Getting a null reference somewhere in the Oracle database layer is not something I can help with.

Happy hacking :-)

/Jørn

To view this discussion on the web visit https://groups.google.com/d/msgid/nhusers/59700c83-33ec-494a-be30-274a0c78962cn%40googlegroups.com.

[Gunnar Liljas]

Are you 100% sure that you get a new instance of the Consumer (and therefore, the UoW and the session) every time a message is received? That's very important.

Weird object reference errors are typical of thread safety issues.

/G

 

[Hakan karaoğlu]

Gunnar,  I found the error and fixed it. However, this time, other consumers who received the message started to give different errors. For example

Received "Pooled connection request timed out" error 1881 times among 13 thousand data

1000 times "Object reference not set to an instance of an object." got the error 

 

By the way, I set the connection timeout to 120 in the configuration. What else can you suggest?

13 Ocak 2021 Çarşamba tarihinde saat 02:37:39 UTC+3 itibarıyla Gunnar Liljas şunları yazdı:

[Hakan karaoğlu]

let me give you one more information, my example query you can see below  

 

14 Ocak 2021 Perşembe tarihinde saat 00:16:06 UTC+3 itibarıyla Hakan karaoğlu şunları yazdı:

[Gunnar Liljas]

Maybe you should try to limit concurrency. Masstransit has ways of doing that.

/G

--

You received this message because you are subscribed to the Google Groups "nhusers" group.
To unsubscribe from this group and stop receiving emails from it, send an email to nhusers+u...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/nhusers/b422eb7c-9561-4a90-bc63-6ccbcd810e39n%40googlegroups.com.

[Hakan karaoğlu]

Hi Gunnar,

As you said, I used concurrent limit and second level cache. There is no problem at the moment, thanks for your suggestion

14 Ocak 2021 Perşembe tarihinde saat 00:44:49 UTC+3 itibarıyla Gunnar Liljas şunları yazdı:

[Felipe Oriani]

Hey @Hakan and guys

I'm sorry, I know it is almost an old thread, but, just to be sure you have tried something I want to suggest. 

We have a project where the requirements is to perform over the SQL Server and Oracle depending of what license the customer has.

I had some issues with pool of Oracle and the final solution was to add a call to clear the Oracle Pool from the current database connection of the application. 

The OracleDataManaged provider has a static method from the OracleConnection class where you pass an instance of a OracleCOnnection and it will be closed and removed from the pool (Oracle). I am not a expert of Oracle but it solve the problem when the application get a lot of users running.See on the link:  https://docs.oracle.com/cd/E85694_01/ODPNT/ConnectionClearPoolCtor1.htm

The implementation should follow something like this:

var oracleConnection = session.Connection as OracleConnection;

if (oracleConnection != null)

    OracleConnection.ClearPool(oracleConnection);

if (session.IsOpen)

    session.Close();

session.Dispose();

To view this discussion on the web visit https://groups.google.com/d/msgid/nhusers/4eb1b2af-7754-4b8e-b93e-970e53da8755n%40googlegroups.com.

--

______________________________________
Felipe B Oriani

felipe...@gmail.com