Win a copy of Kotlin in Action this week in the Kotlin forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic

Database issue in Multi threaded application  RSS feed

 
sachin yadav
Ranch Hand
Posts: 156
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi All,

We are developing a core java multithreaed application which will run across multiple machines(JVM's). Now there is a piece of code which will work like -

1. check if entry is resent in record_master table.
2. If no then make entry in record_master and record_child.
3. If yes then fetch primary key of record_master and use it as foreign key in record_child and make entry in later table only.

this whole code is synchronized() and a class level lock is placed on it to take care of multiple threads within JVM, which works fine. But incase of multiple JVM it fails some time and make 2 entries in record_master. Sceneries goes like this -

Machine 1 running thread t1m1 and t2m1
Machine 2 running thread t1m2 and t2m2.

If t1m1 and t1m2 will reach on point 1 at same time, they will make 2 entries in database while it should be only 1. We are using Jdk 1.5 executor framework to create and manage threads. Simple JDBC with a XA data source running on weblogic 9.1 and oracle 10g. we are using core java only and it's too late in the game to introduce something else. we can't take database table locks as this will hit performance.

Can someone please let me know how can we control dirty reads here?
 
Andrey Kozhanov
Ranch Hand
Posts: 79
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Maybe you should add

0. Connect to the database

point to the whole logic? Perfomance could be decreased slightly but only latest database data will be visible.
 
Paul Sturrock
Bartender
Posts: 10336
Eclipse IDE Hibernate Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

this whole code is synchronized() and a class level lock is placed on it to take care of multiple threads within JVM, which works fine. But incase of multiple JVM it fails some time and make 2 entries in record_master. Sceneries goes like this -

Why? Would pessimistic locking on the DB not be a better choice, rather than implementing locking in a single client?

Ah - just read you whole post more thoroughly. Not sure I understand, won't synchronizing not give you much the same bottleneck? How about using serializable transaction isolation for the connection that performs this particular transaction? OK, yet another bottleneck, but I can't really see how you avoid this without changing your requirements.
 
sachin yadav
Ranch Hand
Posts: 156
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
@Paul,

Thanks for your reply. Yes i could not figure out any solution within the limitations of requirements, that is why i posted my query on this forum. Let me see if i can find something by changing requirements a bit.

@Andrey :

I am not sure but 0 is always presented there. We connect to db to get latest data but the problem is that at the same time same point 1 is being hit by some thread running on other machine and hence a dirty read occurs. Please note that our system is running across 3-4 server box(JVM) with nothing like a load balancer. Our application takes care of all the jobs distribution and execution with in all configured JVM.

Thanks for your help.
 
Paul Sturrock
Bartender
Posts: 10336
Eclipse IDE Hibernate Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Another alternative could be to write the code that controls this update as a distinct application and have other VMs call it via RMI. That way this application could control if it were the only instance somehow (maybe by registering its presence in the database?) and you can at least know that as far as your client is concerned the update is controlled. But this is an over-engineered solution, and does prevent another process unrelated to your application updating the data. This sounds very much like there are existing database mechanisms that exist for this very situation.
 
sachin yadav
Ranch Hand
Posts: 156
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Yes Paul, i am controlling this behavior using database fields now and code seems to be working now, how ever this is not the best thing to do but we do not have choices. Thanks again for your valuable suggestions.
 
Sudheer Bhat
Ranch Hand
Posts: 75
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
You are querying to see for the presence of the record. So I assume the field on which you are querying is a unique for the master table. So just create a unique index on that column. That should take care of the issue you are facing. The index will not let duplicates in it since its unique and one of your threads will get ORA-0001 error. You can handle that appropriately in your java code.

Also having an index on your unique DB field will make your query to run faster!.
 
sachin yadav
Ranch Hand
Posts: 156
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
It's a two fold process -- you query master, if not data insert and then insert child. If data is present in master then insert only in child with the primary key of master. I did consider your approach but that's not a feasible solution for us as of now.

JDBC will always raise SQLException to indicate that entry needs to be made in child only but an SQLException might occur in other scenario also where this exception will be not be an indication of inserting data in child table. Moreover we use service bus so that leaves very narrow possibilities to do much in this way.

 
Martin Vajsar
Sheriff
Posts: 3752
62
Chrome Netbeans IDE Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Oracle has a MERGE statement which can insert a row only if it does not exist yet. This should solve your problem:

1) use MERGE to insert the row atomically if it didn't exist yet,
2) read the master record to get master ID,
3) use master ID to store child record.

You still need to create a unique constraint (index) over columns identifying your entry, but your standard processing will not involve any exceptions. Be aware that your data model is faulty without the unique constraint.

Look up MERGE in Oracle's SQL reference.
 
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!