Prevent Overlapping Runs of an Excel Report Job

A report job that takes ten minutes on a normal day and forty on a bad one will, sooner or later, still be running when the scheduler starts the next copy. Two runs then read the same inputs, write the same workbook at the same time, and send the same email twice — or one overwrites the other's half-written file. The fix is to make the job single-instance: each run takes a lock when it starts, and a run that cannot get the lock knows another is in progress. This page, part of Error Handling and Logging in Excel Automation, builds a small, portable lock for Python report jobs.

If the job runs from cron on Linux, wrapping the command in flock -n does the same without any code, as Scheduling Python Excel Scripts with Cron shows. The in-code version adds what the wrapper cannot: per-report locks, a message saying who holds the lock, waiting with a timeout, and Windows support.

Prerequisites

Only the standard library. single_instance.py is the lock helper this page builds; report_job.py is a stand-in report that holds the lock for a given number of seconds, so the examples can start overlapping runs on purpose.

Step 1 — Take an operating-system lock

The lock is an ordinary file, but what matters is not whether the file exists — it is the lock the operating system holds on it. fcntl.flock with LOCK_EX | LOCK_NB asks for an exclusive lock and fails immediately instead of waiting if another process has it; on Windows, msvcrt.locking with LK_NBLCK does the same. The helper wraps both in a context manager:

# single_instance.py
import contextlib
import os
import time
from pathlib import Path

class AlreadyRunning(RuntimeError):
    pass

if os.name == "nt":
    import msvcrt
    def _try_lock(fh):
        try:
            fh.seek(0); msvcrt.locking(fh.fileno(), msvcrt.LK_NBLCK, 1); return True
        except OSError:
            return False
else:
    import fcntl
    def _try_lock(fh):
        try:
            fcntl.flock(fh, fcntl.LOCK_EX | fcntl.LOCK_NB); return True
        except BlockingIOError:
            return False

@contextlib.contextmanager
def single_instance(name, lock_dir="locks", wait=0.0, poll=0.2):
    Path(lock_dir).mkdir(exist_ok=True)
    path = Path(lock_dir) / f"{name}.lock"
    fh = open(path, "a+")
    deadline = time.monotonic() + wait
    while not _try_lock(fh):
        if time.monotonic() >= deadline:
            try:
                fh.seek(0)
                holder = fh.read().strip() or "unknown"
            except OSError:
                holder = "holder unknown"
            fh.close()
            raise AlreadyRunning(f"{name} is already running ({holder})")
        time.sleep(poll)
    fh.seek(0); fh.truncate()
    fh.write(f"pid {os.getpid()} since {time.strftime('%H:%M:%S')}"); fh.flush()
    try:
        yield path
    finally:
        fh.close()

A job uses it by wrapping its work in the with block and turning AlreadyRunning into a clean exit — this is report_job.py, the stand-in used below:

# report_job.py
import sys, time
from single_instance import single_instance, AlreadyRunning

name, seconds, wait = sys.argv[1], float(sys.argv[2]), float(sys.argv[3]) if len(sys.argv) > 3 else 0
try:
    with single_instance(name, wait=wait):
        print(f"{name}: running", flush=True)
        time.sleep(seconds)
        print(f"{name}: finished", flush=True)
except AlreadyRunning as exc:
    print(f"skipped: {exc}", flush=True)
    sys.exit(75)

Three details make it robust. The file is opened in append mode (a+), so opening it never truncates information another run wrote. Once the lock is acquired, the run writes its PID and start time into the file — not to be the lock, but so a refused run can say who holds it. (On Windows the locked byte cannot be read by another process, so the helper falls back to "holder unknown" there.) And the lock lives only as long as the open file handle: when the with block ends, the process exits or the process crashes, the operating system releases it. There is nothing to clean up and nothing to go stale.

Step 2 — See a second run refused

Start one run that takes two seconds, then a second run of the same report straight away:

import subprocess
import sys
import time

first = subprocess.Popen([sys.executable, "report_job.py", "daily_sales", "2"], stdout=subprocess.PIPE, text=True)
time.sleep(0.5)
second = subprocess.run([sys.executable, "report_job.py", "daily_sales", "0"], capture_output=True, text=True)
print("second run:", second.stdout.strip(), "| exit code", second.returncode)
print("first run: ", first.communicate()[0].strip().replace("\n", " / "), "| exit code", first.returncode)

third = subprocess.run([sys.executable, "report_job.py", "daily_sales", "0"], capture_output=True, text=True)
print("third run: ", third.stdout.strip().replace("\n", " / "), "| exit code", third.returncode)

The second run is refused and names the process holding the lock. Once the first finishes, the third run gets the lock normally — no leftover file had to be deleted. The skipped run exits with code 75, which by convention (EX_TEMPFAIL) means "temporary condition, try later"; using a distinct code lets monitoring tell a skipped run from a failed one. Whatever code you choose, log the skip: a report that silently never runs because its predecessor hangs forever is its own kind of outage.

Step 3 — Lock per report, not per machine

