In this related question, I asked how I could make sure that I don't try to insert the same data into a database twice. However, I am now concerned about the more general case.
Specifically, I am concerned about any database which I access asynchronously over a potentially unreliable communications medium such that a transaction could succeed but due to failure at just the wrong time I never find out about the success and don't know if I need to retry the operation later or not. The idea is that there is some transaction I need to perform, and locally on failure due to network conditions I have to keep trying until it succeeds.
By unreliable this could be anything from an internet connection where the return packet fails to reach me, all the way down to a local machine in which I did a local call, but my program was killed after the transaction was started but before the reply was received (Linux's OOM killer comes to mind here.)
To summarize my previous question, if I tightly constraint what I am doing to only inserting new rows with some unique data in it, I can avoid re-inserting those rows by constructing a query in which I first try to select the new data and don't insert if I find it.
However, without these constraints I am at a loss as to how to protect against "silent success". If I need to arbitrarily create tables, update values in existing tables, delete from those tables, and even drop tables, how can I make sure I don't do something that has already been done? It seems like this problem would exist in all databases which are asynchronous unless they have been carefully designed using for example the usage case I had in my previous question. Yet databases are so widespread and I have never heard anybody mention this problem or how to deal with it that I can only conclude that one of three things are true:
- The problem exists, but it is not widely known or swept under the rug as it is essentially a race condition that may be rarely seen, and when it does occur may be undentified due to unreproducability.
- Few people do anything interesting enough for critical applications to need such a feature, and thus any such applications are constrained as in my previous question so as to not need arbitrary protection.
- There is a known solution, but perhaps due to obscurity or due to not being familiar with terminology I just have not found what it is.
If possible I am interested in the most general case, not a specific SQL server, as what I am doing is basically writing a generic API that is not specific to any particular server. However, if there are certain servers that handle this better than others, or those that don't, I am interested in knowning that as well.