I'm building a device reservation system, and was instructed to do so via Microsoft Sharepoint (to save on server hassle / cost). As such, I plan to create a daemon consuming the MS Graph API to programmatically update some Excel workbook, which will contain individual reservations (simplified example below):
| Person | Device |
|---|---|
| Alice | 1 |
| Bob | 2 |
| 3 |
However, I'm not sure how I'll address data races, since SharePoint isn't intended to work as a classic database. To illustrate this point:
- Both Charlie and Eve read the workbook 'database', and sees that device 3 is available for reservation.
- Charlie clicks the 'reserve' button, and his name goes to cell A3.
- According to Eve, device 3 is still not reserved. She clicks the 'reserve' button.
- Charlie's name gets overwritten.
I'm trying to avoid point #4 from happening, while still sticking to the desired remote Excel / Graph API combination. However, I'm struggling to come up with a solution given what I've read up on in the documentation. My thoughts have been:
- Check out and check in the 'database' drive file. However, this introduces unnecessary, potentially long wait times for the 100+ other people who may be reserving the devices.
- Protect / unprotect the workbook. Yet there's another relatively large drawback — as far as I understand from the documentation, the Graph API only supports protecting entire sheets, so this would require the dynamic creation / deletion of perhaps dozens of workbook worksheets, and lead to a rather complex 'database' structure.
Are there any other viable methods to dealing with potential user data writing races, without locking all the data? Or maybe some Graph API route that I'm overlooking?
