Threading and a database connections.

[Guest]

The program works like this:
There is a form with a button.
When the form is loaded, a separate thread is started which is
retreiving/updating data in the database every x seconds.
When clicked on the button, data is retrieved from the database.

This looks to work fine. However, sometimes after clicking on the butten to
retrieve the data i got an error message (on the separate thread):
"The connection is already Open"

After changing the query that is executed when the button is clicked to a
more extensive query, the error is popping up every time.

The database is an Access database.
Each tread has it's own connection.
Each tread opens and closes its own connection.

Who can help me?

 

[Sankalp]

Check the following:
1. Are you closing your connections in the Finally block?
2. If you are disposing some objects in the Finally block, check if
they exist or not; If Not Object Is Nothing Then Object.Dispose
3. Throw all your exceptions-it should not happen that an error happens
in some function and the program moves ahead.
Update commands would always apply a lock on the table, however,
select commands should not, so use WITH NOLOCK in all select queries.

Sankalp

 

[Runni]

we had the same problem of being that
"The Connection is already open"

Even we r very new to .Net but we could manage to get the solution.

Actually we created a common class for all the methods and in that
constructor only
we hav defined the open connection for the database which led to many
problems.

the solution is that u hav to create a new configuration file (ie.
..config file).
and also create the unique class which is only meant for the opening of
the connection.

and open the connection were ever u need and after the transaction
,then immediately
close the connection.this is a great help when we use datareaders

try this even for urs if it works then fine.

 

[Guest]

Hello, thank you for your replay, however, this is not the answer i was
looking for.
the error should not occur, so i would like to know why i have this problem.
Of course, putting it all in a try and catch routine is a work around, but
that is not what i want.
Any more suggestions?

 

[Guest]

Hello, thank you for your replay.
What you did is right, but because i'm using 2 threads, this is not answer
to the problem.

 

[Sankalp]

You got it all wrong-I asked you to close all your connections in the
finally block and not suppress your errors by wrongly using a
Try-Catch.:-D

"Connection already open" - the only thing that can be inferred from
this your connections are not getting closed.

Adding a data layer or using DataAdapter and 'rightly' using Finally
will also resolve your problem of making sure all your connections get
closed.

 

[Guest]

Hello, the connections are getting closed, the problem is in the treading.
I would exepct that eacht tread has it's own connection. But i looks like
this is not the case, and the 2 treads use the same connenction.

Routine 1:
1: open connection1
2: execute query
3: close connection1

Routine 2:
1: open connection2
2: execute query
3: close connection2

Repeating these routines on one thread does not result in an error.
Repeating each routine on one threads will eventualy result in an error.
Why is the connection of routine 1 interfearing with the connection of
routine 2?

 

[Guest]

I"m new to DataBase access and have a question. During an Update, you say
the database is locked. If I try to access it with a query while it's
locked, won't it wait until the database is unlocked to execute the query or
do I have to put the query in a loop until it's executed? Thanks for any
help you can give me to understand this.

 

[Sankalp]

In SQL Server 2000, if you execute a query affecting a row, then that
particular row will be locked so that no other query can access the
same row till the transaction completes. The other rows are available
to update queries. This is row-level locking. Similarly, if the update
query will be affecting multiple rows, SQL Server applies a lock
depending upon the memory available. If these rows are present in
contiguous pages, then SQL Server might lock those pages, extents or
even the entire table. If pages are locked, then the rest of the rows
in other data pages can be accessed. If the entire table is locked,
then the other simultaneous queries will have to wait until the
transaction is completed. So, as you can see SQL Server chooses the
type of locking for each transaction-it all depends on the memory
availability. If an update query is being executed, the select query
will have to wait till the update transaction completes.

However, SQL does give us some kind of control over the type of
locking. You can still specify a ROWLOCK in an update query. Similary
a select query applies a read lock on the tables. However, you can
specify not to let this happen by using WITH NOLOCK in your select
queries.

This is not the end of story-there is something called Transaction
Isolation Levels. Refer to the SQL Server Books online and you can read
in detail about them.

There is a Microsoft fix to the problem when SQL Server applies a lock
when an Update query is fired and a Select query is fired at the same
time. As per this fix, you experience this problem when you run a
SELECT statement that uses the TOP clause and the READPAST locking
hint.

