Deadlock in Devart.Common.DbConnectionPool.GetObject()

Deadlock in Devart.Common.DbConnectionPool.GetObject()

Postby a.m.sidorenko » Mon 29 May 2017 07:23

Hi,

We faced with a problem that our applications could not restore to normal functioning after issues with blocked sessions on DB.
The situation is following: some requests on DB leads that a lot of sessions are blocked. After that special job kills the blocking session, but every thread in our application stuck in Devart.Common.DbConnectionPool.GetObject(DbConnectionBase) method.
Image

In 80 seconds trace, there were more than 1000 threads blocked in getting a connection from the pool.
In connection string we have following options:
Code: Select all
Direct=true;Unicode=true;Min Pool Size=0;Max Pool Size=140;Connection Lifetime=1800;


We can't increase Max Pool Size. I'm going to try to lower down Connection Lifetime, but anyway it looks strange that now Oracle provider doesn't return connections of dropped sessions to the connection pool.

We use dotConnect for Oracle v9.2.220.0
a.m.sidorenko
 
Posts: 9
Joined: Fri 21 Apr 2017 05:23

Re: Deadlock in Devart.Common.DbConnectionPool.GetObject()

Postby Pinturiccio » Mon 29 May 2017 16:08

Your "Max Pool Size=140" and you use more than 1000 threads. Max pool size means that only 140 connections with the same connection string can exist simultaneously. So when 140 connections are created, other 1000 threads freeze and wait till some other thread frees a connection. Thus your threads can be blocked even when you do not kill a session.
Are some of the threads blocked when the application works normally and you don’t kill any session?

Note that when you kill a session, your thread will throw an exception. You have to handle this situation and close connections.

Connection Lifetime parameter works in the following way: when a connection is returned to the pool, its creation time is compared with the current time, and the connection is destroyed if this time span (in seconds) exceeds the value specified by Connection Lifetime. The default value is 0 (connection always returns to the pool).

Try using the "Validate Connection=true;" connection string parameter to validate connections that are being taken from the pool. For more information, please refer to http://www.devart.com/dotconnect/oracle/docs/?Devart.Data.Oracle~Devart.Data.Oracle.OracleConnectionStringBuilder~ValidateConnection.html
Does the "Validate Connection" connection string parameter help you?
Pinturiccio
Devart Team
 
Posts: 1997
Joined: Wed 02 Nov 2011 09:44

Re: Deadlock in Devart.Common.DbConnectionPool.GetObject()

Postby a.m.sidorenko » Tue 30 May 2017 05:14

Well, 1000 threads are the result of blocks in the connection pool. This is a web application, so when a request is coming, pipeline starts to process it, tries to get a connection from the pool and then is beeing blocked.

Are some of the threads blocked when the application works normally and you don’t kill any session?
No. During the normal operation, we haven't ever got such blocks.

Note that when you kill a session, your thread will throw an exception. You have to handle this situation and close connections.
Yes, we do so. When pipeline catches an exception it finishes processing the request and sends a response to a client with an error message.

Thank you for pointing to the "Validate Connection" setting. We will try to use it and also lower down "Connection Lifetime" setting to 1 or 2 minutes. Also, we are working on fixing the situation with blocked DB sessions in general.
a.m.sidorenko
 
Posts: 9
Joined: Fri 21 Apr 2017 05:23

Re: Deadlock in Devart.Common.DbConnectionPool.GetObject()

Postby a.m.sidorenko » Thu 01 Jun 2017 06:08

Sadly it was impossible to try "Validate Connection" setting as the app's performance became worse in 4 times. We caught this problem during the load testing.

Also, I tried to lower down "Connection Lifetime" setting from 30 to 2 minutes but looks like this caused another performance problem during normal operation of the app (no blocking sessions in DB).
At some point in time the app started to spend a lot of time awaiting connections from the pool, example trace from one thread:
Image

