Avoiding Duplication and Race Conditions During Batch Import in WMS Applications

When working with a Warehouse Management System (WMS), one common challenge is ensuring that concurrent batch import processes run by multiple users do not cause data duplication in the database. This becomes even more critical when dealing with identifiers such as a box code, which must be strictly unique.

Case Study: Inbound XLSX Import

Suppose we have a feature that allows users to import an .xlsx file containing multiple boxes, products, and serial numbers. This is essentially a batch import process initiated by users.

Example of imported data:

CSV
copy
boxCode,productCode,serialNumber
BOX001,P001,SN001
BOX001,P002,SN002
BOX002,P003,SN003

Problems arise when:

1. Two users or two threads attempt to import data with the same boxCode.
2. These processes run concurrently.
3. Without proper protection, both processes may successfully save BOX001, resulting in duplicate data.

Race Condition Example

Consider the following code:

JAVA
copy
for (String boxCode : boxCodes) {
    if (!boxRepository.existsByBoxCode(boxCode)) {
        boxRepository.save(new Box(boxCode));
    }
}

If two users process the same batch at the same time, both can pass the existsByBoxCode() check before either completes the save() operation—causing supposedly unique data to be inserted twice.

Solution: Redis Lock per Box

To solve this issue, we can use a Redis distributed lock (via Redisson). The idea is simple:

1. Extract all boxCode values from the import file.
2. Acquire a Redis lock for each boxCode.
3. If a lock cannot be acquired (because another process already holds it), immediately throw an error.
4. Once processing is complete, release all acquired locks.

Spring Boot Implementation

JAVA
copy
@Transactional
public List<Inbound> importXlsx(InboundBulkDTO body, User user) throws IOException {
    List<InboundImportXLSX> inboundImportXLSXList = this.extractXlsx(body);
    Set<String> boxCodes = new HashSet<>(inboundImportXLSXList.stream()
        .map(InboundImportXLSX::getBoxCode)
        .distinct()
        .toList());
    Map<String, RLock> boxLocks = new HashMap<>();

    try {
        for(String boxCode : boxCodes) {
            RLock boxLock = redissonClient.getLock(
                String.format("lock:box:%s:%s", body.getWarehouseId(), boxCode));
            boolean isLocked = boxLock.tryLock(0, 30, TimeUnit.SECONDS);
            if(!isLocked)
                throw new BadRequestException(String.format("Box %s already on process!", boxCode));
            boxLocks.put(boxCode, boxLock);
        }
        List<Inbound> inbounds = this.processImport(inboundImportXLSXList, user, body.getWarehouseId());
        return inbounds;
    } catch (InterruptedException e) {
        throw new RuntimeException(e);
    } finally {
        boxLocks.values().forEach(boxLock -> {
            if (boxLock.isHeldByCurrentThread()) boxLock.unlock();
        });
    }
}

Benefits of This Approach

✅ Thread-safe: Concurrent imports by multiple users remain safe.

✅ Atomic: If locking fails for any box, the entire process is aborted.

✅ Transactional: With the @Transactional annotation, all changes are rolled back automatically on error.

✅ Reusable: Redis-based locks can be reused for other processes such as inbound, outbound, pick-pack, and more.

Additional Notes

• Always provide a timeout to tryLock to avoid deadlocks.
• Redis locks will automatically expire after a certain time if the thread crashes.
• Use clear and specific Redis key prefixes (e.g., lock:warehouse:box-code).

Closing

With this approach, batch import processes become far more robust. Redis acts as a virtual gatekeeper, ensuring that no box is imported more than once by different users at the same time.

If you’re working on large-scale distribution systems like a WMS, this technique is definitely worth mastering.

"Better safe than sorry. Lock it before you drop it." 🚀