I am looking into the possibility of using AirTable as a back end data repository for some of our data. In order for this to work, I'd have various event triggers accepting data fields and using that data to create or update an Airtable base/table using REST API calls. The goal is to replace some existing back-end that is stored in a SQL Server database.
One aspect that I'm not sure to handle is preventing concurrent changes. If, for example, I have multiple changes happening to a record that could possibly be processed at near the same time, I need to make sure that the newest value "wins".
The way this works in the current service is as follows:
- Before sending an updated value to the back-end via its API, a service gets the current value and compares the current value to the new value and only calls the API if one or more fields changed
- As part of getting the current version, the service gets the version value of the row (implemented as a rowversion in the SQL database, but I've seen similar version/revision in many other REST APIs)
- When a field has changed, the update gets sent to the back-end along with the row version
- The back-end rejects the update if the version in the request is NOT the current version. In other words, if someone else came in and update the field AFTER the current values were retrieved, but BEFORE the update was received, then the update will fail with a error that denotes version conflict (e.g. HTTP 409)
- In the event of a conflict error, the client needs to re-retrieve the current version of the record, and again do its checks to see if there was an update. If there is still an update to make, it can then retry the update with the new version.
I realize that Airtable doesn't have direct analogs to everything that can be done with a SQL database. I'm wondering what the best practice would be with the Airtable API if I want to manage concurrent modification scenarios like this one.
