5 ms·

I partially agree with what you are saying. I understand optimistic concurrency, especially the constraints it places on your code: you must in all cases access

I partially agree with what you are saying. I understand optimistic concurrency, especially the constraints it places on your code: you must in all cases access the data in the same order, otherwise you do get deadlocks. You also must implement the retry mechanism. I am not familiar with all the web frameworks out there (this is where I often encounter these types of issues), but I don't know of one that automatically retries requests for you. That "tiny bit of library code" is something that as far as I know you have to create for every situation, and it will be specific to your situation. I have yet to see a production codebase that actually does this instead of simply returning an error to the user saying "try again". If your database doesn't fully support ACID transactions, yes, get a better DB. And no you don't need to use named locks all the time. As an example, I have a codebase where I use the standard transactions semantics everywhere using the Django ORM, except one very specific and critical bit of code where I want to be dead certain that a thing can't happen twice in a row. So basically out of, say, 100 or so places where I commit a transaction, only one actually uses named locks, but where it does, it solves the problem in a very elegant way. So I agree with you that you shouldn't litter your code with these. But you absolutely can use them to either (a) guard against complex and critical parts of your code or (b) use these to quickly augment a huge existing codebase that keeps running into deadlocks or worse yet lock timeouts.

stickfigure 9y ago
I understand optimistic concurrency, especially the constraints it places on your code: you must in all cases access the data in the same order, otherwise you do get deadlocks. The second half of this statement negates the first :-) You have confused pessimistic concurrency with optimistic concurrency. An optimistic system detects collision at commit time and the actual order of operations in your transaction is irrelevant. You will never experience a deadlock, only aborts/retries. In a pessimistic concurrency scenario, you take explicit locks as you access resources. Those locks block access by other transactions. This creates the dining philosophers problem and the risk of deadlocks. When locking resources, you must access resources in a consistent order to prevent deadlocks. Please avoid database locks and let smart databases do what they are supposed to!
- IgorPartola 9y ago
  Learn something new every day. Yes you are correct, I meant pessimistic concurrency because that's what I'm familiar with. Which databases support optimistic concurrency and full ACID transactions?
  - stickfigure 9y ago
    Postgres, MS SQL Server, Oracle... and more exotic ones like Google's Cloud Datastore and Spanner. Probably many more.
    - IgorPartola 9y ago
      Interesting. I use Postgres and can't find any mention of this in the docs: https://www.postgresql.org/docs/current/static/mvcc.html https://www.postgresql.org/docs/current/static/mvcc.html talks about the usual row and table locks, and https://www.postgresql.org/search/?u=%2Fdocs%2F9.6%2F&q=Optimistic https://www.postgresql.org/search/?u=%2Fdocs%2F9.6%2F&q=Opti... is empty. Is it known by some other name in the Postgres land? Also, I found this and similar on SO: https://stackoverflow.com/questions/17431338/optimistic-locking-in-mysql https://stackoverflow.com/questions/17431338/optimistic-lock... So if this technique actually requires me to write my database access code at this level, it means that using something like an ORM is nearly impossible in a way that makes an ORM useful.
      - wbl 9y ago
        That's how isolation is implemented. MVCC is not compatible with any standard SQL level, but it is used for the lower ones.
      - stickfigure 9y ago
        That stackoverflow answer is wrong - or at least, only applies to MySQL. The author is apparently unaware of other databases. If you put Postgres in repeatable read or serializable[1] mode, it manages optimistic concurrency for you. Collisions will produce an error in the second transaction attempting to commit. The only "trick" is that your client code should be prepared to retry transactions that have concurrency failures, and this is very easily abstracted by a library. Your SQL or ORM code does not change (I use Hibernate, fwiw). Some good reading: https://www.postgresql.org/docs/current/static/transaction-iso.html#XACT-SERIALIZABLE https://www.postgresql.org/docs/current/static/transaction-i... [1] Repeatable Read won't detect collisions caused by phantom rows, so pick carefully. Most CRUD apps don't care about phantom rows.

stickfigure 9y ago

I understand optimistic concurrency, especially the constraints it places on your code: you must in all cases access the data in the same order, otherwise you do get deadlocks. The second half of this statement negates the first :-) You have confused pessimistic concurrency with optimistic concurrency. An optimistic system detects collision at commit time and the actual order of operations in your transaction is irrelevant. You will never experience a deadlock, only aborts/retries. In a pessimistic concurrency scenario, you take explicit locks as you access resources. Those locks block access by other transactions. This creates the dining philosophers problem and the risk of deadlocks. When locking resources, you must access resources in a consistent order to prevent deadlocks. Please avoid database locks and let smart databases do what they are supposed to!

IgorPartola 9y ago
Learn something new every day. Yes you are correct, I meant pessimistic concurrency because that's what I'm familiar with. Which databases support optimistic concurrency and full ACID transactions?
- stickfigure 9y ago
  Postgres, MS SQL Server, Oracle... and more exotic ones like Google's Cloud Datastore and Spanner. Probably many more.
  - IgorPartola 9y ago
    Interesting. I use Postgres and can't find any mention of this in the docs: https://www.postgresql.org/docs/current/static/mvcc.html https://www.postgresql.org/docs/current/static/mvcc.html talks about the usual row and table locks, and https://www.postgresql.org/search/?u=%2Fdocs%2F9.6%2F&q=Optimistic https://www.postgresql.org/search/?u=%2Fdocs%2F9.6%2F&q=Opti... is empty. Is it known by some other name in the Postgres land? Also, I found this and similar on SO: https://stackoverflow.com/questions/17431338/optimistic-locking-in-mysql https://stackoverflow.com/questions/17431338/optimistic-lock... So if this technique actually requires me to write my database access code at this level, it means that using something like an ORM is nearly impossible in a way that makes an ORM useful.
    - wbl 9y ago
      That's how isolation is implemented. MVCC is not compatible with any standard SQL level, but it is used for the lower ones.
    - stickfigure 9y ago
      That stackoverflow answer is wrong - or at least, only applies to MySQL. The author is apparently unaware of other databases. If you put Postgres in repeatable read or serializable[1] mode, it manages optimistic concurrency for you. Collisions will produce an error in the second transaction attempting to commit. The only "trick" is that your client code should be prepared to retry transactions that have concurrency failures, and this is very easily abstracted by a library. Your SQL or ORM code does not change (I use Hibernate, fwiw). Some good reading: https://www.postgresql.org/docs/current/static/transaction-iso.html#XACT-SERIALIZABLE https://www.postgresql.org/docs/current/static/transaction-i... [1] Repeatable Read won't detect collisions caused by phantom rows, so pick carefully. Most CRUD apps don't care about phantom rows.