How to Prevent Server Crashes from Concurrent Excel Exports with a Queue

Introduction

The business requirement is to export large amounts of data from a MySQL database to Excel files. When many users trigger a full‑data export simultaneously, the combined database I/O and file‑stream I/O can severely degrade server performance or even crash the service. To mitigate this, the author proposes queuing export requests.

Business Relationship Definition

Three core entities are defined:

ExportQueue : a fixed‑size FIFO queue that holds ExportUser objects, provides methods to add users, query the queue, and blocks new submissions when the queue is full.

User information : represents the user who initiates an export.

Export class : defines the export method, runs asynchronously, and allows users to view or download the generated file.

ExportQueue Implementation

package com.example.system.config;

import com.example.system.api.domain.ExportUser;
import lombok.extern.slf4j.Slf4j;
import org.springframework.stereotype.Component;
import java.util.LinkedList;

@Slf4j
@Component
public class ExportQueue {
    private final int MAX_CAPACITY = 10; // queue capacity
    private LinkedList<ExportUser> queue = new LinkedList<>();

    public synchronized LinkedList<ExportUser> add(ExportUser sysUser) {
        while (queue.size() >= MAX_CAPACITY) {
            try {
                log.info("Current queue is full, waiting...");
                wait();
            } catch (InterruptedException e) {
                e.getMessage();
            }
        }
        queue.add(sysUser);
        log.info("Current queue size: " + queue.size());
        notifyAll();
        return queue;
    }

    public synchronized ExportUser getNextSysUser() {
        while (queue.isEmpty()) {
            try {
                wait();
            } catch (InterruptedException e) {
                e.printStackTrace();
            }
        }
        ExportUser sysUser = queue.remove();
        notifyAll(); // wake up waiting threads
        return sysUser;
    }
}

AbstractExport (EasyExcel based)

package com.example.system.config;

import cn.hutool.core.bean.BeanUtil;
import cn.hutool.core.util.PageUtil;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.example.system.api.domain.ExportUser;
import lombok.extern.slf4j.Slf4j;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.net.URLEncoder;
import java.util.List;

@Slf4j
public abstract class AbstractExport<T, K> {
    public abstract void export(ExportUser sysUser) throws InterruptedException;

    public abstract void export(HttpServletResponse response, int pageSize, T t, Class<K> k, String fileName) throws Exception;

    public abstract int countExport(T t);

    public abstract List<K> getExportDetail(T t);

    public void export(HttpServletResponse response, int pageSize, T t, Class<K> k, String fileName) throws Exception {
        ExcelWriter writer = null;
        try {
            writer = getExcelWriter(response, fileName);
            int total = this.countExport(t);
            int loopCount = PageUtil.totalPage(total, pageSize);
            BeanUtil.setProperty(t, "pageSize", pageSize);
            for (int i = 0; i < loopCount; i++) {
                BeanUtil.setProperty(t, "pageNum", PageUtil.getStart(i + 1, pageSize));
                List<K> kList = this.getExportDetail(t);
                WriteSheet writeSheet = EasyExcel.writerSheet(fileName).head(k).build();
                writer.write(kList, writeSheet);
            }
        } catch (Exception e) {
            String msg = "Export " + fileName + " exception";
            log.error(msg, e);
            throw new Exception(msg + e);
        } finally {
            if (writer != null) {
                writer.finish();
            }
        }
    }

    public ExcelWriter getExcelWriter(HttpServletResponse response, String fileName) throws IOException {
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        String fileNameUtf = URLEncoder.encode(fileName, "UTF-8").replaceAll("\\+", "%20");
        response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileNameUtf + ".xlsx");
        return EasyExcel.write(response.getOutputStream()).build();
    }

    public abstract void complexFillWithTable(T t, String fileName, HttpServletResponse response);
}

ExportImpl – Concrete Export Service

package com.example.system.service.impl;

import com.alibaba.excel.ExcelWriter;
import com.example.system.api.domain.ExportUser;
import com.example.system.config.AbstractExport;
import com.example.system.config.ExportQueue;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;

@Service
@Slf4j
public class ExportImpl extends AbstractExport {
    @Autowired
    private ExportQueue exportQueue;

    @Override
    public void export(ExportUser sysUser) throws InterruptedException {
        log.info("Export method executed");
        LinkedList<ExportUser> queue = exportQueue.add(sysUser);
        log.info("Export queue: " + queue);
        // Simulate export processing time
        Thread.sleep(20000);
        ExportUser nextSysUser = exportQueue.getNextSysUser();
        log.info("Next queued user after removal: " + nextSysUser.getUserName());
    }

    @Override
    public void export(HttpServletResponse response, int pageSize, Object o, Class k, String fileName) throws Exception {
        super.export(response, pageSize, o, k, fileName);
    }

    @Override
    public ExcelWriter getExcelWriter(HttpServletResponse response, String fileName) throws IOException {
        return super.getExcelWriter(response, fileName);
    }

    @Override
    public void complexFillWithTable(Object o, String fileName, HttpServletResponse response) {
        // Not implemented in this demo
    }

    @Override
    public int countExport(Object o) {
        return 0; // Placeholder implementation
    }

    @Override
    public List getExportDetail(Object o) {
        return null; // Placeholder implementation
    }
}

Test Controller

package com.example.system.controller;

import com.example.system.api.domain.ExportUser;
import com.example.system.service.impl.ExportImpl;
import lombok.SneakyThrows;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

@RestController
@RequestMapping("/export")
@Slf4j
public class ExportController {
    @Autowired
    private ExportImpl export;

    @PostMapping("/exportFile")
    public void exportFile() {
        new Thread(new Runnable() {
            @SneakyThrows
            @Override
            public void run() {
                Thread thread1 = Thread.currentThread();
                ExportUser sysUser = new ExportUser();
                sysUser.setUserName(thread1.getName());
                export.export(sysUser);
            }
        }).start();
    }
}

Test Results

Running the test shows that the queue enforces a maximum of ten concurrent export requests. When the limit is reached, additional requests block until a slot frees up. The first request finishes, is removed from the queue, and the next waiting user moves to the front and proceeds with its export.

Conclusion

The implementation demonstrates a simple FIFO queue to serialize heavy Excel export operations, preventing server overload. The author notes that other aspects—such as designing the export table schema, uploading files to OSS, handling downloads, and evaluating high‑concurrency edge cases—are not covered. Alternative queue mechanisms (e.g., Redis‑based queues) could also be used; this example serves as a concrete starting point.