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.
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 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.
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?
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 , 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.