Multithreading and Database operations.

[bob]

In an effort to keep my UI responsive I have begun to run my time intensive
methods on their own thread and in particular the database inserts.
I open my DB connection in the constructor and close it in the dispose
method. So database_insert method is quick to do its business and finish.

As I was doing these database inserts in response to a timer tick event I
had a situation where the time for my database_insert method was greater
then the tick (especially when I added some time wasting loops to slow it
down).

As I was creating these new threads ( for the database_insert method ) each
tick I can assume that I had more than 1 thread trying to do an insert at a
time. And although the database was getting all the entries I was sending it
AND the UI was still responsive I can't help wondering whether this is a
very very very bad thing to be doing.

1) Should I even be running database operations on a thread I create anyway.
2) Can I check to see if the previous thread has completed. ( considering
that I create it in the tick_event's scope )
3) is the worst that can happen is perhaps my inserts in the database are
out of sequence .. or more probably disaster with a hundred threads all
trying to write at the same time ... ( *shudders* )

I guess I could set a flag that I check before creating a new thread ...
then of course I'd need to consider the thread locking the flag so I don't
read it in a transitional state ... oh dear me ... this is looking knotty.

What should I consider? it seems like a really elegant solution to use
threads do the database operations and keep the UI responsive.
At the moment I'm using the OleDb provider, but I will move to an MySQL one
.... if that has any bearing.

code snippets for those require.
private void timer1_Tick(object sender, System.EventArgs e)
{
update_graphics();
// updates screen & pretty gauges
new Thread( new ThreadStart( DB_insert_method)).Start();
// starts a new thread to handle slower DB calls
}

private void DB_insert_method()
{
// sleep(2000);
oleDbCommand1 = oleDbConnection1.CreateCommand();
oleDbCommand1.CommandText = "INSERT INTO Table1 (myValue,theTime)
VALUES (@myValue, @myTime)";
oleDbCommand1.Parameters.Add ("@myValue",OleDbType.VarChar).Value =
myValue.ToString();
oleDbCommand1.Parameters.Add ("@myTime",OleDbType.Date).Value =
System.DateTime.Now;
oleDbCommand1.CommandType = CommandType.Text;
oleDbCommand1.ExecuteNonQuery();
}

regards Bob

 

[Steven Nagy]

It would appear to me that the same connection is being used in your
different threads, and therefore not that thread safe.

Recently I re-engineered our general data connector DLL that we use for
all projects. This is what I did to get around similar problems to what
you have:

Created a ThreadCollection. When the procedure is called (in your case,
DB_insert_method ), I first check the current thread in the collection.
If it finds it, it gets an associated connection object, if not, it
creates a new threadconnection object which it adds to the collection.
Periodically, it goes through the collection and cleans out any threads
that have finished.

This ensures a unique connection for each thread, and thus no chance of
any 2 threads accessing the same connection at the same time.

To truly test your code, I would suggest that instead of using a
ExecuteNonQuery call, instead call a stored procedure that has a WAIT
statement in it.
That way you can guarentee that you can reconstruct an environment
where 2 threads want to use the same connection at the same time.
I bet you do start having problems.

 

[William Stacey [MVP]]

Why not queue a delegate on the thread pool (or your own thread pool)? Your
delegate could create a new connection, so each thread will be its own
connection.

--
William Stacey [MVP]

| In an effort to keep my UI responsive I have begun to run my time
intensive
| methods on their own thread and in particular the database inserts.
| I open my DB connection in the constructor and close it in the dispose
| method. So database_insert method is quick to do its business and finish.
|
| As I was doing these database inserts in response to a timer tick event I
| had a situation where the time for my database_insert method was greater
| then the tick (especially when I added some time wasting loops to slow it
| down).
|
| As I was creating these new threads ( for the database_insert method )
each
| tick I can assume that I had more than 1 thread trying to do an insert at
a
| time. And although the database was getting all the entries I was sending
it
| AND the UI was still responsive I can't help wondering whether this is a
| very very very bad thing to be doing.
|
| 1) Should I even be running database operations on a thread I create
anyway.
| 2) Can I check to see if the previous thread has completed. ( considering
| that I create it in the tick_event's scope )
| 3) is the worst that can happen is perhaps my inserts in the database are
| out of sequence .. or more probably disaster with a hundred threads all
| trying to write at the same time ... ( *shudders* )
|
| I guess I could set a flag that I check before creating a new thread ...
| then of course I'd need to consider the thread locking the flag so I don't
| read it in a transitional state ... oh dear me ... this is looking knotty.
|
| What should I consider? it seems like a really elegant solution to use
| threads do the database operations and keep the UI responsive.
| At the moment I'm using the OleDb provider, but I will move to an MySQL
one
| ... if that has any bearing.
|
| code snippets for those require.
| private void timer1_Tick(object sender, System.EventArgs e)
| {
| update_graphics();
| // updates screen & pretty gauges
| new Thread( new ThreadStart( DB_insert_method)).Start();
| // starts a new thread to handle slower DB calls
| }
|
| private void DB_insert_method()
| {
| // sleep(2000);
| oleDbCommand1 = oleDbConnection1.CreateCommand();
| oleDbCommand1.CommandText = "INSERT INTO Table1 (myValue,theTime)
| VALUES (@myValue, @myTime)";
| oleDbCommand1.Parameters.Add ("@myValue",OleDbType.VarChar).Value =
| myValue.ToString();
| oleDbCommand1.Parameters.Add ("@myTime",OleDbType.Date).Value =
| System.DateTime.Now;
| oleDbCommand1.CommandType = CommandType.Text;
| oleDbCommand1.ExecuteNonQuery();
| }
|
| regards Bob
|
|

 

[Ignacio Machin \( .NET/ C# MVP \)]

Hi,

In an effort to keep my UI responsive I have begun to run my time
intensive methods on their own thread and in particular the database
inserts.
I open my DB connection in the constructor and close it in the dispose
method.

No a good idea, open the connection as late as possible, release it as soon
as possible. That's a golden rule in DB connectivity.
I usually open the connection inside the method that will execute the
command and close it in the same method. The connection returns to the
connection pool when closed and becomes available to another request.

As I was doing these database inserts in response to a timer tick event I
had a situation where the time for my database_insert method was greater
then the tick (especially when I added some time wasting loops to slow it
down).

What timer r u using? IIRC System.Threading.Timer tick is executed in a
separate thread. you should consider using it.

As I was creating these new threads ( for the database_insert method )
each tick I can assume that I had more than 1 thread trying to do an
insert at a time. And although the database was getting all the entries I
was sending it AND the UI was still responsive I can't help wondering
whether this is a very very very bad thing to be doing.

No, why it should be wrong? in fact it's the correct way of going.

1) Should I even be running database operations on a thread I create
anyway.

Yes, or in a thread from the threadpool

2) Can I check to see if the previous thread has completed. ( considering
that I create it in the tick_event's scope )

why you need this? the db can handle concurrent connections fine, unless you
need to perform the db actions in a certain order. it's ok to send several
commands to the DB, just do as I said before regarding connections though.

3) is the worst that can happen is perhaps my inserts in the database are
out of sequence .. or more probably disaster with a hundred threads all
trying to write at the same time ... ( *shudders* )

Do the inserts orders matter? if so you should enqueue the request and you
will end with a tipical producer/consumer scenario.

are you expecting several hundred operations? if so you should reconsider
your strategy.