Understanding High-Watermark Syncs and SQL Server Locks

Data synchronization between a central SQL Server database and remote devices often relies on a high-watermark pattern. This technique queries for records where a ModifiedDate timestamp is greater than the last successfully synced timestamp.

A common concern with this approach is transaction timing: if a transaction sets ModifiedDate = GETDATE() at the start of a statement but takes several seconds to commit, a concurrent sync query might record a high-watermark timestamp after the statement ran but before it committed. In theory, this would cause the row to be permanently skipped in subsequent syncs.

However, during testing under SQL Server's default isolation level, you might notice that the sync query simply blocks until the uncommitted transaction finishes. Does this mean lock blocking makes `ModifiedDate` watermarking completely safe? The short answer is no. While blocking offers a false sense of security in basic tests, several edge cases and database configurations can cause data loss.

Why Lock Blocking Happens (And Why It Feels Safe)

By default, SQL Server operates under the READ COMMITTED isolation level. When a transaction performs an INSERT or UPDATE, it acquires Exclusive (X) locks on the modified rows or index pages.

When your sync session runs a SELECT query, it requests Shared (S) locks. If the sync query scans an index or table page containing an uncommitted row, it must wait for the Exclusive lock to be released. Once Session A commits, Session B reads the newly committed row and includes it in the result set. Because the sync query waited, it captured the row safely.

4 Ways `ModifiedDate` Watermark Syncs Can Still Fail

Relying solely on default locking behavior for watermark syncs introduces significant risks. Here is how your sync mechanism can break in production:

1. Read Committed Snapshot Isolation (RCSI)

Many modern SQL Server databases enable Read Committed Snapshot Isolation (RCSI) or Snapshot Isolation to prevent readers from blocking writers. Under RCSI, when Session A holds an uncommitted row, Session B does not block. Instead, it reads the row's previous version (or ignores an uncommitted insert entirely) from tempdb.

If the sync query finishes and advances `@lastSyncedDate` to the current time, Session A's row will commit with an older `ModifiedDate`. On the next sync run, the query checks WHERE ModifiedDate > @lastSyncedDate, effectively missing Session A's row forever.

2. High-Water Mark Calculated from `GETDATE()` Instead of Data

If your application updates `@lastSyncedDate` using the current system time (GETDATE()) at the end of the sync run rather than the maximum ModifiedDate actually retrieved from the dataset, a race condition occurs:

  • 10:00:00 – Transaction A starts and sets ModifiedDate = 10:00:00.
  • 10:00:05 – Transaction B starts, sets ModifiedDate = 10:00:05, and commits immediately.
  • 10:00:06 – Sync Query runs, seeking records where ModifiedDate > 09:55:00. It reads Transaction B's row, but blocks on Transaction A's lock if reading sequentially.
  • If another sync process updates the high-watermark using current time without processing blocked rows properly, data points fall behind the cutoff.

3. Non-Sequential Scans and Index Seek Skipping

If an index exists on ModifiedDate, SQL Server performs an Index Seek starting at `@lastSyncedDate`. If an uncommitted transaction inserted a row with a `ModifiedDate` earlier than `@lastSyncedDate` (due to application clock skew or explicit timestamp overrides), the index seek will skip that entry entirely without ever hitting a lock.

4. Lock Escalation and Query Timeouts

If concurrent write activity increases, SQL Server may escalate fine-grained row locks to table locks, or sync queries may experience lock timeouts (Error 1222). Developers often respond by adding the WITH (NOLOCK) hint to prevent blocking, which introduces dirty reads and exacerbates missing record issues.

Robust Alternatives for Delta Synchronization

Instead of relying on `GETDATE()` and default lock blocking, consider these industry-standard solutions for safe data synchronization:

Approach 1: Use `rowversion` and `MIN_ACTIVE_ROWVERSION()`

SQL Server provides a monotonically increasing binary number called rowversion (formerly timestamp). It automatically updates whenever a row changes.

CREATE TABLE dbo.MyTable (
    Id INT IDENTITY(1,1) PRIMARY KEY,
    Name VARCHAR(50) NOT NULL,
    SysVersion ROWVERSION NOT NULL
);

To query safely without missing uncommitted transactions, use MIN_ACTIVE_ROWVERSION() as your ceiling watermark:

DECLARE @SafeCeiling ROWVERSION = MIN_ACTIVE_ROWVERSION();

SELECT * 
FROM dbo.MyTable WITH (READCOMMITTEDLOCK)
WHERE SysVersion > @lastSyncedVersion 
  AND SysVersion < @SafeCeiling
ORDER BY SysVersion;

Approach 2: Introduce a Lookback Window

If you must use `ModifiedDate`, subtract a safety buffer (e.g., 5 minutes) from your watermark query to re-check recently modified rows, relying on primary key upserts at the destination to handle duplicates cleanly.

SELECT * 
FROM dbo.MyTable
WHERE ModifiedDate > DATEADD(minute, -5, @lastSyncedDate);

Approach 3: Native SQL Server Change Tracking (CT)

For critical synchronization tasks, enable SQL Server's built-in Change Tracking. It tracks inserts, updates, and deletes with minimal overhead and avoids timestamp timing issues entirely.

-- Enable on Database
ALTER DATABASE MyDatabase
SET CHANGE_TRACKING = ON
(CHANGE_RETENTION = 2 DAYS, AUTO_CLEANUP = ON);

-- Enable on Table
ALTER TABLE dbo.MyTable
ENABLE CHANGE_TRACKING;

Conclusion

Lock blocking in SQL Server under default isolation levels may give the illusion that `ModifiedDate` watermarking is thread-safe. However, enabling RCSI, timestamp offsets, or lock timeouts can silently corrupt your sync process. Transitioning to `rowversion` with active version checks or native Change Tracking ensures reliable, fail-safe data replication.