HotXLS Read Leases and Write Guards for Delphi Workbooks

A background thread was exporting a 40,000-row report when the UI thread set one cell, and the file that landed on disk matched no workbook that ever existed. HotXLS handles that class of bug in lxWorkbookView.pas, where IXLSWorkbookViewCore issues O(1) read leases and fail-fast write guards: while a lease is open, every mutation entry point raises instead of writing

The failure that arrives without a stack trace

Reading a workbook is never one atomic operation. A report walk is tens of thousands of individual cell reads spread over seconds, and a single SetValue landing between two of them is enough to change what the rest of the walk sees. The classic engine makes this concrete: TXLSCellRef.SetValue can call FSST.Remove to drop a shared string entry, reset FValueType, and invalidate a formula cache state, all while another thread is midway through dereferencing exactly those structures. Nothing crashes on the spot. You get a report whose subtotals do not add up, or an export that silently reads a string index that now points somewhere else

HotXLS deliberately does not solve this by making writers wait. A reader can hold a workbook for several seconds, and in a VCL application the writer is often a UI callback or an event handler on the main thread — blocking that thread until a background export finishes is a worse outcome than failing the edit. So the coordination core raises EXLSWorkbookWriteGuardUnavailable the moment a write is attempted against an open lease, before a single field has been touched, and the caller decides whether to queue the edit, retry, or tell the user. Fail-fast conflicts, not queued ones

Is a workbook safe to read from two threads?

Yes, provided both readers hold a lease and nobody writes. IXLSWorkbookViewCore.AcquireReadLease takes a TCriticalSection, increments a counter, snapshots the current generation, and returns an IXLSWorkbookReadLease — constant time regardless of whether the workbook holds a thousand cells or a million. Any number of leases coexist, they may be released in any order, and each one pins the core alive through its own interface reference, so a lease outliving the object that created it is safe rather than a dangling pointer. Both engines participate: TXLSWorkbook in lxHandle.pas and TXLSXWorkbook in lxHandleX.pas each build a core in their constructor and expose _AcquireReadLease and _AcquireWriteGuard

What matters just as much is what the lease does not add to the read path. The critical section covers lease acquisition, lease release, and write transaction boundaries — nothing else. The ordinary per-cell read never enters a lock, a monitor, or an atomic counter, so holding a lease costs one acquisition and one release for the whole scan, not one per cell. That is the same design instinct behind the parallel XLSX parsing and memory allocator work: pay for coordination at the boundary, never in the inner loop. The symmetric rule also holds — AcquireReadLease raises EXLSWorkbookReadLeaseUnavailable whenever WriteDepth is non-zero, so you cannot open a lease from inside a write transaction, not even on the writing thread

uses
  lxHandle, lxWorkbookView;

procedure TReportThread.Execute;
var
  Lease: IXLSWorkbookReadLease;
  Sheet: TXLSWorksheet;
  Row: Integer;
  Total: Double;
begin
  // Raises EXLSWorkbookReadLeaseUnavailable if a write is in flight
  Lease := FWorkbook._AcquireReadLease;
  Sheet := FWorkbook.Sheets[1];
  Total := 0;
  for Row := 1 to 50000 do
    Total := Total + Sheet.Cells[Row, 3].Value;
  FTotal := Total;
  // Lease leaves scope here: its reference count drops to zero,
  // ReleaseReadLease runs, and writers become possible again
end;

Where does the write guard actually sit?

At the lowest mutable layer, never at the convenience API on top of it. _AcquireWriteGuard is called from inside TXLSCellRef.SetValue itself, which means every public path that funnels into it — Range.Value, worksheet text assignment, cell-by-cell copy, paste — is gated once instead of each wrapper repeating a check that a future wrapper will forget. The coverage is deliberately wide: 55 guard acquisitions in lxHandle.pas and 37 in lxHandleX.pas as of the batch that introduced the core

