Multi user access design for a large database - interesting challenge
I'd really appreciate some thoughts....
I have a complex dataset/dbase containing possibly hundreds of integer fields stored. This data is financial based. It's viewed and manipulated via a GUI delivered via a SaaS web app.
I have two types of users;
1) ones who can consume the data and create financial simulations and get subsequent forecasts, and
2) another type of user who can do the above and also make core changes to chunks of the database (we'll call them models) and these users need to be able to pass the models back and forwards to each user of the same type
To create a forecast/simulation, a user would modify several database fields via the GUI, and then click run. Its important for this simulation to be accurate, that the order of the fields modified are sent into the processing queue in the correct order. There is no time limit for the user to run the simulation but if any other user touches any of the same fields that simulation would be inaccurate as the data would have changed before they clicked run.
Calculation accuracy is dependent on the global data being up to date, changes to the financial models impact the accuracy of the calculations and any one change anywhere in the database, may impact users who are also performing a forecast against that data at the same time.
Where two users submit a simulation at the same time, it's important that each users 'job' is submitted in the correct order, and not a race depending on bandwidth, processing etc.
I want to avoid database deadlocks, there'll be about 100 users mixed between the user types mentioned above