Control of high demand stock with firebird and / or mongodb

Question

Control of high demand stock with firebird and / or mongodb

Navigation

#1 by (2 votes)
#2 by (1 votes)

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 possible problems. The goal is to build a control that will never let the quantity of the item become negative. I still have the bias of ACID, which makes this migration difficult. I have been able to solve all the problems of the rest of the system, but there are 2 situations in stock control that are now solved with triggers.

Problem 1

An order with 2 items (quantity ball = 2 and chair quantity = 1) and there is not enough quantity in the stock (ball amount = 1 and chair = 0) to complete the order stock movement. As soon as I try to move the order stocks, I make a loop to lower the stock quantity of each item, one by one. Consequently, there would be a failure in the drive for not having enough stock and the rollback would happen. In the case of MongoDB, each 'document' would be equivalent to a quantity 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 select s returning the current quantity and a if to say whether it has quantity or not, item by item. The first user sees the quantity of stock sufficient (quantity ball = 2, chair quantity = 1) and the system releases inventory movement and order completion. But while the first user transaction is running, a second user tries to make the same move, but since the first transaction has not yet ended, the second user also sees quantity = 2, chair quantity = 1 when querying inventory, and the system also releases (erroneously) the inventory movement. Consequently, the chair would be decremented 2 times when it could only be 1 time, and I end up with negative stock.

I saw some alternatives as a stock reserve but I do not think they are good for me. Please do not think about an e-commerce or POS. Think of a large warehouse full of reels weighing 1 tonne and costing 1 million reais each, and can not be moved easily or stocked (because of business rules). In addition, with 1000 branches scattered throughout the country with 10 sellers in 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 do not think it can handle ten thousand clicks per minute.

I got tested: I ran 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 move, with no gap between them. I managed a maximum of 9 concurrent operations with a successful inventory movement of the same items. Firebird returns the error:

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

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

I've been reading and thinking, and using apache Kafka Transaction Streams with MongoDB? Will Kafka be able to queue the requisitions so that I can test the quantity of all the items of the order before moving, and only move if there is stock. And, 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 same, but considering this result I got with JMeter?

java nosql mongodb firebird

asked by anonymous 15.01.2018 / 16:30

2 answers

To serve ExampleUnitTest and ExampleInstrumentedTest in Android Studio? FileUpload does not load the listener in JSF 2.2

score 2 · Answer 1

Before changing Firebird, I would suggest trying a solution using another transactional database such as MariaDB. For you to have 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.

I personally do not consider Firebird to be good at executing large transactions on the volume you describe because it normally stores all the data in a single FDB file. This makes it very vulnerable to file fragmentation problems on the 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 in and it can be done with multiple files, but I think putting MariaDB or PostgreSQL in place is a lot easier.

In my opinion (I may be wrong here, as I do not know it in depth enough), Firebird's biggest advantage is the ability to copy the entire database as one or more files to another place 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 loading all your data together. But this advantage is not something that seems to me to be of any value in your specific case.

Still keeping up with 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 that the same tuple is 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 way to avoid deadlocks.

The NoSQL paradigm sacrifices consistency to gain performance. NoSQL has the concept of eventual consistency, where a newly updated data may not yet be available to all its users, but will become available given the time required to do so. However, in your scenario the strong consistency seems to be a sacred requirement and eventual consistency would not serve you, which is an indication that NoSQL may not be the output you are looking for.

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 dismiss the transactional as much as possible. A Data Warehouse solution is also useful if you think about it.

Another pivot would be the denormalization of some of the more "hot" tables, so that transactions involve manipulating fewer rows into fewer tables at the cost of redundancies and increased disk space consumption (and this would be mitigated in other ways ).

As for Kafka, I do not know what to say.

score 1 · Answer 2

Let's go to Kafka.

Maybe it's coming late but ....

Kafka can deal with 100,000 tpm there.

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

Let's pro flow, Kafka, will not have direct contact with MongoDB, someone will produce a json, which will get into a kafka topic and will stay there.

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

One for when the product is in stock, so you would put in a unique topic for orders that the product has in stock.

Other for when the order has no product in stock.

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

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

29.08.2018 / 03:52