CODEWITHSUNDEEP
javascheduled-tasksdeadlock
Prabhanshu Sharma
Prabhanshu Sharma

Reputation: 27

How to use multiple java programs running on different machine accessing same table without getting deadlock

I have a java program running on different machines. In those program i have used scheduler which inserts and updates table in database. Now how should i handle program so that dead lock doesn't occur in future because it may happen that program in both machine try to update the table at same time or if one is updating table and if other tries to access the table then since it is being used by program on different machine then it may become some slow.

Please note that same program is running on different machines..

I thought of using synchronization but since programs are on different machine , so there is no need to use synchronization here.

What logic should I used?

Upvotes: 1

Views: 328

Answers (3)

Constantin
Constantin

Reputation: 1506

Can you use optimitic locking instead of locking down tables through a pessimistic locking scheme?

With optimistic locking, you basically read in the record, making note of the timestamp and id of the row.

When you go to update the samw row, you ensure the timesatamp is the same as when it was initially retrieved. Something like

UPDATE foo SET col1 = 123 WHERE ID = @id AND ts =@ts

If the update occurs, it means no other user or process updated the row while you were viewing and or editing it. If no update occured, you can re-retrieve the same row with its up=to-date data and re-apply your same changes and try again

Also, make sure that a database trigger modifies this ts on any insert or update.

This is the preferred way of handling multi-user concurrency in a relational database

Now if you absolutely must have a lock, you can use a SELECT FOR UPDATE type statement which allows you to lock the records. You are not required to make changes to the records in order to use this statement. The record locks are released when the next commit or rollback statement is issued.

Upvotes: 0

Panther
Panther

Reputation: 3339

IF first connection has lock and second waits for it. Then it will not impact the first query performance . And definetly second connection cannot take lock or update on same rows so no chance of deadlock. However you can check that your query takes only row level locks, so other connection can work if you want parallel processing.

If parallel processing not needed you can run schedular at different time in each machine by reading time from external property file so that same program can run at different time.

You can also keep some flag in common database in separate table to check if other process running and sleep the current program for sometime.However i will prefer schedular at different time.

Upvotes: 0

user207421
user207421

Reputation: 310840

You can't get a deadlock with only one resource being locked. A deadlock requires two or more resources and an inconsistent locking order. As long as you only have one table, or multiple tables that everybody always locks in the same order, you're OK.

Upvotes: 3

Related Questions