High demand stock control with Firebird and/or mongodb

Asked

Viewed 786 times

2

I’m thinking of migrating my ERP (currently in java) from a relational database (currently in Firebird) to a Nosql (probably Mongodb). I am planning and trying to anticipate the possible problems. The goal is to build a control that never let the quantity of the item become negative. I still have the ACID bias, which makes this migration difficult. I managed to solve all problems of the rest of the system, but there are still 2 situations in the stock control that today are solved with triggers.

Problem 1

One order with 2 items (quantity ball = 2 and quantity chair = 1) and there is not enough quantity in the stock (quantity ball = 1 and chair = 0) to complete the order stock move. As soon as I try to move the order stocks, I run a loop to download the stock amount of each item, one by one. Consequently, there would be a failure in the drive by not having enough stock and the rollback would happen. In the case of Mongodb, each 'document' would be equivalent to an amount of an item to decrease, that is, there is no transaction between documents in Mongodb.

Problem 2

In a word: competition. Suppose there are no triggers controlling this scenario, only selectreturning the current amount and a if to say whether there is quantity or not, item by item. The first user sees the quantity of sufficient stock (ball quantity = 2, chair quantity = 1) and the system releases the stock movement and the order completion. But while the first user transaction is running, a second user tries to make the same move, but as the first transaction has not yet finished, the second user also sees ball quantity = 2, chair quantity = 1 when consulting the stock, and the system also releases (erroneously) the stock movement. Consequently, the chair would be lowered twice when it could only be 1 time, and I end up with negative stock.

I saw some alternatives as stock reserve but I don’t think they fit for me. Please don’t think of an e-commerce or PDV. Think of a big warehouse full of reels that weigh 1 ton and cost 1 million reais each, and that can not be moved easily nor have stock reserved (because of business rules). In addition, with 1000 branches spread across the country with 10 sellers each trying to move the same items. Think of ten thousand clicks per minute.

Initially I thought about isolating only the stock movement function in Firebird, but I think it can’t handle ten thousand clicks per minute.

I even made a test: I made an application with Spring Boot and Firebird to make these moves. To test this competition demand, I used Jmeter and put 100 users (the goal is 10000) who try to make this movement, without starting interval between them. I managed at most 9 concurrent operations successfully moving stock of the same items. Firebird returns error:

'deadlock, update conflicts with concurrent update....', 'SQL Error: 335544336, SQLState 40001'

I am using Firebird 3, Debian 9, Java 8 and Hibernate 5.0.11.final.

I’ve been reading and thinking, and if I use Apache Kafka Transaction Streams with Mongodb? Will Kafka can queue the requisitions so that I can test the quantity of all the order items before moving, and only move if there is stock. And only then process the next move request. Is this possible?

How to build this system/functionality with Nosql meeting these requirements? Or how to build with Firebird, but considering the result I got with Jmeter?

java mongodb firebird nosql microservices
  • 1

    To me it seems the kind of problem that gets better in SQL. If you need some extra fields that need features Nosql could use a base that accepts this, such as Postgresql with its type "JSONB". This bank still has the advantage of having the performance you need.

  • Reading your requirements/tests I remembered this one page of the Mongodb documentation. I agree with Victor Stafusa’s considerations in his answer, it is to evaluate a hybrid approach, because this documentation solution should add a lot of work in the end. Now if competition is critical as well as consistency, take a look at the proposal there.

2 answers

2

Before switching Firebird, I would suggest trying a solution using another transactional database like Mariadb. To give you an idea, Mariadb is used by wikipedia which has a monstrous volume of transactions (although wikipedia is far from completely transactional). Another suggestion would be Postgresql.

Personally I do not consider Firebird good to perform large transactions on the volume you describe because it usually stores all the data in a single FDB file. This way, it gets quite vulnerable to file fragmentation problems on disk and has to deal with the trade-off between saving space and optimizing performance. There are a lot of things that can be tuned into this and it is possible to make it work with multiple files, but I think putting Mariadb or Postgresql in place turns out to be a lot easier.

In my view (I may be wrong here, because I don’t know deep enough), the biggest advantage of Firebird is the ability to copy the entire database as one or more files to somewhere else easily, something very useful for various desktop applications that have to be copied easily from one folder to another, or to pen drives or to Cds carrying all their data together. But that advantage is not something that seems to me to be of any value in your specific case.

Still staying in the transactional database, you could try an optimistic lock-based solution. This would increase the competition of your database based on the principle that the probability of the same tuple being modified by two processes simultaneously is low, and that even if this occurs, this condition is detectable and can be treated. This can also be a good exit to avoid deadlocks.

The Nosql paradigm is something that sacrifices consistency to gain performance. Nosql has the concept of eventual consistency, where a newly updated data may not yet be available to all of its users, but it will become available given the time needed to do so. However, in your scenario strong consistency seems to be a sacred requirement and eventual consistency would not suit you, which is an indication that Nosql may not be the way out you seek.

If you still want a Nosql-based solution, think of a hybrid solution where part of the system is transactional and part is Nosql. The idea here would be to relieve the transactional as much as possible. A solution based on Data Warehouse it is also useful to think in this sense.

Another way out would be the denormalization of some more "hot" tables, so that transactions involve the manipulation of fewer rows in fewer tables, at the cost of having redundancies and greater disk space consumption (and this would be mitigated in other ways).

As for Kafka, I don’t know what to say.

  • Thank you so much for the answer. I used optimistic lock (direct timestamping at the bank), but still gave deadlock. Of course I pushed the bar a lot: I programmed every time a user moves the stock, decreases 1 Qtde of all items. Also, as I said, I tried with 100 users Imultaneos in Jmeter. About testing with Mariadb and Postgree, I think q are valid tests. But I’d like to stick to the maximum in our field, J H q would have to change. Therefore also the idea of Kafka, pq would use mongodb and add Kafka to cooperate. But q vc said it is much more with the idea of polyglot system.

  • I like the Firebird pq Jah I know it well, by the question q vc commented on portability of the base, and tb pq is easy to embark it, q would take me quite a microservice even. It is possible to ship the mariadb and tb port so easily the database?

  • 1

    @Raphaelstefani It’s possible to ship Mariadb and Postgresql, but it’s a fairly complicated thing to do. Their paradigm is different, because to use these databases in production the way you do, the ideal is for them to be installed on dedicated servers. So instead of boarding, you do full backups and portability would be reduced to creating an entire HD/Storage ISO/backup. There are quite sophisticated and specialized backup tools for both.

1

Let’s talk about Kafka.

Maybe I’m coming in late but....

Kafka can handle 100,000 tpm.

So it works with very high volumes quiet, of course it goes much of the infra that you have available to it, since at each request it opens a number of operating system files, stores all these requests in a certain number of days, with it swells the machine’s disk and everything else.

Let’s go to the stream, Kafka, will not have direct contact with Mongodb, someone will produce a json, which will enter a Kafka theme and will stay there.

You would have to do a stream that looks at this issue of the availability of an item, it’s done, in theory, out of Kafka, so you take JSON, validate the items, see if you have in stock, then you could format two messages based on the result of this stream validation.

One for when there is the product in stock, then you would put in a unique theme for orders whose product has in stock.

Other for when the order has no product in stock.

With this, you would make consumers who keep looking at these response topics and taking the actions, those who have product in stock, make the move, those who do not have, follow their workflow.

I wrote here a post about how Kafka works, maybe it helps with the terms and the most basic architectural issues.

What is this Apache Kafka?

hug

Browser other questions tagged java mongodb firebird nosql microservices

You are not signed in. Login or sign up in order to post.