e.g. if the version column is an incrementing number, then it relies on no client unilaterally incrementing the value on failure and retrying--not much of a 'lock'.
Nobody would call CAS a lock. If your system only uses CAS, it should rightly be called “lock-free”.
In the spirit of trying to keep this complex subject free of misrepresentation... Compare-and-set (CAS, or alternatively compare-and-swap) is commonly used to implement lock-free algorithms.
A 'spin lock' where a thread uses only CAS in a tight loop and not carrying on until the lock is acquired is indeed a lock and not a kind of non-blocking or lock-free algorithm.
Performance and implementation characteristics differ but the end result is functionally the same as a pessimistic lock, which is why they're both called "locking" mechanisms.
The functional difference is crucial here, the non-lock characteristics are what allows them to offer atomic updates via HTTP. They couldn’t have used pessimistic locking to do this, not only because it is is limited to a single db transaction, but because they can not trust API users or the network to ensure the rows ever get unlocked.
It is a lock in the sense that it allows success only.
Pesimistic lock usually means "maybe wait then success". In complex locking spaghetti it may mean deadlock. It may also mean wait then timeout. It may also mean wait then timeout then I don't know what actually happened, maybe success, maybe not.
