3 ms·

I'm glad to see optimistic concurrency control mentioned. In my experience even seasoned developers are unaware of this pattern, which should be really the defa

by isoos 10y ago

I'm glad to see optimistic concurrency control mentioned. In my experience even seasoned developers are unaware of this pattern, which should be really the default in many database-handling code. I'm not aware of ORMs handling it either (except for my in-house code generator). Is there anything out there that handles it well?

joemccall86 10y ago
GORM in Grails uses optimistic concurrency control by default, which is how I learned about the issue in the first place.
agopaul 10y ago
Doctrine2 (PHP) supports this natively. I've never used this approach, but after working a lot with row-level locks (SELECT ... FOR UPDATE) errors in environments with sustained load, I think next time I'm going to evaluate this approach. Row-level locks need to be carefully implemented in order to avoid deadlocks, which can occur for a number of reasons (eg. missing indexes, other transactions locking rows in different orders, etc.).
pmontra 10y ago
Rails' ActiveRecord has both http://api.rubyonrails.org/classes/ActiveRecord/Locking/Optimistic.html http://api.rubyonrails.org/classes/ActiveRecord/Locking/Opti... http://api.rubyonrails.org/classes/ActiveRecord/Locking/Pessimistic.html http://api.rubyonrails.org/classes/ActiveRecord/Locking/Pess...
- marcoperaza 10y ago
  From the optimistic locking page: This locking mechanism will function inside a single Ruby process. To make it work across all web requests, the recommended approach is to add lock_version as a hidden field to your form. Unless I'm misunderstanding, that's absolutely terrible advice, from both a security and proper layering standpoint.
  - netghost 10y ago
    I think if your lock version is a random hash that is updated each time you write via trigger or app code, then at best someone nefarious could change that value in the form and prevent themselves from being able to update the record. It also depends on what you're trying to achieve. For instance, if you're just trying to help coordinate people updating a wiki page so that two people don't accidentally clobber each other's updates then it's probably not a huge concern. From a layering perspective, you can probably implement this with checks and triggers in postgres so that it's mostly transparent to the client. That said, I wouldn't bother myself ;)
  - dcosson 10y ago
    > Unless I'm misunderstanding, that's absolutely terrible advice, from both a security and proper layering standpoint. Not trying to start a flamewar but... that's Rails in a nutshell. The default patterns are just straight up bad for any kind of large app built by more than one or two people. The gotchas mentioned in this post basically have no built-in rails patterns to mitigate them so you have to know how to write an app without the ORM to effectively use ActiveRecord, even though it's viewed as a great abstraction that you should always use so your code stays readable and you and your future team members won't have to worry about the implementation details in SQL. For instance there's no built-in way to take advantage of Postgres UPSERT so every codebase I've come across does it wrong with a naive read then create (sometimes wrapped in a .transaction block which doesn't necessarily fix it as the post points out but is used as like a rain dance in many rails codebases whenever something's acting weird and we're not sure why). The built-in Rails increment() is not atomic, and the atomic increment_counter() helper still encourages you to do the wrong thing! (it doesn't use UPDATE .. RETURNING so you see people incrementing and then reading which obviously is no longer atomic). And on and on.
mosburger 10y ago
IIRC Hibernate supports it (haven't used Hibernate in ages though).
Aaargh20318 10y ago
JPA 2.0 has both optimistic and pessimistic locking.
RazerM 10y ago
SQLAlchemy suppports optimistic concurrency control[1] and row level locking[2] [1]: http://docs.sqlalchemy.org/en/latest/orm/versioning.html http://docs.sqlalchemy.org/en/latest/orm/versioning.html [2]: http://docs.sqlalchemy.org/en/latest/orm/query.html#sqlalchemy.orm.query.Query.with_for_update http://docs.sqlalchemy.org/en/latest/orm/query.html#sqlalche...
dvlsg 10y ago
Mongoose uses optimistic locking by default, I believe.
GordonS 10y ago
I believe Marten supports this

by isoos 10y ago

joemccall86 10y ago

GORM in Grails uses optimistic concurrency control by default, which is how I learned about the issue in the first place.

agopaul 10y ago

Doctrine2 (PHP) supports this natively. I've never used this approach, but after working a lot with row-level locks (SELECT ... FOR UPDATE) errors in environments with sustained load, I think next time I'm going to evaluate this approach. Row-level locks need to be carefully implemented in order to avoid deadlocks, which can occur for a number of reasons (eg. missing indexes, other transactions locking rows in different orders, etc.).

pmontra 10y ago

Rails' ActiveRecord has both http://api.rubyonrails.org/classes/ActiveRecord/Locking/Optimistic.html http://api.rubyonrails.org/classes/ActiveRecord/Locking/Opti... http://api.rubyonrails.org/classes/ActiveRecord/Locking/Pessimistic.html http://api.rubyonrails.org/classes/ActiveRecord/Locking/Pess...

marcoperaza 10y ago
From the optimistic locking page: This locking mechanism will function inside a single Ruby process. To make it work across all web requests, the recommended approach is to add lock_version as a hidden field to your form. Unless I'm misunderstanding, that's absolutely terrible advice, from both a security and proper layering standpoint.
- netghost 10y ago
  I think if your lock version is a random hash that is updated each time you write via trigger or app code, then at best someone nefarious could change that value in the form and prevent themselves from being able to update the record. It also depends on what you're trying to achieve. For instance, if you're just trying to help coordinate people updating a wiki page so that two people don't accidentally clobber each other's updates then it's probably not a huge concern. From a layering perspective, you can probably implement this with checks and triggers in postgres so that it's mostly transparent to the client. That said, I wouldn't bother myself ;)
- dcosson 10y ago
  > Unless I'm misunderstanding, that's absolutely terrible advice, from both a security and proper layering standpoint. Not trying to start a flamewar but... that's Rails in a nutshell. The default patterns are just straight up bad for any kind of large app built by more than one or two people. The gotchas mentioned in this post basically have no built-in rails patterns to mitigate them so you have to know how to write an app without the ORM to effectively use ActiveRecord, even though it's viewed as a great abstraction that you should always use so your code stays readable and you and your future team members won't have to worry about the implementation details in SQL. For instance there's no built-in way to take advantage of Postgres UPSERT so every codebase I've come across does it wrong with a naive read then create (sometimes wrapped in a .transaction block which doesn't necessarily fix it as the post points out but is used as like a rain dance in many rails codebases whenever something's acting weird and we're not sure why). The built-in Rails increment() is not atomic, and the atomic increment_counter() helper still encourages you to do the wrong thing! (it doesn't use UPDATE .. RETURNING so you see people incrementing and then reading which obviously is no longer atomic). And on and on.

mosburger 10y ago

IIRC Hibernate supports it (haven't used Hibernate in ages though).

Aaargh20318 10y ago

JPA 2.0 has both optimistic and pessimistic locking.

RazerM 10y ago

SQLAlchemy suppports optimistic concurrency control[1] and row level locking[2] [1]: http://docs.sqlalchemy.org/en/latest/orm/versioning.html http://docs.sqlalchemy.org/en/latest/orm/versioning.html [2]: http://docs.sqlalchemy.org/en/latest/orm/query.html#sqlalchemy.orm.query.Query.with_for_update http://docs.sqlalchemy.org/en/latest/orm/query.html#sqlalche...

dvlsg 10y ago

Mongoose uses optimistic locking by default, I believe.

GordonS 10y ago

I believe Marten supports this