A chemical reagent ERP batch inventory concurrent processing method and system

Abstract

The application discloses a chemical reagent ERP batch inventory concurrent processing method and system, and belongs to the technical field of enterprise resource planning and warehouse management. The method routes all batch inventory changes to the inventory management interface; obtains an application layer mutual exclusion lock in ascending order of product specifications; applies an update lock and a row lock to the batch inventory row and writes a flow in the transaction; captures a database deadlock and retries with an exponential backoff; adopts absolute quantity adjustment semantics for inventory checking; atomically completes the out-of-warehouse and in-warehouse operations in a single transaction for re-warehouse; and atomically executes the specification sub-packaging verification after the net weight ratio. The application reduces the concurrent over-selling and deadlock failure rate, unifies the inventory checking, re-warehouse and sub-packaging semantics, and is suitable for the ERP scene of the multi-batch inventory of chemical reagents.

Description

Technical Field

[0001] This invention relates to the field of enterprise resource planning and warehouse management technology, specifically to a method and system for concurrent batch inventory processing of chemical reagents in an ERP system. Background Technology

[0002] Chemical reagent ERP systems need to manage inventory across multiple specifications, batches (COAs), and storage locations. Transactions such as purchasing receipts, sales shipments, inventory checks, transfers, production material requisitions, and specification-specification repackaging are concurrently written to the batch inventory table, which can easily lead to overselling, inconsistencies in transaction logs, and SQL Server 1205 deadlock.

[0003] In existing technologies, inventory change codes are scattered across multiple services, lacking unified concurrency control; inventory checks often use simple increase / decrease quantities rather than absolute quantity semantics; if a transfer is divided into two independent inbound / outbound operations, failure midway will lead to a split between the accounting records and the actual inventory.

[0004] Therefore, a concurrent batch inventory processing technology solution is needed for chemical reagent ERP systems. Summary of the Invention

[0005] This invention aims to solve problems such as concurrency consistency, deadlock failure, and inconsistent semantics in inventory transfer and reassembly.

[0006] This invention provides a concurrent batch inventory processing method for chemical reagent ERP systems, comprising: uniformly routing all batch inventory change requests to the inventory management facade service; extracting product specification identifiers, sorting them in ascending order, and then sequentially acquiring application-layer mutex locks; applying update locks and row locks to the target batch inventory rows within a database transaction, executing inventory changes, and writing inbound / outbound logs and specification-time point-in-time inventory snapshots; when a database deadlock error is captured, retrying the transaction according to an exponential backoff strategy; for inventory checks, converting the difference between the target absolute inventory quantity and the current inventory quantity into standard inbound / outbound processes; for transfer requests, sequentially executing source batch outbound and target location inbound processes within a single transaction and inheriting source batch attributes; for specification repackaging requests, verifying the net weight ratio, and then atomically executing outbound and inbound processes under single-lock protection.

[0007] This invention also provides a concurrent batch inventory processing system for chemical reagents in an ERP system, including an inventory management facade module, an ordered lock module, a transaction execution module, an inventory check module, a transfer module, and a repackaging module.

[0008] Compared with existing technologies, this invention reduces the failure rate of concurrent consistency errors and deadlocks, and unifies the semantics of database disking, database migration, and data packaging. Attached Figure Description

[0009] Figure 1 This is a schematic diagram of the system architecture; Figure 2 Here is a flowchart of ordered locks and transaction execution; Figure 3Flowchart for adjusting absolute inventory count; Figure 4 This is a flowchart of the warehouse transfer and repackaging process. Detailed Implementation

[0010] like Figure 1 As shown, the ERP business layer calls the inventory management facade service, which relies on the ordered lock provider and the relational database.

[0011] like Figure 2 As shown, after deduplicating and sorting productSpecId in ascending order, application-layer locks are acquired sequentially. The Redis implementation uses SET NX EX, Lua release, and watchdog renewal. In-transaction UPDLOCK+ROWLOCK is used. A 1205 exponential backoff retry is implemented. ES synchronization is asynchronous outside of transactions.

[0012] The disk index `AdjustToAbsoluteAsync` with `diff = absolute - old` is used to convert the data for both inbound and outbound operations.

[0013] TransferAsync performs a single-transaction outbound and inbound operation, inheriting batch attributes. RepackAsync performs atomic packaging after net weight ratio verification.

[0014] Produce ResolvePickBatchCoaAsync dual-ID path FIFO batches.

[0015] Test: Consistency error rate [To be filled] % → [To be filled]%.

Claims

1. A method for concurrent batch inventory processing of chemical reagents in an ERP system, characterized in that, include: All batch inventory change requests are routed to the inventory management facade service. Extract one or more product specification identifiers involved in the request, sort them in ascending order, and then acquire the application layer mutex locks sequentially. Within the database transaction, apply update locks and row locks to the target batch of inventory rows, perform inventory quantity changes, and write inbound / outbound logs and a snapshot of the inventory at the specified time. When a database deadlock error is captured, the transaction is retried using an exponential backoff strategy.

2. The method according to claim 1, characterized in that, The application-layer mutex lock includes a local semaphore lock or a Redis-based SET NX EX distributed lock. When the distributed lock is released, a Lua script verifies the lock token for matching before deleting it.

3. The method according to claim 2, characterized in that, The distributed lock initiates a watchdog timer task to renew the lock key during the lock holding period.

4. The method according to claim 1, characterized in that, The waiting time for the exponential backoff strategy is based on milliseconds multiplied by 2 raised to the power of attempt minus 1, plus random jitter.

5. The method according to claim 1, characterized in that, For inventory checks, read the current inventory quantity of the target batch, calculate the difference between the target absolute inventory quantity and the current inventory quantity, and convert the difference into a standard inbound or outbound operation.

6. The method according to claim 1, characterized in that, For a data transfer request, the following steps are executed sequentially within a single database transaction: the source batch is sent out; the target location is sent in; and the batch number, production date, expiration date, and batch cost of the source batch are inherited.

7. The method according to claim 1, characterized in that, For specification repackaging requests, verify that the source specification and the target specification belong to the same product, and verify the ratio of the source quantity to the target quantity based on the specification net weight and the preset tolerance percentage; after the verification is passed, lock the source specification identifier and the target specification identifier in ascending order, and execute the source batch outbound and target specification inbound within a single transaction.

8. The method according to claim 1, characterized in that, For production material requisition requests, if the input identifier is set to query by batch inventory primary key, if the specifications do not match, the batch list is queried by batch information identifier and material specification identifier, and the first batch with available quantity that meets the material requisition quantity is selected in ascending order of production date.

9. The method according to claim 1, characterized in that, Search engine index synchronization and cache updates are executed asynchronously after the database transaction is successfully committed, and do not participate in the transaction.

10. The method according to claim 1, characterized in that, The inventory management gateway service provides entry points for standard inbound / outbound, batch inbound / outbound, transfer, inventory check, absolute quantity adjustment, and specification repackaging. Business modules are prohibited from directly updating the batch inventory table.

Images