SOSHibernate: SQL Server set LOCK_TIMEOUT command should not create a new connection

Current Situation

• When openSession() is called , SOSHibernate will execute the SET LOCK_TIMEOUT <milliseconds> command.
  • <milliseconds> - defaut 30000 (30 seconds)
  • Other value can be set in a hibernate configuration file, e.g:

    <property name="hibernate.sos.mssql_lock_timeout">60000</property>

• Execution of this command always creates a new database connection independenty if an inactive connection eventually available in the connection pool or not.
  • In this case the pool can hangs when trying to get connection due to the max connection pool size is exhausted.

Desired Behaviour

• Execution of this command should have no effect to create a new database connection but use a connection delegated by hibernate/connection pool.

Workaround (when the connection pool hangs in SOSHibernate openSession)

1. increase the max connection pools size (hibernate.c3p0.maxPoolSize)
2. Microsoft SQL JDBC Driver
   1. extend the connection string (hibernate.connection.url) with lockTimeout=<milliseconds>
      • e.g.:

        <property name="hibernate.connection.url">jdbc:sqlserver://...;lockTimeout=30000</property>

1. 1. disable the default SOSHibernate set LOCK_TIMEOUT behaviour (starting with JobScheduler release 1.13.5)

      • <property name="hibernate.sos.mssql_lock_timeout">-1</property>

Note
the the connection pool hangs error was detected while executing the JobScheduler JobStream plugin and fixed by the JobStream plugin implementation with the JobScheduler release 1.13.5