Hard inventory management with firebird and/or mongodb

By linux10000 ·
I've been thinking to migrate my erp(java currently) from rdbms(firebird currently) to nosql (mongoDB likely) and i trying to antecipate some issues. I'm trying to make a "failproof" inventory control which never let a qty's item be smaller than 0. Offcourse i still get "ACID bias" which i need to left in order to complete this task. The goal is update inventory qty of all order's items when approve the order. For that, 2 problems can occur which currently are resolved with a trigger.

1.problem: order gets 2 items (ball = qty 2 and chair = qty 1) and there is no enough qty in inventory (ball = qty 1 and chair = qty 0) to complete the order. Once i trigger the command do complete the order, a loop is executed to decrease qty in inventory and fails on the second item cause there is no enough qty. Consequently rollback operation is trigged and everybody go home happy.

2.problem: in one word, concurrency. suppose there is no trigger controling this operation but only "selects and if". User 1 query inventory qtys, see enough qty (ball = 2, chair = 1) e system allow complete the order. While the transaction is running user 2 query same invetory qtys but because first transaction is not complete yet second query see "old" qty (ball = 2, chair = 1) e system also lets complete order. Consequently, 1 qty of chair is descreased twice, but shold be descreased only once and fail on the second. Result: i got negative qty.

I saw some workarrounds with inventory reservation but i don't think is suitable for me. Please don't think about e-commerce or POS. Think about a huge warehouse full of 1 ton/$1milion coils each which are not possible to move phisically easly or previosly reserve (business rules) and 1000 facilities spread across to country with 10 sellers in each one. 10k clicks per minute.

I thought to isolate this feature in some RDBMS(firebird likely) but i don't think it can hold 10k clicks/minute. Does it?

Do anyone have any solution for this problem?

I've made a test: built a small spring boot app with firebird that decreases inventory quantity. In order to test concurrent users, i've used JMeter with 100 (the really goal is 10000) users with no delay between them and i record how many time operation really happened. The top number that i could get was 9 successful operations. I got 'deadlock, update conflicts with concurrent update....', 'SQL Error: 335544336, SQLState 40001'. I'm using firebird 3 (debian 9) with java 8 and hibernate 5.0.11.Final.

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Answers

Share your knowledge

Related Discussions

Related Forums