This is what we saw in a monitoring tool. You can see spikes of connections:
Image
a.m.sidorenko
 
Posts: 9
Joined: Fri 21 Apr 2017 05:23

Re: Deadlock in Devart.Common.DbConnectionPool.GetObject()

Postby Pinturiccio » Fri 02 Jun 2017 15:34

a.m.sidorenko wrote:Yes, we do so. When a pipeline catches an exception it finishes processing the request and sends a response to a client with an error message.

You can also execute the static method ClearAllPools(true) to clear pools. If a session was killed, connections in the pool are invalid. ClearAllPools deletes connections that are in the pool. However, the connections that were open when the method was called aren’t deleted, and will be returned to the pool even if they are invalid. For more information, please refer to https://www.devart.com/dotconnect/oracle/docs/?Devart.Data.Oracle~Devart.Data.Oracle.OracleConnection~ClearAllPools(Boolean).html

a.m.sidorenko wrote:Sadly it was impossible to try "Validate Connection" setting as the app's performance became worse in 4 times.

Validate connection performs validation each time when a connection is taken from the pool. If connection is not valid, it is closed. This verification takes additional time and decreases performance. If you open a connection often, performance may be impaired. However, in this case you will be sure that a valid connection is open.

a.m.sidorenko wrote:Also, I tried to lower down "Connection Lifetime" setting from 30 to 2 minutes but looks like this caused another performance problem

When you use "Connection Lifetime=120" connection string parameters, it means that connection would be deleted instead of placing to the pool when it is closed if it was created more then 2 minutes ago. As for the issue with session killing, this option won’t help you, but performance will be decreased.

At the moment we can see two situations:
1. You have the Max Pool Size = 140. For example you have already opened 100 connections. When issue with a session occurs 100 connections became invalid. You handle errors generated in your code after this and close connections. These connections are returned to the pool. Then you open another 100 connections. These connections are taken from the pool, but they are invalid, and connection opening takes time. You initialize another 100 threads that open a connection each. 40 connections are opened and other 60 wait while connection timeout is reached. If connection timeout equals to 0 then the connection freezes forever. In this situation "Validate connection" can help, but performance will be decreased in other situations.

2. You have reached Max Pool Size value without the session issue. In this case a thread will wait until some other thread frees a connection. In this case you need to increase Max Pool Size value or open less connections at the same time.
Pinturiccio
Devart Team
 
Posts: 1997
Joined: Wed 02 Nov 2011 09:44

Re: Deadlock in Devart.Common.DbConnectionPool.GetObject()

Postby mynkov » Thu 29 Jun 2017 12:56

I manage to reproduce this problem.
ConnectionString:
Code: Select all
"Direct=true;Unicode=true;Min Pool Size=0;Max Pool Size=140;Connection Lifetime=1800;"

or
Code: Select all
"Direct=true;Unicode=true;Min Pool Size=0;Max Pool Size=140;Connection Lifetime=1800;Validate Connection=true;"


Steps:
1. I lock one row with uncommited "Select for update" from IDE.
2. One thread tries to update same row from App and freezes.
3. I do some other requests from App to another entities and it goes fine.
4. After some time (~1-30 minutes) all my App request are frozen ("Select for update" is still running).

It goes back to normal if I commit "Select for update" from IDE.

Trace (when all request are frozen):
Image


Image


Sessions (when all request are frozen):
Image
mynkov
 
Posts: 6
Joined: Thu 29 Jun 2017 10:17

Re: Deadlock in Devart.Common.DbConnectionPool.GetObject()

Postby Pinturiccio » Tue 04 Jul 2017 13:08

Thank you for the provided information. We will investigate it and post here about the results as soon as possible.
Pinturiccio
Devart Team
 
Posts: 1997
Joined: Wed 02 Nov 2011 09:44

Re: Deadlock in Devart.Common.DbConnectionPool.GetObject()

Postby Pinturiccio » Wed 05 Jul 2017 14:05

