Hi,

I really appreciate if someone give an idea regarding an issue with multiple threads.

I have 2 databases. In one database, i store all the configuration information and in the other database, that information will get copied over when the request comes in, but it should be copied only once.

But the problem here is if multiple threads are running simultaneously and they are trying to copy the same data multiple times as there was no data when the exection started.


Any ideas on how to resolve these issues? I am really stuck on this. Help is greately appreciated.



Thanks a lot.

Hi,
I guess it depends on how exactly you perform the copy. Could you provide some details on that? Are you copying the rows as-is while maintaining their ID's? what technology are you using for the copy? Hibernate? SQL queries? db-specific utility?

thanks.

I really appreciate your reply.

Yes, i am trying copy a record from one db to another db by creating a new object and setting all the properties via a setter call.

i am not using any tools for this. after i create a new object, i will use normal save method to copy that into the other db.

pl let me know you need any other details.


thanks

Hi,

1. How often do you expect this concurrency to become an issue? I mean, copying a large database is not something you usually do frequently.

2. How many tables are we talking about here?

3. How long does it take to copy the database in your tests (without concurrency)?

Thanks for your reply.

My comments are inline...

1. How often do you expect this concurrency to become an issue? I mean, copying a large database is not something you usually do frequently.
[b]once every 2 months.[/b]

2. How many tables are we talking about here?
[b]5 -10 tables.[/b]

3. How long does it take to copy the database in your tests (without concurrency)?
[b]one record from each table.[/b]


let me know if you have any questions.

Thanks a lot.

maybe if u specified unique constraints/keys on tables u would have avoid such situation.

or add one more column - flag which can indicate if the row is being copied, was copied, or should be copied together with optimistic locking to avoid flag could be update with more threads

Yes, these are options i have exactly tried.

for some reason, i am not able to get the second option working, so i have moved to the first option already, but i was pulled into other taks, so i could not finish up the work.


let me get back to you if i have any questions.

i really appreicate your response. thanks a lot.

Hi,
thanks for the answers. Seems like optimistic locking is ok for your situation, judging by how infrequent this happens. However, there may be a few limitations we should examine first.

1. Is it reasonable for the code that copies the data to have UPDATE privileges on the SOURCE database?

2. Is it reasonable to add columns to each table in the existing schema?

If the answer is YES for 1 and 2, my suggestion is to use optimistic locking techniques. Example: add 2 columns to each table:
1. a lock_version column, specified as the "version" column in hibernate, usually of numeric type e.g. long, and default value 0.
2. 'archived' boolean column, default value=false

Now, instead of simply selecting rows and persisting them into the new database, select a row, set its archive column to 'true' and merge it back to the SOURCE database in the same transaction that writes to the second database. Hibernate will automatically increment the lock_version column. This will effectively implement optimistic locking, causing all but one of the concurrent transactions to fail when trying to copy the same rows. It may be possible to implement optimistic locking without the 'archived' column - if you can get hibernate to "think" that your source rows have changed and that it needs to increment the version column.

good luck :-)

hm.. i see. i need to understand this locking mechanism properly, then it could be the easiest way to fix the issue.

thanks a lot for the response.