H2 Login Timeout

[Prakash]

Hello H2 Users,

I am currently using H2-1.2.142.jar in my application,

I create a connection pool as follows,
connPool = JdbcConnectionPool.create(url, "sa", "sa");
connPool.setLoginTimeout(60);

the url also includes the keyworkds, ;AUTO_SERVER=TRUE

Currently I am trying to read records out of the database with say 20
threads, and am seeing this Login Timeout. I have increased the
timeout from default 30 seconds to 60 but am still getting this.

java.sql.SQLException: Login timeout
at
org.h2.jdbcx.JdbcConnectionPool.getConnection(JdbcConnectionPool.java:
211)
at
comp.data.ConnectionPoolManager.getConnection(ConnectionPoolManager.java:
88)
at comp.report.CompareTask.process(CompareTask.java:47)
at comp.report.Report2$Producer$1.run(Report2.java:325)
at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(Unknown
Source)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
at java.lang.Thread.run(Unknown Source)

Is there any upper limit for the number of threads to use?,
Is there some setting that can help me get past this error?

Please Help.

Thanks
Prakash

[Joe]

Believe by default the pool is of size 10. If your threads obtain the
connection and retain it then in theory 10 of the 20 threads cannot
get one. Try increasing the size of your pool and see if those login
timeouts turn into LOCK_TIMEOUTS.

Did you observe that the waiting period was truly 30 or 60 seconds
before those timeouts occurred?

[Prakash]

Thanks Joe,

Is max connections the same as the size of the pool? I didn't get the
Login Timeout error after Increasing the max connections in the
connection.
If it is not the same please let me know how to increase the
connection pool size.

If it is the same, is it advisable to have value of max connections as
40, will there be any negative impact?

[Joe]

Yes, pool size and max connections is the same thing.

Whether it is advisable to have 40 connections or not entirely depends
on your usage scenario. By default the database is single threaded so
having so many connections is likely not buying anything in terms of
performance.

If you were seeing log-in timeouts before I'd be concerned that you're
now susceptible to lock timeouts since instead of having to wait for
the connection object (log-in timeout) you're now waiting for your
statement to get a lock on a table (lock timeout).

If you're using a thread pool of some kind you might want to consider
the usage pattern the threads in that pool would levy on the database
and find some happy middle ground between thread pool size and
behavior and database connection pool size and behavior.

Joe

[Prakash]

Thanks Joe, your explanations were very helpful. Much appreciate it.

[Thomas Mueller]

Hi,

FYI: There was a bug (the timeout exception was thrown too early), but
that was fixed in version 1.2.141. See also the change log.

Regards,
Thomas

[priyanka]

hello sir,

From last 2 months am using h2 database and its really good.
Now iam using the latest version of h2 database i.e h2-1.3.169
just want to tel that am working with million database
and using this settings
lock_mode=0;auto_server=true;auto_reconnect=true;lock_timeout=1000000;
i tried mvcc=true
Then also iam getting the exception as Login timeout
so my Questions are
1)y? we get login timeout
2)how and where to increase Login timeout or
3)If we connect remotely is there any problem we get login timeout because remotely only 5 clients we are connecting at once normally connecting also its giving login timeout.and remotely connecting also its giving the same.
4)and please suggest me the best settings i can apply for my h2 database.. and get rid of exceptions

Thanks a lot...in advance..please help me....

[priyanka]

Hello sir,

I have small Question...

My database server is used by 7 clients...because iam connecting remotely

while connecting remotely in h2 database...Iam getting an exception as login timeout
I have increased lock timeout and mvcc=true... for some time it works..after that again i get the same..as login timeout..and i need to again refresh it...

1. can you please tel me how and where to increase the login timeout...
2. Is it in connection pool we need to increase login timeout..?
3.  how to avoid this exception...

Thanks in advance...

[Noel Grandin]

On 2012-10-16 11:35, priyanka wrote:
> Hello sir,
>
> I have small Question...
>
> My database server is used by 7 clients...because iam connecting remotely
>
> while connecting remotely in h2 database...Iam getting an exception as
> login timeout
> I have increased lock timeout and mvcc=true... for some time it
> works..after that again i get the same..as login timeout..and i need
> to again refresh it...

Sounds like you have network problems, not database problems.
You'll need to run some kind of network tool like Wireshark to analyze
the connection.

[Noel Grandin]

On 2012-10-17 14:03, priyanka priya wrote:

hai sir,
 
Thank you so much for replying

i have downloaded Wireshark and analyzed all the connections...but there is no problem with network

and then i have disconnected remote connection and started using my local host h2 database then also iam getting the exception as Login timeout
and my application is getting stopped...

OK, nice work.
In that case you probably have something locking the whole database for long periods of time.
Look for very long-running transactions, or for a session that is opening a transaction and then forgetting to commit() it, leaving the database locked.