How a Simple Queue Prevents Server Crashes When Multiple Users Export Excel Simultaneously

Introduction

When database tables grow, exporting the full dataset becomes I/O‑intensive; simultaneous Excel exports trigger heavy MySQL reads and file‑stream writes, which can crash the server. To protect performance, the author proposes queuing export requests.

Business Relationship Definition

ExportQueue : a fixed‑size FIFO queue that tracks waiting users and blocks new submissions when the queue is full.

User information : each queued entry represents a user who will run the export.

Export class : defines the asynchronous export method; users can view and download the generated file.

Code Implementation

ExportQueue

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 size limit
    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, please wait"); wait(); }
            catch (InterruptedException e) { e.getMessage(); }
        }
        queue.add(sysUser);
        log.info("Current export 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

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;

    /**
     * Export data to an Excel file.
     */
    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);
    public abstract int countExport(T t);
    public abstract List<K> getExportDetail(T t);
}

ExportImpl

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 java.util.LinkedList;

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

    @Override
    public void export(ExportUser sysUser) throws InterruptedException {
        log.info("Export method started");
        LinkedList<ExportUser> queue = exportQueue.add(sysUser);
        log.info("Export queue: " + queue);
        // Simulate 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) { }

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

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

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(() -> {
            @SneakyThrows
            public void run() {
                ExportUser sysUser = new ExportUser();
                sysUser.setUserName(Thread.currentThread().getName());
                export.export(sysUser);
            }
        }).start();
    }
}

Test Results

Requests are limited to a maximum queue size of 10; when the queue exceeds this limit, further submissions are blocked.

The first and second requests are spaced 10 seconds apart; after the first export finishes, the user is removed from the queue and the next waiting user moves to the front and proceeds with export.

Summary

The current implementation only covers queuing and a simulated export delay; file‑format design, OSS upload, download handling, and behavior under high concurrency are not addressed. Alternative queue mechanisms such as Redis could also be used; the article provides a basic idea rather than a production‑ready solution.