Comment by jongjong | Hacker News Reader

Comment by jongjong | Hacker News Reader

I'm a bit confused why OP says that because two-phase commits are not natively supported by various databases, that it's not a viable strategy. It's possible to implement 'two-phase commits' manually through a simple algorithm. You just need an extra field to keep track of whether or note the record is 'pending' or 'settled' and also some additional fields for creation date and settlement date. The main process just copies over all the records as 'pending' state then you have another parallel process/task which goes over all the pending records in order and changes them to 'settled' if some condition (e.g. some related records in different tables/collections also exist) is met. Records need to be settled in a particular order to protect from failure of the settlement process itself but it's not particularly difficult to implement. Also, you need good indexing for fast/efficient cross-table/collection lookups (you especially need indexing on date fields in order to quickly look up and settle records in the same order as they were inserted starting from the last settled record).

I did something like this a while ago; some operations in my system inserted multiple records into different collections (into a database which did not support atomic transactions) and I needed to guarantee that when a record existed in a particular collection in the 'settled' state that it would guarantee the existence of a matching record in a different collection. It was very reliable. I believe I didn't even end up needing to launch a separate process for the settlement logic since I was using Node.js, I just used an async task scheduled to run on an interval. The front end would just ignore records until they were in the settled state (but you could also show them as pending).

Another thing which really helps is using UUIDs are IDs as it guarantees idempotence so you don't have to worry about double-insertion in case of process failure/restart. You can just re-process some records that you may have already processed with no side effects; you just need to check that the error is an insertion conflict and continue.

If a transaction A adds 10 records, and they're currently being settled, with 5 of them being pending and 5 of them being settled, how do you ensure that a concurrent transaction B either sees all of them as settled, or none of them?

That's why the settlement order matters. If the records are settled in a specific order, you only need to check the last one (the last leg of the settlement); if that one is settled then you know all the others are settled.

For example, if you settle based on creation timestamps then you know that if a record is settled, then all records with a smaller timestamp within the same collection/table are settled too. You can also implement a similar guarantee if dealing with multiple collections/tables.

For example, if you create a 'Company' record along with a 'User' record which points to the 'companyId' and you want them to be settled atomically as a pair, then in your settlement process/logic, you could ensure that you always mark the company as settled first (and wait to get a success response from that DB query) before you then mark the user as settled - In this case, you only need to check that the user is settled to know that the associated company is also settled since your settlement logic guarantees that it's not possible for the user record to be settled before its associated company since you check that the company settlement query was successful before moving onto the related user.

In this case, you can think of settlement as having two legs; the first leg is the company record, the second leg is the user record. If the second leg of the settlement fails, the user will stay in a pending state which may cause your settlement script to reprocess (or re-check) the first leg (the related company record) since, if the last leg of the settlement failed, it will treat it as if the entire transaction failed and that's fine.

How did you handle cleaning up aborted / rolled back transactions after binaries restarted? Or did you just let the pending records hang around forever.

When a node rejoins after a restart, have all other nodes drop any pending transactions initiated by the node in question. I implemented something similar for a distributed filesystem and it worked pretty well.

I get what you're saying. This was not an issue. If one or more of the records in the 'transaction' failed to be inserted, then none of the other records inserted as part of this transaction would be able to be converted into the settled state. As part of my settlement script, there was logic which looked at creation timestamps and would change the state to 'failed' if they had been pending for too long (which was only a few minutes). My script settled records created by different accounts in parallel (though for each account, sequentially based on timestamp) so as not to allow a user to intentionally make incomplete transactions in order to hold up settlement and prevent the script from settling other users' records. A user could only delay settlement of their own records.

Because I used UUID as IDs and they were created on the front end, the user in my app could potentially re-submit the form (click submit again after seeing the 'Unexpected connection error please try again...' message); this represented a second chance to complete the transaction. Those records which were already successfully inserted into the db the last time would be ignored the second time (due to ID conflict) and those which had failed the first time and not been inserted into the db would then be inserted as pending; then the settlement script could complete its job and settle on the next interval.

Reply on news.ycombinator.com