0

I have a Web API in between a SQL database and a C# Winform app. The database table contains serial numbers. The Winform application issues a GET request and on the server-side, the entire list of serial numbers in that table are sorted numerically from smallest to largest.

The smallest number is put into a recursive method, which iterates through the list and generates new ones wherever there is a gap in the numerical sequence. The number of serial numbers that need to be generated is based on the input from the client-side Winform application.

Once the serial numbers are generated server-side, they are returned to the client's Winform application. Once they're happy with everything, they click a 'Print' button, which does three things: (1) Prints the serial numbers, (2) Saves the serial numbers and (3) Sends a POST request to the Web API and commits the newly generated numbers to the table.

I'm new to Web APIs, and have read a bit about throttling, but this doesn't seem to apply to what my problem entails, which is basically this:

The Winform application will be used by several people at a time (not many: two, three, four tops), but there is a chance that two clients issue the same GET request on the same table at the same time. So when one client goes to Print/Save/POST those serial numbers, the other client would not be able to do that given that most or all of those numbers would have already been committed to the database.

So basically, I'm looking for the best way of handling this problem. Maybe there's a way via the Web API to display on each Winform application who is currently using said app, displaying their IP address, and locking out other user's from generating numbers until the one client is finished with their GET/POST process? But this seems somewhat clunky and not as robust.

I'm sure there's a more elegant way of handling this, and being new to Web APIs, I'm not really sure what to search for, besides throttling, but that seems to limit the number of request issued within a certain time frame whereas my conditions require that other users not be able to issue any API calls while other users are in the middle of the GET/POST process.

Daniel Pavlovsky
  • 71
  • 2
  • 13
  • 1
    You could implement a command pattern and use a queue to store the commands issued by clients and handle them in order. – Dennis VW Dec 22 '19 at 02:20
  • This looks very promising. Would this be a ConcurrentQueue on the Web API? Something like: https://stackoverflow.com/questions/19452881/how-to-maintain-state-or-queue-of-requests-in-web-api/19453146 ? – Daniel Pavlovsky Dec 22 '19 at 03:15
  • I'd suggest looking into implementing the complete command pattern. It involves an invoker, command object, sender and receiver. – Dennis VW Dec 22 '19 at 04:41

1 Answers1

-1

The thematic you are talking about is concurrency. In particular Race Condition when updating data:

https://en.wikipedia.org/wiki/Race_condition#Example

Normmally I am a fan of Optimistic Concurrency: You add a Rowversion or similar column. Check if it is still the same during a update. But this seems like it would require pessimistic concurrency - locking those numbers once one user starts thinking about modifying them. But personally I never really used it. There is just too many cases to consider, for removing the locks.

Another way entirely, might be a slight redesign of your Serial Number System. What you really wanted, is groups of serial numbers for related products, right? So what if you made a composite key for the Serial number? It consists of a "Series" Number and a "Item" Number part. Each of those opeartions would just add a new series number, with Item Numbers related to it comming later. That way you could leave picking the Series Part to the DBMS, during the actuall insert. And defer to addition of the actually Items to whenever the user is ready.

Christopher
  • 9,634
  • 2
  • 17
  • 31
  • I'm not allowed to mess with the database, so I can't really add any new columns to the table. Also, the generated SNs are based on the entire list, where it iterates from the smallest number and generates new numbers wherever there's a gap. Anyway, I'm looking to solve this either via the Web API or the Winform app, and not the SQL database. (This is my bad -I should've been more specific in my original post.) – Daniel Pavlovsky Dec 22 '19 at 03:12
  • @DanielPavlovsky This is not solveable without modifying the Database, one way or the other. You are dealing with Concurrency on the DB level. Only in the DB can you fix that reliably. Primary Key Maintenance is always done exclusively in the DB. Any other place will just cause issues. – Christopher Dec 22 '19 at 15:26
  • It seems like one can utilize concurrent queuing at the Web API level. The POST is dependent upon the GET, so whichever client is "first in line" would be making the DB call, and regardless of whether they end up committing those numbers to the db, the next person in the queue would be using the SNs calculated from the previous client (instead of GET) in order to calculate their list -a kind of "recursive queue" where, again, the first in line is making the DB GET call, and every subsequent client in the queue would be using info gleaned from the previous client. – Daniel Pavlovsky Dec 22 '19 at 17:31