my dog learned polymorphism*
The moose likes JDBC and the fly likes Reading / Writing in to table Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Reading / Writing in to table " Watch "Reading / Writing in to table " New topic
Author

Reading / Writing in to table

Suren Singh Khatana
Greenhorn

Joined: Mar 05, 2009
Posts: 9
Guys ,

I want to write a JDBC Code in which i have to read a column from the database table and also update the same column after doing some processing .
Issue which i am facing is that , when multiple threads read and write , Concurrency issue is creeping up .

How to avoid concurrency and make sure that no thread is allowed to read until one thread reads and update .

Thanks
Suren
Campbell Ritchie
Sheriff

Joined: Oct 13, 2005
Posts: 38481
    
  23
Lock the database connection? Use a transaction?
Suren Singh Khatana
Greenhorn

Joined: Mar 05, 2009
Posts: 9
Thanks for your reply ,

Can you give an example how to lock connection and user transaction or point me to some reference


Thanks
Suren
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336

Introducing pessimistic locking is usually a bad idea - are you sure you really need this?

For transactions, the JDBC tutorial covers these.


JavaRanch FAQ HowToAskQuestionsOnJavaRanch
Suren Singh Khatana
Greenhorn

Joined: Mar 05, 2009
Posts: 9
I want to use the best solution ,provided i know it
At this time i only know what my problem is ...

Thanks for help !!


Suren
Sandeep Sanaboyina
Ranch Hand

Joined: Dec 14, 2009
Posts: 72
Instead of locking the DB connection, why not just synchronize the method that is calling the read and update. It should stop any parallel processing by the threads.


They say you have to be the first, the best or different. I say, is it too much to ask for all three.
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336

Sandeep Sanaboyina wrote:Instead of locking the DB connection, why not just synchronize the method that is calling the read and update. It should stop any parallel processing by the threads.


Why introduce this bottleneck? Relational databases are designed to support concurrent access to data well, it's a little counterintuative to turn an inherently multi-user system into a pseudo-single user system. I call it a pseudo-single user system because synchronizing a method will only fix the concurrency issue if only one instance of the client is ever used. More than one (or other clients) and the issue returns.

The issue (as I understand it) can largely be solved by using a transaction with read committed transaction isolation.
Sandeep Sanaboyina
Ranch Hand

Joined: Dec 14, 2009
Posts: 72
Why introduce this bottleneck?
I don't exactly agree to this. Creating a lock till the transaction completes is in itself a bottleneck. Even through we have multiple threads runnning, none will be processing till the lock is released. I don't understand how this is any different from synchronisation.
More than one (or other clients) and the issue returns.
You are totally right on this . Synchronisation does not handle this.

The reason I suggested this is - if we keep a transaction lock, all the remaining threads will open a connection and wait for the query to execute after the lock is released. Based on the number of threads, this can pile up a number of connections that puts load on the database. This, of course, depends on the implementation of the logic.

Also, I think that in some cases (may be in some databases) creating a lock on the transaction, may cause other transactions to throw an SQLException. Correct me if I am wrong.
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336

[
I don't exactly agree to this. Creating a lock till the transaction completes is in itself a bottleneck. Even through we have multiple threads runnning, none will be processing till the lock is released. I don't understand how this is any different from synchronisation.

It depends on how the database implements read committed transaction isolation but the locking mechanism is liable to be a lot more subtle than you would see with synchronization. Some database do use very similar behaviour - SQL Server for example, before Microsoft added snapshot isolation, would use shared read locks which meant you would see an awful lot of blocking (i.e. behaviour very much like synchronizing access to the database). You are also forgetting that synchronizing access is kind of like using the biggest scope lock available. Row locks used by a database's transaction mechanism get escalated as appropriate by the database depending on the amount of sessions it is managing.


Also, I think that in some cases (may be in some databases) creating a lock on the transaction, may cause other transactions to throw an SQLException. Correct me if I am wrong.

You should only see an exception if the transaction is blocked till it times out or a deadlock occurs and your thread is picked as the victim. In the first case this hints at a major bug in the application (e.g. selecting load of data, forgetting to commit transactions, doing something in an infinite loop etc.) the second is expected behaviour of database application and you should probably have code implemented to cater for it by retrying.
 
Don't get me started about those stupid light bulbs.
 
subject: Reading / Writing in to table