1

I have developed an WCF application in VB.NET which fetches records(unique) one by one from database (for now MS ACCESS, later it will be moved to SQL) and work on that and update the same record accordingly. Now I want to run multiple instance of this application on multiple virtual server, I am afraid that at any point of time more than one instance of the application might pick the same record and result an error. How can I avoid picking up same record?

I have tried updating a field while picking up the record and check for the value in that field while picking up records to know whether that record has already been picked up by other instance of the application. As of now it has worked when I am running multiple instance in the same server. Not sure whether it will work while working on different server running simultaneously.

Meghdut Saha
  • 85
  • 1
  • 14
  • It might be good to look into row level locking. If you are using SQL Server this page has a bit of info - https://learn.microsoft.com/en-us/sql/relational-databases/sql-server-transaction-locking-and-row-versioning-guide?view=sql-server-2017 Otherwise there is a bit of guidance here too - https://stackoverflow.com/questions/3462643/why-is-table-level-locking-better-than-row-level-locking-for-large-tables – Navik Hiralal Jun 21 '19 at 05:22
  • Thanks for your reply Navik, I have checked row level locking in google solves my purpose partially. Let me give you an example. Suppose the table has 2 records say A and B with columns col1,col2,col3 with col1 being primary. Now when I run the application, it gets 2 records and starts working on record A as that is the first available record to work on and after working (takes some minutes which is variable) it updates record A accordingly. If the second instance is run simultaneously, it also will pick up record A as that is the first available record and will start processing. – Meghdut Saha Jun 21 '19 at 06:08
  • If I lock the row, the update will not occur, but both the instances of the application will waste time on processing record A. Correct me if my understanding is not correct. What i want is if one instance picks up record A, the second instance will not pick up record A, instead it will pick up record B. – Meghdut Saha Jun 21 '19 at 06:08
  • I'm not sure if its the best way, but you could use a combination of a status flag and row locking - upon picking up a row from the table the first instance could update the status flag so the second instance can see that this is being worked on (i.e. Select from where Status !=
    – Navik Hiralal Jun 21 '19 at 06:28
  • Get a real database first. Access is not designed for concurrent application instances. Then implement locking mechanisms that the chosen database provides. – Nic3500 Jun 22 '19 at 02:30

0 Answers0