How to know when a db connection is free?

[Jonck van der Kogel]

Hi all,
This is perhaps more of a theoretical question than a real-life one,
but I would be very interested to know how to solve this.

I have an application where I keep one connection to the database open
while the application is running and all the communication with the
database takes place through this connection. The base class holds a
database connection object that is called by a lot of other classes in
the application which then go on and use this connection to read and
write from the database.
So the base class has a getDbConnection() method which returns the
connection object. Now if my entire application takes place in the
main thread there is no problem, there will only be one method trying
to access the connection at a time.
But when I have, let's say, a background thread that is running and
checking whether the data in my tables is consistent with the data as
it is stored in the database, a problem could arise. If the main
thread is reading or writing from the database using a session and in
the middle of this the background thread comes and asks for the
connection and starts doing stuff with it I guess some weird stuff
could happen. Now I know I could just give the background thread it's
own connection, but just out of interest I would like to know how to
do this using a single connection.
Obviously I need some way to tell the background thread (or the main
thread for that matter) whether or not the connection is in use at
that time. So I could set some boolean field which a user of the
connection will need to set to free when it is done with it. The
calling method will need to check this field first before calling the
getDbConnection method. But what if the connection is busy at that
time?

If I do a simple
if (application.isConnectionFree) {
dbConn = application.getDbConnection();
} else ... // else what??

I would like the calling method to wait and try again at a later time,
but idealy I would want it to make this call *right* after the other
thread is done with it. How do I do this? I can't let a thread wait
for the main thread by using a thread.join(), since the main thread
will never be finished untill the application shuts down

Any suggestions?

Thanks very much, Jonck

 

[Michael Borgwardt]

Obviously I need some way to tell the background thread (or the main
thread for that matter) whether or not the connection is in use at
that time. So I could set some boolean field which a user of the
connection will need to set to free when it is done with it. The
calling method will need to check this field first before calling the
getDbConnection method. But what if the connection is busy at that
time?

If I do a simple
if (application.isConnectionFree) {
dbConn = application.getDbConnection();
} else ... // else what??

else what? is exactly the question. In most cases, there will not be a useful
alternative, so you might as well wait until the connection is "free".
This is exactly what java's synchronization does, and I suspect that
most, if not all, implementations of JDBC connections are synchronized for
exactly that reason, so that no "weird stuff" happens.

So you actually don't have to do anything at all. You problem is already solved.

 

[Jonck van der Kogel]

else what? is exactly the question. In most cases, there will not be a useful
alternative, so you might as well wait until the connection is "free".
This is exactly what java's synchronization does, and I suspect that
most, if not all, implementations of JDBC connections are synchronized for
exactly that reason, so that no "weird stuff" happens.

So you actually don't have to do anything at all. You problem is already solved.

Ahhh... great, thanks!