4

If I have a distributed database that is eventually consistent, what happens if I have something like a site where I'm selling products and I'm changing the price of an item?

If a product has price X and I update it to price Y, some versions of the database might still show price X. If a customer goes to check out, are there strategies to ensure they are actually getting the most up to date price, so they don't get charged the incorrect value?

At some point, don't I need to do some integrity checking of the data to ensure the most up to date value is being used?

Jeff Storey
  • 56,312
  • 72
  • 233
  • 406
  • are you using a second replica database for load balancing? – Dan Kanze Mar 21 '12 at 02:59
  • @DanKanze, yes the replica would be for load balancing – Jeff Storey Mar 21 '12 at 03:01
  • @DanKanze Another possibility is that the database would be used for localtiy. So for example, if I'm logging in from location X, I will hit a database that's close to me. – Jeff Storey Mar 21 '12 at 03:06
  • Isn't this what had been described in Consistency's definition ? I read the [wiki](http://en.wikipedia.org/wiki/Consistency_model) and find your question is a typical situation of database system technology. I don't know much about dbs, but I know this is what many databases devote to, in production, it's something about architecture. – Reorx Mar 21 '12 at 03:08
  • @Reorx Eventual consistency is a model in which not all copies of the database are guaranteed to be up to date a given time. So I'm wondering how this problem is addressed when using an eventually consistent model. – Jeff Storey Mar 21 '12 at 03:09

3 Answers3

4

We used to have a custom script which would track the replication lag, but that was a few years ago. Since then, we have moved to the heartbeat monitor provided by Percona Toolkit.

You may also want to consider adding the product they selected to their session, so if the price does change before they checkout, they won't get sticker shock.

Mike Purcell
  • 19,847
  • 10
  • 52
  • 89
  • @MikePurcell, can you expand on what you mean by "consider adding the product they selected to their session, so if the price does change...". How can I verify if the price actually changed? I could try to verify if the data is stale (maybe using something like the toolkit you mentioned above) and then if it is stale, consult the master for the correct value? Is that what you are suggesting? – Jeff Storey Mar 21 '12 at 03:53
  • Close, what I would suggest is that you try to show the most up-to-date prices as possible via the heartbeat monitor (and whatever other methods can be employed), but when a user selects a product for purchase, freeze that product's price in time, by adding it to their current session. This way, the price will be consistent for the user no matter what, and if they remove the item from their cart (session), you can force a page refresh which will show the latest price, in case they select it again. – Mike Purcell Mar 21 '12 at 03:59
  • Makes sense. But let's say I put a product in my cart and it is $1. Now the site owner realized the price should be $10. When I go to checkout, I should be notified that that the price is $10 and no longer $1. Would your solution address this? – Jeff Storey Mar 21 '12 at 04:01
  • Another alternative would be to use 2 databases - one for product prices in this example. That database would guarantee consistency. Since this database would have a relatively few number of writes, the performance hit shouldn't be too heavy. Then one database could have data that can tolerate eventual consistency, such as the product descriptions. Just a thought. Obviously I need to do some testing to see where my bottlenecks are, etc. – Jeff Storey Mar 21 '12 at 04:20
  • The price change from $1 to $10 is an obvious issue, and to be honest, I would change the price on the user, even mid-session. I guess it comes down to the level of tolerance with regards to price changes. Would adjusting a price 1-2% be worth losing a potential customer? – Mike Purcell Mar 21 '12 at 05:28
1

Your database should have a price, a next price, and a date/time when the price becomes effective. Then check the timestamp on the trandaction when the user indicates the order. You should then store that price with the transaction as effective from then on for that purchase.

Better, create a unique identifier for an item/price combination and record it when you display the catalog - then there won't be surprises compared to what you offered them. (of course with some reasonable timeout to prevent abuse.)

dkretz
  • 37,399
  • 13
  • 80
  • 138
  • @ledorfier, I'm not sure how this actually addresses the replication issue. If I change or add a next price, there is still no guarantee that it will be updated in the database from which the read is being made – Jeff Storey Mar 21 '12 at 03:05
  • You probably have some performance requirement for "eventually". Insert the price change before that. – dkretz Mar 21 '12 at 03:25
0

As Mike Purcell said: "You may also want to consider adding the product they selected to their session, so if the price does change before they checkout, they won't get sticker shock."

I think this is important for you because if you are in the middle of updating both and one finsihes but the other doesn't at the same time a purchase is being made the user may get trapped into the wrong price.

If you are updating your databases without bringing the whole site down for at least a minut or two to make your updates, I can't think of anyway to deal with this problem without running a script before each transaction makes a request to the payment gateway. Especially if your users are already in session.

You could do something like make a lookup for the item price in each database before a user fowards the request to the payment gateway, if they arent identical, redirect back to the product page?

If the updates are a second apart I could see this being a tangible solution.

Dan Kanze
  • 18,485
  • 28
  • 81
  • 134
  • Not sure the user will like being redirected back to the product page while going through the checkout process and may reduce in a drop in sales because people would jump the site and find another site which sells the same product. – Mike Purcell Mar 21 '12 at 03:46
  • I agree, I think that would be confusing to a user. – Jeff Storey Mar 21 '12 at 03:54
  • Yea that's true I didn't really think about that. – Dan Kanze Mar 21 '12 at 03:55