synchronization of the connection object

[Adeel Ansari]

watch this. please dont take it as it is. just take it as a scenerio.

i have some handler classes which is instantiated inside init() of servlet. in the constructor of every handler class i m constructing my DB classes say DAOs.

1.
now if i get the connection object, from DriverManager.getConnection(), in the constructor of my each DAO. and all the methods inside that particular DAO use the same connection object to query or update the DB. then if i write

synchronized(conn){
....
...
int n = conn.executeUpdate();
}

then it will be threadsafe. means no other thread can execute an update on that particular table using the same method. right.

2.
if i get the connection object inside every method. then it would not be like that. right.

anybody here can help me out by coming up with some better idea in terms of performance. any comments would be appreciated. because i think if it is right then it would result in lack of performance. isn't it.

[Daniel Mayer]

If you are concerned about concurrent access to the database, you should probably make use of the database's transaction mechanisms instead of java's synchronization.

[m qasim]

I am using the same approach to lock the connection object. One more thing you probably need to do is to lock the db tables also to include other processes in the synch.

[Warren Dew]

adeel ansari:

1.
now if i get the connection object, from DriverManager.getConnection(), in the constructor of my each DAO. and all the methods inside that particular DAO use the same connection object to query or update the DB. then if i write

synchronized(conn){
....
...
int n = conn.executeUpdate();
}

then it will be threadsafe. means no other thread can execute an update on that particular table using the same method. right.

Sorry, but not right. It only means no other thread can use the same connection to write to that particular table. Some other thread can use a different connection to write to that table, perhaps through the same function called on a different instance of the same class.

To synchronize access to database tables, you need to use database transactions, not Java synchronization.

[Adeel Ansari]

Sorry, but not right. It only means no other thread can use the same connection to write to that particular table. Some other thread can use a different connection to write to that table, perhaps through the same function called on a different instance of the same class.

but in the case i have described above no thread will get a different instance. so that means whatever i assumed is right.

here we will see one more problem which is:
- we can not change the properties of our connection object like,

conn.setAutoCommit(false);
conn.close();

because all threads are going to use the same connection object here.

what you people mean by saying DB table lock. there is a table lock mechanism inside the database. whenever we execute a DML on the particular table that instance acquire a row level lock and table level lock on that particular table.

is there any performance issue while we are not doing any kind of connection pooling here.

[Mr. C Lamont Gilbert]

So long as no other DAOs can modify that table, AND that the methods in question are static or only 1 dao instance per table allowed at a time.

Its similar to what i do.