• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Reading / Writing in to table

 
Greenhorn
Posts: 9
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
 
Marshal
Posts: 79151
377
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Lock the database connection? Use a transaction?
 
Suren Singh Khatana
Greenhorn
Posts: 9
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Thanks for your reply ,

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


Thanks
Suren
 
Bartender
Posts: 10336
Hibernate Eclipse IDE Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Introducing pessimistic locking is usually a bad idea - are you sure you really need this?

For transactions, the JDBC tutorial covers these.
 
Suren Singh Khatana
Greenhorn
Posts: 9
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
 
Ranch Hand
Posts: 74
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
Paul Sturrock
Bartender
Posts: 10336
Hibernate Eclipse IDE Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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
Posts: 74
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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
Posts: 10336
Hibernate Eclipse IDE Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

[
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.
 
Water! People swim in water! Even tiny ads swim in water:
a bit of art, as a gift, the permaculture playing cards
https://gardener-gift.com
reply
    Bookmark Topic Watch Topic
  • New Topic