Garbage Collection Problem

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for universal data access
Post Reply
glenncm
Posts: 3
Joined: Mon 10 Jul 2017 12:13

Garbage Collection Problem

Post by glenncm » Mon 10 Jul 2017 12:22

Hello. We're using dotConnect Universal and we're using an in-memory SQLite database. We ran into a really strange problem. If the application sits idle for a while, VB's garbage collection is cleaning up the tables! Has anybody seen this? More importantly, any good ideas to keep it from happening? If it matters, we're currently using Visual Studio 2012. Thanks.

Glenn

Pinturiccio
Devart Team
Posts: 2420
Joined: Wed 02 Nov 2011 09:44

Re: Garbage Collection Problem

Post by Pinturiccio » Thu 13 Jul 2017 13:59

We could not reproduce the issue. Please provide the connection string that you use. Please also specify the steps that we should perform for reproducing the issue. If possible, create and send us a small test project.

glenncm
Posts: 3
Joined: Mon 10 Jul 2017 12:13

Re: Garbage Collection Problem

Post by glenncm » Mon 24 Jul 2017 22:28

Sorry for the delay. The application we have is pretty complex so I had to find time to create a simple one to replicate the problem. I just uploaded it. In a nutshell, the application has one form with two buttons and one label. The first button creates a table and adds data. The second one gets a count from the table. If I click the first button, then the second, it works fine. I can click the second button repeatedly in succession and it works. If I then wait a while (maybe half an hour) and then click the second button, I get an error. Here is the code for the form:

Public Class Form1
Friend connection As New UniConnection
Friend sqLiteCmd As New UniCommand
Friend m_sqLiteConnectString As String

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
Dim s1 As String
Dim s2 As String
Dim j As Integer
connection.Provider = "SQLite"
connection.DataSource = ":memory:"
Try
Dim cmd As UniCommand = connection.CreateCommand()
connection.Open()
cmd.CommandText = "create table Demo (Field1 int, Field2 int)"
cmd.ExecuteNonQuery()
For I As Integer = 1 To 10
j = I
s1 = j.ToString
j = I * I
s2 = j.ToString
cmd.CommandText = "Insert into Demo (Field1, Field2) VALUES (" & s1 & ", " & s2 & ")"
cmd.ExecuteNonQuery()
Next
Finally
connection.Close()
Label1.Text = "Values stored in database"
Button1.Enabled = False
End Try
End Sub

Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
Dim j As Integer = 0
Dim k As Integer
Dim s1 As String
Dim cmd As UniCommand = connection.CreateCommand()
Try
connection.Open()
cmd.CommandText = "select Count(*) from Demo"
k = CInt(cmd.ExecuteScalar)
s1 = "count = " & k.ToString
Label1.Text = s1
Finally
connection.Close()
End Try
End Sub
End Class

Pinturiccio
Devart Team
Posts: 2420
Joined: Wed 02 Nov 2011 09:44

Re: Garbage Collection Problem

Post by Pinturiccio » Tue 25 Jul 2017 14:27

SQLite engine closes an in-memory database when the last connection to it is closed. For more information, please refer to http://www.sqlite.org/inmemorydb.html#sharedmemdb

When you used in memory database without shared cache, only one connection to the database is established, and when it is closed, the database is deleted. In the end of the Button1_Click method you close the connection. However, the actual connection is not closed, and is placed to the pool. When you click the "Read DB" button, the connection opens. In this case the connection is taken from the pool. However, if the connection was idle in the pool for a long time, it is closed by the pool. Since there is no more connections to the in-memory database, the database is deleted. When you click the "Read DB" button after it, a completely new connection is created. It is connected to a new database, which doesn't contain the Demo table.

As a workaround, you can replace the following code:

Code: Select all

connection.Provider = "SQLite"
connection.DataSource = ":memory:"
with

Code: Select all

connection.ConnectionString = "Provider=SQLite;Data Source=:memory:;Min Pool Size = 1"
When "Min Pool Size = 1" parameter is used, the connection in the pool won't be closed if it is idle for some time in the pool, because the pool must contain at least one connection.

glenncm
Posts: 3
Joined: Mon 10 Jul 2017 12:13

Re: Garbage Collection Problem

Post by glenncm » Wed 26 Jul 2017 18:39

Thanks for the help!!

Post Reply