0

I have an app that will be used by up 22 users at one time, that pulls data from an API. in the background the app also copies some of the data into a SQL Database, but I am running into the problem of getting duplicate records because more than one application tries to update the database at the same time.

I have tried using "TimeStamp" in a different table to see when the database was last updated, but that doesn't account for on-going New updates.

back in Microsoft Access world I would use "SeeDatabaseChanges" to prevent records getting duplicated, but is there a way to make sure that only 1 app instance updates the database or better runs the method?

Thanks in advance

Mac
  • 53
  • 6
  • Dear Mac, welcome to stackoverflow. First of all, I advice you to post your code to show how you update your database. And in which language you use? As a solution, according to what you posted, I can advice you to lock your database during the update to avoid that another user updates the same thing. If you post a bit of code, you will have a better chance to have a response. Thanks. – Coskun Ozogul Nov 16 '20 at 10:13
  • Which DBMS product are you using? "SQL" is just a query language used by all relational databases, not the name of a specific database product and the solution to this might well be database specific. Please add a [tag](https://stackoverflow.com/help/tagging) for the database product you are using. [Why should I tag my DBMS](https://meta.stackoverflow.com/questions/388759/) –  Nov 16 '20 at 10:18
  • If you want to prevent duplicates, create a unique constraint. Then deal with the error if it occurs –  Nov 16 '20 at 10:18
  • I scrapped the old version and was looking for Ideas for a new approach. (hence no code). – Mac Nov 16 '20 at 21:28
  • @a_horse_with_no_name - Sorry, the BackEnd is MS SQL Server 2016. I thought I had already set a "primarykey" field, but it would seem it does not enforce Unique, which was an oversight on my part. Thank you. – Mac Nov 16 '20 at 21:31
  • @CoskunOzogul - Not sure how I would lock the database for the connection. which end would that be done? in the connection or in the Settings on the database end? – Mac Nov 16 '20 at 21:31
  • You can take a look at this :https://stackoverflow.com/questions/7395915/does-inserting-data-into-sql-server-lock-the-whole-table – Coskun Ozogul Nov 17 '20 at 08:36

1 Answers1

1

just at a high level: to avoid duplicates - one must define duplicate and check for it before the append/write.

check for dupe may not be trivial; Is it last name, first name or does it need to be every field of the address - or is it even more fields...etc etc...

so there may be some side processing in an import staging temp table to hold new data for the purpose of determining duplicates, and then a final step to append/insert just new records into the permanent table...

the actual implementation is very dependent on how your app actually works and the actual data involved....

Cahaba Data
  • 624
  • 1
  • 4
  • 4
  • thank you. I took what you said on board. and used that to prevent duplication. I then implemented a "master user" token which the app checks for based on a quid generation. this has sorted the issue and ensures only 1 app is running updates to the database at any time. – Mac Apr 13 '21 at 13:23