0

We are facing odd issue. We have two parts 1. Windows task to update database 2. Web API using same database to provide search results

We want to pause API while Windows task updating the database. So Search results won't be partial or incorrect. Is it possible to pause API request while database is being updated? Database update take about 10-15 seconds.

user2918107
  • 103
  • 8

2 Answers2

2

When you say "pause", what do you expect to happen to callers? It seems like you are choosing to give them errors instead of incomplete data.

If possible, your database updates should be wrapped in a transaction so consumers get current, complete data until the transaction is committed. Then, the next call will have updated and complete data.

I would hope that transactional processing would also help you recover from errors in your updates. What happens now if something fails part way through an update?

This post may help you: How to Decide to use Database Transactions

snow_FFFFFF
  • 3,235
  • 17
  • 29
  • Thanks, so if I do something like this `BEGIN TRANSACTION DELETE FROM TABLEA DELETE FROM TABLEB INSERT INTO TABLEA select * from TempA INSERT INTO TABLEB select * from TempB` Then my API which is taking data from TABLEA and TABLEB should be safe ?? – user2918107 May 01 '18 at 13:47
  • 1
    Yes - your api won't see the changes until you call COMMIT TRANSACTION. Typically, you would open your transaction and then do any data manipulation within TRY/CATCH blocks so you could handle any potential errors. If you hit an error, you would ROLLBACK TRANSACTION. If no error, COMMIT TRANSACTION. See the following for an example: https://stackoverflow.com/a/10153706/3242103 – snow_FFFFFF May 01 '18 at 14:50
0

If the API knows when the this task is being starting, you can do have the thread sleep for 10 seconds by calling:

System.Threading.Thread.Sleep(10000)
Nico Pizzo
  • 105
  • 10
  • I tried doing that. I added flag like this to my database. First thing in my ActionResult I do `if(database.flag = "updating") System.Threading.Thread.Sleep(10000) ` and then I serve search results. But I am still getting old results. Is it because I am using unitofwork and this whole thing is in one request? – user2918107 Apr 30 '18 at 21:42