The SQL Standard isolation level definitions are (probably deliberately) imprecise. They are given in terms of allowable concurrency phenomena:
- P1 - dirty read
- P2 - non-repeatable read
- P3 - phantom
READ COMMITTED isolation, only P1 is disallowed.
extract from ISO/IEC 9075-2
Reading the "same record" more than once due to movement within a physical structure is therefore not disallowed, so long as each read is not a dirty read (i.e. the read is performed on committed data).
Therefore, SQL Server's locking implementation of
READ COMMITTED isolation conforms with the standard.
I understand the behaviour may be surprising, but the root of that surprise is in misunderstanding what this isolation level does, and does not, guarantee. For example, one might expect a concurrent
UPDATE never to change the number of records returned by a
SELECT query. This is a reasonable expectation, but not one that must be met to conform with the standard.
If you want a point-in-time (stable) view of the data, locking read committed is not the isolation level implementation you need. While not mandated by the standard, read committed using row versioning (RCSI) happens to provide a statement-level point-in-time consistent view of the committed state of the database. For a transaction-level point-in-time consistent view of the committed state of the database, you would need snapshot isolation (SI).
As far as I know only MS SQL Server behaves this way.
It would be possible in any database that implements read committed using locks (specifically short-term locks for reads) and where a concurrent modification might cause a record to move ahead of the current scan position. In other words, it is implementation-specific.