If we understood you correctly there is the following behaviour:
1. You have locked some row by "SELECT FOR UPDATE" query.
2. If you update some other record, then all works. But if you update this record in other thread, it hangs.
3. After some time, some threads access this record and freeze till there is 140 connections in the pool. This corresponds to 140 threads that try to modify this row.
4. Then there appear new queries, and new connections are created. Since the pool is full, they throw an error after connection timeout. Since the query is not performed, new and new threads are created. They all try to create a connection with the same connection string.
5. If you unlock a row, locked with the "SELECT FOR UPDATE" query, everything works. But till the row is locked, all threads will try to open a connection with no success.

Did we understand you correctly? If the situation is different, please describe what exactly we have understood incorrectly.

If we understood you correctly, this is the expected behaviour. When a "SELECT FOR UPDATE" query is executed for a row, and another query tries to modify it, the latter query waits till the row is unlocked. Additionally, you have reached max pool size, and you create new and new connections. Naturally, all resources are wasted for opening connections. Since the pool is full, an exception is generated. You process it and open a new connection again and again, and this process takes all resources.
Pinturiccio
Devart Team
 
Posts: 1997
Joined: Wed 02 Nov 2011 09:44

Re: Deadlock in Devart.Common.DbConnectionPool.GetObject()

Postby mynkov » Thu 06 Jul 2017 12:10

No, behaviour is diffrent. I'll try to clarify your suggestions:

But if you update this record in other thread, it hangs.

Yes, it hangs, but I update this record just once and then I don't touch it. Only one thread hangs.

After some time, some threads access this record.

No, other threads don't touch this record. They update another entities.

freeze till there is 140 connections in the pool

No, it freeze when connections less then 140, in our case 3 connection is open (picture 3).

they throw an error after connection timeout

No, they forever stuck in DbConncetionPool.GetObject right before "lock" operation.
mynkov
 
Posts: 6
Joined: Thu 29 Jun 2017 10:17

Re: Deadlock in Devart.Common.DbConnectionPool.GetObject()

Postby mynkov » Thu 06 Jul 2017 12:14

We can reproduce this behavior only when we use IIS hosting.
mynkov
 
Posts: 6
Joined: Thu 29 Jun 2017 10:17

Re: Deadlock in Devart.Common.DbConnectionPool.GetObject()

Postby mynkov » Tue 11 Jul 2017 12:03

Is there any news?
mynkov
 
Posts: 6
Joined: Thu 29 Jun 2017 10:17

Re: Deadlock in Devart.Common.DbConnectionPool.GetObject()

Postby Pinturiccio » Tue 11 Jul 2017 13:19

Thank you for clarification. We are working on reproducing the issue. We will post here when we get any results.
Pinturiccio
Devart Team
 
Posts: 1997
Joined: Wed 02 Nov 2011 09:44

Re: Deadlock in Devart.Common.DbConnectionPool.GetObject()

Postby mynkov » Wed 12 Jul 2017 08:34

Can we get source code to help you fix the problem? Is there such an option?
mynkov
 
Posts: 6
Joined: Thu 29 Jun 2017 10:17

Re: Deadlock in Devart.Common.DbConnectionPool.GetObject()

Postby Pinturiccio » Wed 12 Jul 2017 14:34

If you wish, you can purchase Professional Edition with Source Code. For more information, please refer to https://www.devart.com/dotconnect/oracle/docs/?SrcEdition.html

But this is not necessary. We are investigating the issue. We will post here when we get any results.
Pinturiccio
Devart Team
 
Posts: 1997
Joined: Wed 02 Nov 2011 09:44

Re: Deadlock in Devart.Common.DbConnectionPool.GetObject()

Postby mynkov » Tue 25 Jul 2017 11:24

Did you manage to reproduce this behavior? Is there any news?
mynkov
 
Posts: 6
Joined: Thu 29 Jun 2017 10:17

Next

Return to dotConnect for Oracle