Writing and write conflicts

Writing is as dynamic as reading: the method, table, key, and fields come from the request, and the service generates the SQL. Every change generated this way affects exactly one row. Instead of overwriting a change someone else made since the row was read, the service detects it—provided the client sends the version it read in If-Match; without it, or with If-Match: *, the last change wins. If the operator enables it, this also works for tables without a row version and without a key. Statements that callers write themselves (checked statements and pass-through) can change several rows, as they can in SQL Server; this page doesn't apply to them.

• One row by its complete unique key, never by a free-form condition.
• Optimistic concurrency through the row version (If-Match; in Access, the write conflict).
• A computed row stamp for tables without a row version.
• Updating and deleting by the original values for tables without a key.

A row is addressed by its complete unique key and by nothing else. The key is the table's primary key or, failing that, its first unique index. If part of the key is missing, or if there's an extra argument or a $filter next to it, the request is refused. A partial key or a free-form condition would let a request that is missing a value change or delete every row that matches the rest. The generated statement adds a further safeguard: it runs in a transaction, counts the rows it affects, and rolls back for anything other than exactly one—back to its own savepoint, so that inside a client transaction it undoes only itself.

Optimistic concurrency through If-Match. If the table has a row version—or is enabled for the row stamp—a write can be tied to it. The service accepts the value as returned by a read (Base64) or in SQL Server notation (0x…). If the row has changed in the meantime, the service responds with 412 and writes nothing. Without If-Match, the last writer wins—for the fields it names.

A change that must not overwrite someone else's change needs a value that changes with every change to the row. A table with a row version has one. For a table without one, the only option a client has is to put the original values of all columns into the condition—which is what Access does over a direct connection—and neither of the two interfaces can carry that: If-Match names a single tag, not a list of values.

For such tables, the service can compute a row stamp: a SHA-256 hash over all stored columns of the row, computed in the database itself. It appears just like a row version—as the read-only column gw_rowstamp (binary(32)), as @odata.etag and the ETag header, announced in the metadata document with Core.OptimisticConcurrency—and on update and delete it's recomputed and compared in the same statement that writes. Nobody else can slip in between the check and the write. A client that can handle row versions needs nothing more, and the database needs no new column.

How the stamp is computed. Each column contributes its stored bytes, prefixed with their length; a missing value contributes a single 0xFF. The length makes the encoding unambiguous—without it, ('ab', 'c') and ('a', 'bc') would produce the same bytes—and the marker distinguishes “missing” from “empty.” Bytes rather than text, because a text form depends on things that aren't part of the value: the session language for a date, and rounding for money and float. The stamp is the same across repeated reads, and any change to a stored value changes it—including the fourth decimal place of a money value, the smallest change to a float, 100 ns on a datetime2, a change in letter case only, the same bytes in a different type for sql_variant, geography, hierarchyid, json, and vector, a column with a space in its name, and NULL versus an empty string. text, ntext, json, and vector can't be converted to bytes directly, so they go through varchar(max) or nvarchar(max).

What it doesn't do—and why it isn't applied across the board:

• A value that's changed and then changed back produces the same stamp again. The stamp describes the state; it doesn't count changes. A row version maintained by SQL Server doesn't have this gap.
• Every read computes it for every returned row over every column, large text columns included. On wide tables, you pay for that on every read.
• It compares bytes, not text under a collation: a change in letter case only counts as a change, even though SQL Server's comparison considers both values equal. That's stricter, never weaker.

Where you're allowed to change the schema, a row version column is still the better answer: exact, maintained by SQL Server, and free when reading.

The stamp is also available for a table without a key that is also listed under KeylessWriteTables. There it's part of the address and also covers the long text that a comparison of individual values doesn't capture.

A table without a unique key has no address for “this row,” so without explicit release it's read-only. An application that edits such a table over a direct connection identifies the row by its original values instead. This is how Access writes over a direct connection after a substitute key was chosen when linking:

UPDATE "t" SET "Text"=? WHERE "Nummer" = ? AND "Text" = ? AND "Betrag" IS NULL AND "Wert" IS NULL AND "Datum" IS NULL AND "Aktiv" = ?

Access names every column except the long text (nvarchar(max)) and writes IS NULL itself for a missing original value. The service accepts exactly this form for tables released on the alias (KeylessWriteTables):

• The original values must identify the row completely—every comparable column, or the row version or row stamp; fewer columns would also hit rows that differ only elsewhere.
• A missing original value becomes IS NULL.
• Exactly one row is updated or deleted. If the original values match several rows (identical in every comparable column), the service rolls back the change and responds with 409—just like Access over a direct connection, which then reports error 3362 and also rolls back.
• If nothing matches, the service responds with 412: the row has been changed or deleted in the meantime; without a key, the two cases can't be told apart.

The actions of the OData interface and their responses are described under Tables without a key: actions.

• Make tables without a row version editable
• Make tables without a key editable
• HTTP: writing to tables