The gated surface spans cell values and cell formatting, TXLSWorkbook.Open, copy and paste, defined names (Add, rename, RefersTo, Visible, IsMacro, Comment, Delete), worksheet metadata such as Name, Zoom, Visible, StandardHeight, FreezePanes, Protect and Activate, page setup, page breaks, and Calculate. Placement is the whole point: the guard is acquired before the first field is written, not validated afterwards by a notification hook, so a rejected mutation leaves the model byte-identical. The regression suite asserts precisely that, re-reading sheet name, zoom, visibility, standard height, margins, orientation and page-break counts after every refused call. Load paths get the same treatment one layer down, where the ZIP read gate coordinates concurrent inflate for package formats

procedure TXLSWorksheet.Activate;
var
  WriteGuard: IXLSWorkbookWriteGuard;
begin
  // Acquired before the first field is touched, never after
  WriteGuard := FWorkbook._AcquireWriteGuard;
  if not FSelected then
  begin
    FWorkbook.FWorkSheets.Deselect;
    FSelected := True;
  end;
  FWorkbook.FWorkSheets.FActiveSheet := Self;
  // Only a completed outermost guard advances the generation
  WriteGuard.Complete;
end;

Why does a nested write advance the generation only once?

Because a write transaction is defined by the outermost guard on a thread, not by each guard individually. The core keeps a per-thread writer state holding a thread id, a depth and a completion flag. A second AcquireWriteGuard on the same thread finds that state and increments Depth rather than creating a new transaction, and only when Depth falls back to zero — with the outermost guard having been marked Complete — does FGeneration advance. This is what lets a high-level operation such as Calculate or Open call ten guarded primitives underneath and still register as one change. Inner Complete calls are recorded but do not move the counter on their own, and the guards may be released out of order without breaking the accounting

The failure direction is equally explicit. If a guard is released without Complete — the ordinary consequence of an exception unwinding the interface reference — the generation does not advance, because the write transaction never claimed success. Be clear-eyed about what that means: HotXLS does not roll the partial edit back. The counter records that no successful transaction completed, which is exactly the signal a cache needs, but restoring the model to its previous state is not something a reference-counted guard can do for you. If a mid-transaction failure can leave the workbook in a shape you cannot ship, keep the source file and reopen it, rather than trusting the in-memory object

What the generation counter buys you

Cheap staleness detection with no scanning. Generation is a UInt64 that starts at 1 and skips 0 on wraparound, so 0 is never a value the core issues and works as a reliable "never observed" sentinel. Two invariants make it usable: the generation cannot move while any read lease exists, and each successful write transaction increments it exactly once. So IXLSWorkbookReadLease.Generation is a snapshot that stays constant for the whole life of the lease, and IXLSWorkbookWriteGuard.StartGeneration tells a writer what the model looked like when its transaction opened. A grid, a print preview, or a derived index can compare one integer instead of diffing rows

var
  Lease: IXLSWorkbookReadLease;
begin
  Lease := FWorkbook._AcquireReadLease;
  if Lease.Generation <> FCachedGeneration then
  begin
    FCachedGeneration := Lease.Generation;
    RebuildRowHeightCache;
  end;
  PaintVisibleRows;
  // FCachedGeneration starts at 0, a value the core never issues,
  // so the very first pass always rebuilds
end;

What this coordination does not promise

Three limits are worth stating plainly, because assuming otherwise is how the mechanism gets misused. First, a write guard is not mutual exclusion between writers: the core excludes readers against writers, and two different threads can each hold a write guard at the same time, each advancing the generation independently — a regression test asserts exactly this behavior. Serializing your own writer threads is still your job. Second, nothing here is a file lock or a cross-process mutex; it coordinates threads inside one process against one workbook instance, and two processes opening the same .xlsx know nothing about each other. Third, the guarantee only reaches callers who actually take a lease — an unleased read still walks an unlocked hot path, which is fast and entirely unprotected. This is a coordination core, not a transactional database

Used within those bounds it is a small, honest primitive: nine dedicated regression tests cover multiple readers, both conflict directions, reentrancy, out-of-order release, aborted transactions, and cross-thread read/write and write/write races, inside a suite of 1,328 tests passing on Win32 and Win64. Pair it with the crash-safe staged temp-file save path and a background export becomes something you can reason about end to end — consistent while it reads, atomic when it writes. Read leases, write guards and the generation counter ship as part of the classic and package engines in the HotXLS Delphi Component for Delphi and C++Builder, with no configuration needed to enable them