aspose file tools*
The moose likes Java in General and the fly likes A simple DB deadlock problem Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Java 8 in Action this week in the Java 8 forum!
JavaRanch » Java Forums » Java » Java in General
Bookmark "A simple DB deadlock problem" Watch "A simple DB deadlock problem" New topic
Author

A simple DB deadlock problem

Dhan Kumar
Greenhorn

Joined: Aug 03, 2009
Posts: 29

I have a list of work to be processed by a fixed(say 20) number of threads.
Requirement : I have to insert one record in a certain table but with sequence number.
Example
work_ID Seq_ID
work1 1
work3 2
work5 3
work4 4
workx n

Sequence number shows in what order these records were inserted. Strange requirement , but it is like that.
What I do :
Step 1 : Find max seq_id from the table. It will be a select query on table.
Step 2 : new seq_id = seq_id + 1, set this in the object
Step 3 : Insert into table the object created in step 2 in the table

And booooom : I am getting db deadlock when executing the application. From the log I can say its coming in step 1.

What I did again : I made the whole method as synchronized which included these 3 steps. Now I am not getting DB deadlock but I am getting DB timeout for some threads. Looks like some threads are getting starved and transaction time is used in just waiting for the lock.

Can you advise a better approach for this problem ? I believe the thread must pass through a synchronized block so that I can calculate the sequence number.

Note : Threads are responsible for some other things as well. This is one of part of whole execution.


Dhan
SCJP - Here for Knowledge..
Winston Gutkowski
Bartender

Joined: Mar 17, 2011
Posts: 7081
    
  16

Dhan Kumar wrote:Can you advise a better approach for this problem ?

Well, one thing would be to let the db work out the sequence number itself. They're quite good at that. It's almost certainly just a constraint on the ID field. And if the insertion really has to be 'staged' (although I'm not at all convinced that it does), just add a "ready for processing" column that defaults to false, which you change to true after the "insertion" is complete.

I'm not sure if there's any way to get the sequence number of a newly inserted row with JDBC, but if there is, I'm sure one of the experts here can tell you how. Alternatively, don't worry about it and just let the database handle it.

Winston

PS: This page shows one way to do it, but I have no idea whether it's the best or not.

Isn't it funny how there's always time and money enough to do it WRONG?
Artlicles by Winston can be found here
Dhan Kumar
Greenhorn

Joined: Aug 03, 2009
Posts: 29

Thanks Winston for your reply and some good suggestions.
As you said , DB will be best to do this job. But for that there will be DB change which I do not want to do.
If it is possible by java logic then I will be saved. The sequence number is a field in my Entity bean object which is finally stored in corresponding Table. I believe , to implement seq number by DB , I have to apply some DB changes or some change in the hibernate mapping file for this particular column. But at the same time , every time this seq num will have to start from 1. I will try your advise.
May be some expert here have some other view and I can find other solution.
Winston Gutkowski
Bartender

Joined: Mar 17, 2011
Posts: 7081
    
  16

Dhan Kumar wrote:As you said , DB will be best to do this job. But for that there will be DB change which I do not want to do.

Why not?

If it is possible by java logic then I will be saved.

Maybe right now you will, but you'll be adding a whole pile of unnecessary (and error-prone) logic to do something that the db can already do for you.

I believe , to implement seq number by DB , I have to apply some DB changes or some change in the hibernate mapping file for this particular column.

So, do it. Don't avoid doing it properly just to save yourself a bit of work.

Have a look at my signature quote, because this sort of thing is exactly what it's about.

Winston
Dhan Kumar
Greenhorn

Joined: Aug 03, 2009
Posts: 29

Winston , Thanks for the motivation. DB will be the best and most stable solution for this.
But my next problem is that the Table and the Java entity object(Lets call it WorkItemDE) belong to other module and I can call only available services like :
findMaxSequence , createWorkItemDE, modifyWorkItemDE , deleteWorkItemDE.
I can not change the hbm file of the WorkItemDE...because other modules might be using this in a different way. DB or hbm change will affect others as well.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: A simple DB deadlock problem
 
Similar Threads
Why I lost points in locking, pls comment on my code
can anyone help please
Passed SCJD
record lock&unlock
Data class thread safe using a RecordLockingManager and a DataAccessFile