A single global lock makes every job wait for every other. Name the lock after the report — and the period, if runs for different periods may safely overlap — so unrelated jobs run in parallel and only true duplicates are stopped:

a = subprocess.Popen([sys.executable, "report_job.py", "daily_sales", "1"], stdout=subprocess.PIPE, text=True)
b = subprocess.Popen([sys.executable, "report_job.py", "stock_levels", "1"], stdout=subprocess.PIPE, text=True)
print(a.communicate()[0].split("\n")[0], "|", b.communicate()[0].split("\n")[0])
print("both exit codes:", a.returncode, b.returncode)

The sales and stock reports each hold their own lock and run side by side. A name such as f"month_end_{period}" lets a backfill for March run while the live September job is running, but stops two September runs colliding.

Step 4 — Wait instead of skipping, when the work must not be lost

Skipping is right for a scheduled report: the next scheduled run picks up the work. When a run is triggered by an event — a file arriving, a user pressing "generate" — skipping would lose it. Wait for the lock instead, with a timeout so a hung predecessor cannot block forever:

first = subprocess.Popen([sys.executable, "report_job.py", "on_upload", "1.5"], stdout=subprocess.PIPE, text=True)
time.sleep(0.3)
start = time.monotonic()
waiter = subprocess.run([sys.executable, "report_job.py", "on_upload", "0", "5"], capture_output=True, text=True)
first.wait()
print(f"waiter: {waiter.stdout.strip().replace(chr(10), ' / ')} after {time.monotonic() - start:.1f} s, exit {waiter.returncode}")

The waiting run polls until the first releases the lock, about a second later, and then runs. If the timeout expires first, it raises AlreadyRunning just as the skipping version does — at which point alerting a person is better than waiting longer.

Why not a PID file?

The common home-made alternative is to write a file containing the process ID at start, delete it at the end, and refuse to start if it exists. It fails in exactly the situations a lock is for. A crashed or killed run never deletes its file, so every later run refuses to start until someone notices — often days later. Checking whether the recorded PID is still alive helps, but process IDs are reused, so an unrelated process can make a dead run look alive. An operating-system lock has neither problem, because it belongs to the running process and vanishes with it.

When runs start on more than one machine

File locks protect runs on one machine. If a job can start on several — a pool of workers, a scheduled container that might be scheduled twice — the lock has to live somewhere they all share. Locks on network file shares are unreliable: whether they work depends on the file system and its settings. Use a service built for it instead. With PostgreSQL, SELECT pg_try_advisory_lock(hashtext('daily_sales')) returns true for exactly one session and is released when that session ends; Redis offers SET key value NX PX 600000 for a lock that also expires by itself. Both follow the same pattern as the file lock: try, and skip or wait if someone else has it.

Common pitfalls

• Checking the file exists instead of locking it. Existence is not a lock; take an OS lock on the file.
• PID files. They go stale after a crash; use a lock the OS releases.
• One lock for everything. Name locks per report (and period) so unrelated jobs are not blocked.
• Silent skips. Log every skipped run and alert when runs keep being skipped.
• Waiting forever. Always give a waiting run a timeout.
• Locks on network shares. Use a database or Redis lock across machines.

Performance notes

Taking and releasing a file lock costs microseconds; the only real cost of this pattern is the run you skip. If runs are regularly skipped because the previous one is still going, the schedule is too tight or the job too slow — Skip a Report When the Input File Has Not Changed removes unnecessary runs altogether.

Conclusion

Make report jobs single-instance by taking an exclusive, non-blocking operating-system lock — fcntl.flock or msvcrt.locking — on a lock file named after the report, and write the holder's PID into it so refused runs can say who is running. Skip with a distinct exit code for scheduled runs, wait with a timeout for event-driven ones, avoid PID files that go stale, and move the lock to a database or Redis when runs can start on more than one machine.

Frequently asked questions

How do I stop a Python script from running twice at the same time? Open a lock file and take an exclusive, non-blocking lock on it with fcntl.flock on Linux and macOS or msvcrt.locking on Windows. If the lock is already held, another run is in progress, so exit. The operating system releases the lock when the process ends, even if it crashes.

Why not just create a PID file and check whether it exists? A PID file outlives a crashed run, so the next run sees it and refuses to start until someone deletes it — and a recycled PID can make a dead run look alive. An operating-system lock disappears with the process that held it, so it cannot go stale.

Should the second run wait or exit? For a report that runs on a schedule, exit: the next scheduled run will pick up the work. Wait, with a timeout, when the job is triggered by an event that must not be lost, such as a file arriving.

Does a lock file work when jobs run on several servers? Not reliably. File locks on network shares depend on the file system and its configuration. When runs can start on different machines, use a lock in a shared service instead, such as a PostgreSQL advisory lock or a lock key in Redis.

Related

• Up one level: Error Handling and Logging in Excel Automation — making unattended jobs fail well.
• Scheduling Python Excel Scripts with Cron — the flock -n wrapper.
• Handle Permission Denied When Writing Excel in Python — the file already open elsewhere.
• Backfill Excel Reports for Past Dates — runs that overlap on purpose.