Simultaneously request to servlet and causing duplicate trx_id
Hi,
I have a servlet which will handle all the request from external.
That servlet is responsible to insert the request into database by copy a record which already created in one of the table.
My problem is when two requests are coming simultaneously with same trx id, I will hv error of duplicate key.(Note, I assume external party send duplicate request with same trx_id in the same time)
I hv tried to synchonize the method of insert record but seems it does not work. The duplicate primary key error still occurs.
Anyone hv any idea how to solve it? Any suggestion is appreaciate.
Thanks.
# 3
The trx_id is when the first request they come in, we will response back them by assigning each request with one trx_id which is primary key of one of the table. So when they make the second request, they need to send us the trx_id and we will move the first request record to another table. Now the problem is when move the first record to another table(means the trx_id in the first request will b primary key as well for another table), we get duplicate key when we insert a record.
Please advice if you hv any solution on it.
Thanks
# 4
Mouving record from one table to another is very bad idea. Because it means You have two similar/equivalent entities in Your DB. And it is redudant and confusing. I guess You can see it now ;)
What I would recomend use trx_id as PK in the first table and as forein key (FK) in any other where You need to refer to the first one. This will provide integrity and performance as well.
Good luck!
# 5
I did not duplicate record. After it is moving to another table, the currect record will b deleted. As I know we need to change our database design by using foreign key. However the application now is sitting in production and I could not juz change it like that. It will involve others application as well.
Now my question is how I can ensure each table onli can handle one request per time. As we know synchonize method is to lock the method and others request is not allow to enter the record. But why for my case, even I use synchonize method but others request still can access the method in the same time?
