We had similar problem (banking service). Instead of "moving" directly to destination we include a safe account in the middle.
A > safe > B
Safe account is called this way because we don't risk the misuse of money in case of rollback.
Adding Optimistic Locking to an API
21–30 of 39 posts
Re: Adding Optimistic Locking to an API
#22I understand each solution has trade-offs, but I've traditionally done this with an in-db proc (we used stored procedures extensively on Wall Street for txns). How does this compare against having the entire transaction functionality inside the DB?
Re: Adding Optimistic Locking to an API
#23I understand each solution has trade-offs, but I've traditionally done this with an in-db proc (we used stored procedures extensively on Wall Street for txns). How does this compare against having the entire transaction functionality inside the DB?
Tradeoff is that it has very poor performance for frequently changed data, you can starve clients etc.
It's much better to create transfer api which atomically debits one and credits other account instead of low level individual ops.
Re: Adding Optimistic Locking to an API
#24Earlier quoted context omitted.
I thought I didn't know what it was when I heard of them, then realized that it's not a lock at all, it's just a row version column, with the basic rule don't overwrite without having seen what you're about to overwrite. Same goes for `git push --force`, always use `git push --force-with-lease` instead.
Why do you think it is not a lock?
It's not a lock in the sense that that is succeeds or fails immediatelly.
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.
Re: Adding Optimistic Locking to an API
#25I understand each solution has trade-offs, but I've traditionally done this with an in-db proc (we used stored procedures extensively on Wall Street for txns). How does this compare against having the entire transaction functionality inside the DB?
Using stored procedures is old fashioned and won't earn you any points on your resume if you're being interviewed by 20 year olds. It's a solid solution though unless you are at FAANG volumes.
Re: Adding Optimistic Locking to an API
#26if you're solely relying on "lock_version" numbers, you may wanna watch out for integer overflows (not sure how ruby handles it). I had faced a similar problem in the past, it can have massive ripple effects with downstream services.
Also, if you're using postgres. Its worth looking into advisory locks [1] for similar use cases. They are pretty light weight compared to other locking mechanisms.
[1] https://www.postgresql.org/docs/9.4/explicit-locking.html
Re: Adding Optimistic Locking to an API
#27We had similar problem (banking service). Instead of "moving" directly to destination we include a safe account in the middle. A > safe > B Safe account is called this way because we don't risk the misuse of money in case of rollback.
Generally in banking you either separate preauthorization (or reservation of funds) from authorization, or you separate transaction from settlement and you compute an acceptable rate of loss.
Why did you decide to go with an escrow model in your use case?
Re: Adding Optimistic Locking to an API
#28> 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…
They wanted to add locking to their API, not internally.
Re: Adding Optimistic Locking to an API
#29Earlier quoted context omitted.
I thought I didn't know what it was when I heard of them, then realized that it's not a lock at all, it's just a row version column, with the basic rule don't overwrite without having seen what you're about to overwrite. Same goes for `git push --force`, always use `git push --force-with-lease` instead.
Why do you think it is not a lock?
It is a locking convention, achieving something locks are used for, but you can't point to one thing and say, there, that's the lock. Contrast with a database row lock where no other transaction can change that row or get a locked access to it until released.
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'.
Re: Adding Optimistic Locking to an API
#30> Since the protocol doesn't specify how the ETag should be generated, we could have passed in our "lock_version" version numbers. But because it seemed strange to only honor the ETag headers for a single resource in our API, we decided against it. Odd choice. There's a standard, but the developers still chose to re-implement w/ specific semantics. There's nothing on the standard saying you have to support ETags for…
Yep.
The HTTP standard is rich with a caching, idempotence, etc.
You really have to craft a set of requirements to not find what you need there.