Threading with Database Access

T

Tommy

Hi!

I have a singleton class that accesses a database (using jtds as
database driver).

class DatabaseAccessSingleton {

private static DatabaseAccessSingleton instance;

public static DatabaseAccessSingleton getInstance() {
if (instance==null) {
instance = new DatabaseAccessSingleton();
}
return instance;
}

public Vector getData() {
Vector data = new Vector();

try {
ResultSet rs =
connection.createStatement().executeQuery("SELECT * FROM TABLE");

while (rs.next()) {
data.add(rs.getString(1));
}

return data;

} catch (Exception e) {
}
}
}

That´s roughly the pattern that I use.
DatabaseAccessSingleton.getData() might be called by multiple threads.
So my question is, how to make it thread-safe?
In the jtds database driver I have read that while connection is
threadsafe, Statement is not.

So if I make the whole method synchronized, I guess that would solve
it. But then, only one thread at a time has access to the database, the
other threads would be blocked.

How can I allow multiple threads to use getData() without blocking? Or
is the method getData() even thread-safe as it is now?

Thanks!

Tommy
 
J

Joseph Dionne

Tommy said:
Hi!

I have a singleton class that accesses a database (using jtds as
database driver).

class DatabaseAccessSingleton {

private static DatabaseAccessSingleton instance;

public static DatabaseAccessSingleton getInstance() {
if (instance==null) {
instance = new DatabaseAccessSingleton();
}
return instance;
}

public Vector getData() {
Vector data = new Vector();

try {
ResultSet rs =
connection.createStatement().executeQuery("SELECT * FROM TABLE");

while (rs.next()) {
data.add(rs.getString(1));
}

return data;

} catch (Exception e) {
}
}
}

That´s roughly the pattern that I use.
DatabaseAccessSingleton.getData() might be called by multiple threads.
So my question is, how to make it thread-safe?
In the jtds database driver I have read that while connection is
threadsafe, Statement is not.

So if I make the whole method synchronized, I guess that would solve
it. But then, only one thread at a time has access to the database, the
other threads would be blocked.

How can I allow multiple threads to use getData() without blocking? Or
is the method getData() even thread-safe as it is now?

Thanks!

Tommy
http://www.janeg.ca/scjp/threads/synchronized.html

change your getdata method declaration to;

public synchronized Vector getData( ...
 
S

shakah

Tommy said:
Hi!

I have a singleton class that accesses a database (using jtds as
database driver).

class DatabaseAccessSingleton {

private static DatabaseAccessSingleton instance;

public static DatabaseAccessSingleton getInstance() {
if (instance==null) {
instance = new DatabaseAccessSingleton();
}
return instance;
}

public Vector getData() {
Vector data = new Vector();

try {
ResultSet rs =
connection.createStatement().executeQuery("SELECT * FROM TABLE");

while (rs.next()) {
data.add(rs.getString(1));
}

return data;

} catch (Exception e) {
}
}
}

That´s roughly the pattern that I use.
DatabaseAccessSingleton.getData() might be called by multiple threads.
So my question is, how to make it thread-safe?
In the jtds database driver I have read that while connection is
threadsafe, Statement is not.

So if I make the whole method synchronized, I guess that would solve
it. But then, only one thread at a time has access to the database, the
other threads would be blocked.

How can I allow multiple threads to use getData() without blocking? Or
is the method getData() even thread-safe as it is now?

Thanks!

Tommy
It might be OK as is, as long as it is safe to interpret "Statement is
not" as meaning that multiple threads calling the same Statement Object
is not thread-safe but that multiple threads using distinct Statement
Objects (as you imply in your example) is OK. I'll throw in the
obligatory disclaimer that I can also see how that interpetation could
be unsafe, given that I tend to equate a Connection with a socket and
that the SQL traffic would have to successfully share that socket.
 
R

Ross Bamford

Hi!

I have a singleton class that accesses a database (using jtds as
database driver).

class DatabaseAccessSingleton {

private static DatabaseAccessSingleton instance;

public static DatabaseAccessSingleton getInstance() {
if (instance==null) {
instance = new DatabaseAccessSingleton();
}
return instance;
}

public Vector getData() {
Vector data = new Vector();

try {
ResultSet rs =
connection.createStatement().executeQuery("SELECT * FROM TABLE");

while (rs.next()) {
data.add(rs.getString(1));
}

return data;

} catch (Exception e) {
}
}
}

That´s roughly the pattern that I use.
DatabaseAccessSingleton.getData() might be called by multiple threads.
So my question is, how to make it thread-safe?
In the jtds database driver I have read that while connection is
threadsafe, Statement is not.

So if I make the whole method synchronized, I guess that would solve
it. But then, only one thread at a time has access to the database, the
other threads would be blocked.

How can I allow multiple threads to use getData() without blocking? Or
is the method getData() even thread-safe as it is now?

Thanks!

Tommy

I'd be tempted to leave it as-is. Statement may well be unsafe but
you're throwing it away anyway. Outside that I leave the sync/serial to
the DBMS (It's better at it).

Failing that, if you're sharing your connection, why not synchronize on
that throughout?

Ross
 
T

Tommy

One thing that is still not totally clear is the role of local
variables.

In the getData() method, there is the data Vector. So given I would not
make any call to the database via connection, there would not be any
threading issue since data is not shared between threads.
Data is a local variable that gets created when the method is called.
So when different threads call getData(), each gets its own new data
object.

Is that correct? So could I make the local data Vector into an
ArrayList and still consider it thread-safe (ignoring the database
access issue for a while)?
 
S

shakah

Tommy said:
One thing that is still not totally clear is the role of local
variables.

In the getData() method, there is the data Vector. So given I would not
make any call to the database via connection, there would not be any
threading issue since data is not shared between threads.
Data is a local variable that gets created when the method is called.
So when different threads call getData(), each gets its own new data
object.

Is that correct? So could I make the local data Vector into an
ArrayList and still consider it thread-safe (ignoring the database
access issue for a while)?

Sure, all the variables used in getData() (data, the Statement, the
ResultSet) are local (except for connection, of course, which is
presumably an instance variable) and pose no concurrency problems.
 
R

Ross Bamford

One thing that is still not totally clear is the role of local
variables.

In the getData() method, there is the data Vector. So given I would not
make any call to the database via connection, there would not be any
threading issue since data is not shared between threads.
Data is a local variable that gets created when the method is called.
So when different threads call getData(), each gets its own new data
object.

Is that correct? So could I make the local data Vector into an
ArrayList and still consider it thread-safe (ignoring the database
access issue for a while)?
This is true. I would make getData() return a Collection (or maybe List
if ordering/indexing matters), and you could then return any kind of
list you like.

The data variable is your new list, you're guaranteed no-one else is
accessing it because you haven't given it to anyone else yet, so you
don't need the overhead of Vector's synchronization (which could be
surprising with a lot of records).

Ross
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

No members online now.

Forum statistics

Threads
473,744
Messages
2,569,484
Members
44,903
Latest member
orderPeak8CBDGummies

Latest Threads

Top