473,394 Members | 1,755 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,394 software developers and data experts.

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
Feb 10 '06 #1
3 4227
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.

Feb 10 '06 #2
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]

"bob" <bo*@bob.com> wrote in message
news:uO**************@TK2MSFTNGP11.phx.gbl...
| 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
|
|
Feb 10 '06 #3
Hi,

"bob" <bo*@bob.com> wrote in message
news:uO**************@TK2MSFTNGP11.phx.gbl...
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.


--
Ignacio Machin,
ignacio.machin AT dot.state.fl.us
Florida Department Of Transportation
Feb 10 '06 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: Egbert Bouwman | last post by:
My aim is to develop a policy for database connections and commits, however I do not fully understand these phenomena, and any comment is welcome. I use postgres with psycopg. The essence of my...
55
by: Sam | last post by:
Hi, I have a serious issue using multithreading. A sample application showing my issue can be downloaded here: http://graphicsxp.free.fr/WindowsApplication11.zip The problem is that I need to...
29
by: MP | last post by:
Greets, context: vb6/ado/.mdb/jet 4.0 (no access)/sql beginning learner, first database, planning stages (I think the underlying question here is whether to normalize or not to normalize this...
5
by: Claudio Grondi | last post by:
I have just started to play around with the bsddb3 module interfacing the Berkeley Database. Beside the intended database file databaseFile.bdb I see in same directory also the __db.001...
3
by: Martin B | last post by:
Hallo! I'm working with C# .NET 2.0, implementing Client/Server Applications which are connecting via Network to SQL-Server or Oracle Databases. To stay independent from the underlaying Database...
35
by: Terry Jolly | last post by:
Web Solution Goal: Have a global database connection Why: (There will be 30+ tables, represented by 30+ classes) I only want to reference the database connection once. I put the connection...
3
by: Joe | last post by:
I'm connecting to an Oracle database and running a query which returns data. The query can be fairly long - 2-3 minutes. I have a label set up on the form which is invisible to begin with. I...
12
by: nyathancha | last post by:
Hi, I have a question regarding best practices in database design. In a relational database, is it wise/necessary to sometimes create tables that are not related to other tables through a...
1
by: nicky123 | last post by:
A distributed database is a specialization of distributed computing. A distributed database allows certain data specific operations to be distributed to one or more different machines. This means...
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
0
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers,...
0
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
0
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.