[valenterry]

> Looking at our request traffic made choosing optimistic locking fairly easy. We expect the majority of ledger operations to be reads, and we didn't want reads to block writes (and vice versa).

I don't get it. If they use an SQL database that supports ACID already, why not just lock all the ledger rows necessary with an exclusive row access when writing and otherwise just with a shared access so that the write waits until the read finishes?

[cormacrelf]

I think the missing context is that the db reads go all the way to an HTTP client, get modified on the client, and are sent back hoping nobody else has edited the row in the meantime. Pessimistic = check row is not locked on READ; Optimistic = check lock_version matches on WRITE.

For a table where single row ops are all you can do, this is basically enough to let API users read and update rows concurrently. SQL transactions don’t survive longer than a single batch of statements sent in one request, so inadequate.

Edit: Turns out ActiveRecord’s pessimistic locks don’t use a column at all, they use builtin row locking from MySQL/Postgres (ie ‘inadequate’). So you can’t use it for multi HTTP request transactions at all.

Final edit, if you read about how databases implement ACID in the first place, optimistic locking is one of the building blocks. You can use it to reproduce the various isolation levels of SQL transactions in HTTP APIs, manually, in SQL. (Also look at MVCC.) This does not sound fun. Distributed locking is kinda like doing a group project at university, in that you’re going to have to do it all yourself, but in another sense if one writer goes MIA you all fail.

[valenterry]

Ahh, so they sent the lock_version with the read. Now it all makes sense. Thank you!

And as you say, in this case you couldn't even reliably lock on read because you don't know whether or when a client sends a POST anyways.

[paulddraper]

They wanted to add locking to their API, not internally.