Refer to this: http://support.microsoft.com/?kbid=867746

HTH.
Sankalp

 

[Sankalp]

Let me have a look at the code you have written in the thread event-the
event handler which gets fired by your threads, the way you have
declared your connection object, they way you open database
connections.
As you said, each thread will be starting its own connection. Think of
them as separate pieces of code-when you create a NEW connection
object, and the other thread also creates a NEW connection object, the
previous thread has got nothing to do with it. They are separate
objects created in the managed pool and its your softwares
responsibility to return them back to the connection pool when you dont
need them. This is where you HAVE to close connections in a FINALLY
block after checking whether your application was able to open them in
the first place or not.
The ideal way to open connections is to use connection pooling as that
will increase the performance of your application greatly and more so
when you have a multithreaded application. The .NET framework
automatically creates a connection pool for you for each unique
connection string. But this is not the point being discussed here.

Sankalp

 

[Guest]

Hello, here is the code for the test program, just copy it in a new form.
Make sure you have a Access database (C:\Database.mdb) with a table named
Sessions with the fields [Session ID] Varchar(20), [Active] YESNO.
At first everything seems to work fine. But when it takes longer for the
dataset to fill, the error occures (in my case when there where around 40
records in the sessions table).

Private Shared DbConn_Thread_Form As OleDb.OleDbConnection = New
OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User
ID=Admin;Data Source=C:\Database.mdb;Mode=Share Deny None;Extended
Properties="""";Jet OLEDB:System database="""";Jet OLEDB:Registry
Path="""";Jet OLEDBatabase Password="""";Jet OLEDB:Engine Type=5;Jet
OLEDBatabase Locking Mode=1;Jet OLEDB:Global Partial Bulk Ops=2;Jet
OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password="""";Jet
OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet
OLEDBon't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica
Repair=False;Jet OLEDB:SFP=False")
Private Shared DbConn_Thread_Timer As OleDb.OleDbConnection = New
OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User
ID=Admin;Data Source=C:\Database.mdb;Mode=Share Deny None;Extended
Properties="""";Jet OLEDB:System database="""";Jet OLEDB:Registry
Path="""";Jet OLEDBatabase Password="""";Jet OLEDB:Engine Type=5;Jet
OLEDBatabase Locking Mode=1;Jet OLEDB:Global Partial Bulk Ops=2;Jet
OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password="""";Jet
OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet
OLEDBon't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica
Repair=False;Jet OLEDB:SFP=False")
Public TimerDelegate As New Threading.TimerCallback(AddressOf TimerTask)
Public TimerItem As Object
Dim Button1 As New System.Windows.Forms.Button

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load
Button1.Text = "Start"
AddHandler Button1.Click, AddressOf Button1_Click
Me.Controls.Add(Button1)
TimerItem = New System.Threading.Timer(TimerDelegate, Nothing, 1000,
3000)
End Sub
Sub TimerTask(ByVal StateObj As Object)
Beep()
Dim DbComm_Session As New OleDb.OleDbCommand("INSERT INTO [Sessions]
([Session ID], [Active]) VALUES ('" & Now() & "', -1)", DbConn_Thread_Timer)
DbConn_Thread_Timer.Open()
DbComm_Session.ExecuteNonQuery()
DbConn_Thread_Timer.Close()
End Sub
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs)
Button1.Enabled = False
Dim DS_Result As New DataSet
Dim DbComm_Result As New OleDb.OleDbDataAdapter("SELECT
Sessions.[Session ID], Sessions.Active FROM (((Sessions INNER JOIN Sessions
AS Sessions_1 ON Sessions.Active = Sessions_1.Active) INNER JOIN Sessions AS
Sessions_2 ON Sessions_1.Active = Sessions_2.Active) INNER JOIN Sessions AS
Sessions_3 ON Sessions_2.Active = Sessions_3.Active) INNER JOIN Sessions AS
Sessions_4 ON Sessions_3.Active = Sessions_4.Active", DbConn_Thread_Form)
DbComm_Result.Fill(DS_Result, "QueryData")
Button1.Enabled = True
End Sub