What structure and database storage method should I use for temporary storage that may be written concurrently?

Question

In working with a third-party API, I need to store some state information to help track a process. I'm wondering where and how I should store it.

Specifically, I have a multi-page Gravity Form, and using the hooks they provide, I'm submitting a POST request to an API once the first page is filled out, getting back a unique ID, and then submitting the fields from the second page to the API with a PATCH request. (There are good reasons for this seemingly complex flow.)

I have a unique ID for the form submission, and a unique ID for the API record that's created and then needs to be updated. What I'm looking for is the best way to connect that information in the database so that when the second page of the form is completed, I know which API record should be updated.

I've considered using the Transients API, since this is by definition a very temporary storage need, but since the data is not guaranteed to be there when I need it, I don't think it will work.

I'm leaning towards using an associative array (with the unique form ID as the key) with the Options API. My concern: if I have dozens or even hundreds of people filling out this form at the same time, is there a risk of race conditions or similar issues in writing to one option? Should I break this out into one option per form submission? Or create a separate table similar to the postmeta table?

Or am I overthinking this, and a single array in an option will be just fine?

Answer 1

I ran a JMeter load test with one hundred simultaneous visitors to see if it is practical to write an array to the wp_options table via the Options API, knowing the array needs to be updated for every visit.

This test confirmed that only a portion of the database updates happened in the way they need to, without overwriting other versions of the array. I expect the exact proportion of failures will vary with load, but since I need my state information to be reliably available, any failures make this a non-starter for me.

My plan going forward is to create a custom table similar to the postmeta table, but tying my unique form ID and unique API ID together rather than a post.