Issue in handling concurrent requests

[Rakesh Kayaprathodi]

Hi

I am working in critical project where we are facing an performance issue with the java web services.

Let me explain the issue,

We have a table which contains unique serial numbers. currently we have more than 1 million records and it is growing day by day. Each web service request has to pick an unique number from the table. For that we are keeping a status column with initial value as OPEN' and update the status to 'USED' when it is assigned to a request.

We are expecting more than 200 concurrent requests. In this scenario multiple requests are trying to pick the same number and updating to 'USED' . To avoid this we tried following techniques.

1) Select & update SQL query :- Select the number having status 'OPEN' and update to 'USED' in same query.
2) Used synchronize block around number selection logic.
3) Used a temp table to lock the number selection logic.

All these logics are working fine if there are less concurrent requests. but with more concurrent requests we are getting performance issues, some requests are having long waiting time and crossing the SLA or getting timed out. During our analysis we found, threads are waiting for other threads to release the lock. In first scenario it is taking a table level lock. We are using DB2 as database. we also tried select query 'with UR' to avoid table lock but it is picking duplicate numbers.

We are using JDBC to connect to DB2 database.

What is the best way to address this situation without performance impact. Is there any other tool or technology available to address this?

Thanks in advance,
Rakesh

[Anil KumarVerma]

Hi Rakesh,
If I understand it correctly, each web service request has to pick an unique number from the table. Is it necessary to pick a unique number from the table. How about generate a unique random number on the fly and then write it to the table.
Multithreading in Java is easier said than done. The principle I follow while developing multi threaded applications in java is to make most of my classes immutable and hence thread safe.

[Rakesh Kayaprathodi]

Hi Anil

Thanks for your help. I cannot generate the numbers since it is coming from external source. It should be the same as what is there in external system. Here my classes are thread safe but all are trying to get the values from same table.

Please let me know if you need any other details.

Thanks
Rakesh

[Anil KumarVerma]

Few things can be done to improve performance.
1) Do an indexing for your table for corresponding column. That would ensure that get access to the table would be faster.
2) The GET and UPDATE operation should be in same synchronized block (I guess you would already be doing that).
3) See if your database and application can be present on the same physical server so that both GET and UPDATE queries are faster. Involve your DBA as well to get his/her suggestions.
4) Since you are expecting around 200 concurrent requests, how about creating a CACHE set of your table records and do your GET and UPDATE operations on your CACHE set. That way you could completely avoid application to database round trip for each request.

You also mentioned that your table has already million plus records and it keeps growing. How about keeping only NEW numbers in that table. Have a job which runs say every 4 hours and moves all USED numbers to a new table. That way, your main table won't keep growing.
As you mentioned that you are expecting 200 concurrent requests, so above things should suffice. If you are expecting considerable increase in concurrent requests (10k+), consider using AKKA or Node.JS (technologies meant for building concurrent networking apps)

[Rakesh Kayaprathodi]

Hi Anil

Thanks for your valuable inputs. I would like to know more ablout CASHE mechanism.I never used it before. What are the different types of CASHE mechanism available? Do we need any software or tools need to be installed for this? Also please share if you have any good links or tutorials available

Thanks in advance.
Rakesh

[Anil KumarVerma]

Check out JCSCache or EhCache
http://www.ehcache.org/
https://commons.apache.org/proper/commons-jcs/

Either of them would suffice your needs