Poor man´s transaction tracking

Ask the community
,
1

I need to implement some kind of (poor man´s) Transaction Tracking to keep Wix (Mongo) DB in a consistent state. Need to do this:

  1. save data in 2 collections with a Main-Detail (1-n) relationship
  2. update a denormalised, existing row in a 3-rd collection per Detail Row.
    Example:
    Suppose we keep track of items ordered. Main collection would hold CustomerId, Date, etc. Detail Rows would keep Items ordered, VAT, Number, Price, etc
    The 3-rd Collection would decrease Stock per Item (thus denormalised).

I could do this:

  1. (bulk) save Detail data to Log
  2. save Main data to Log

If no errors, then “commit”, meaning copy from log to real collections. And now comes the problem:

What if during this (server side) action an error occurs? I could try to set flags (“notValid”) inside the rows and try to revert the decreasing of the stock in the denormalised 3-rd collection. But what if that fails?

Has anyone ever done this?

2

I might not have understood you correctly but I think I have done something like this.

I have a Products Database ’ productsDB ’ which hold all the product information such as product name, SKU, images, base price.

Another database I have is ’ stockCenter ’ in which I have the stock for all the products and they are linked to the products database via the unique SKU for each.

A visitor can add items to their ’ cart ’ database. The cart database stores all the information including SKU and the userId of the visitor/member.

Once a customer makes a purchase a webhook is sent via Stripe. This webhook contains the userId of the visitor/member which I had sent in the initial api call.

After this the site queries the ‘cart’ database to find all items with the matching userId from the webhook and uses each of the items SKU (items.forEach) to find the relevant item in the stock database and reduce the quantity.

Then the webhook moves the item from the cart database to the ’ purchasedItems ’ database (move the item and then delete it from the cart)

I have done a lot of testing and until now there has been no issue in processing orders like this from the backend.

3

Thanks Shan. It´s not entirely an answer to my question, but your last sentence gave me an inkling towards how far to stretch it. Prob. a two-phase commit will do.
I will try to design it like this:

  1. write order to log
  2. if no errors in writing to log, copy data from log to collections, but, …
  3. with a flag “itHasAllGoneHorriblyWrong” set to true
  4. if errors, flag is in correct position
  5. if no errors, update flag and pray

In short, writing a complex transaction will take 3 moments:
a) write to log
b) copy from log to collection, assuming it went wrong
c) if it did not, update flags