If you do use serializable isolation level you might get a deadlock, depending on how conscientious you are about updating everything in the same order.
It sounds like you misunderstand optimistic concurrency. If your transaction touches a piece of data, and that data changes (by another transaction) while your transaction is in progress, then your transaction will rollback on commit. With a tiny bit of library code, your transaction will retry until success. In the case of severe contention you may see repeated retries and timeouts, but you will never see deadlocks - at least one transaction will successfully commit each "round".
I disagree - database locks should not be used by the average developer. Understand optimistic concurrency and use serializable isolation. If (and only if!) you have a known performance problem with highly contended data, consider a locking strategy. 99 out of 100 developers will never need it, and most of that 1% will botch locks on the first try anyways.
If your database doesn't support optimistic concurrency, get a better database.
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.
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!
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?
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.
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).
How does this work if your transactions also do external things? So if have two concurrent transactions that charge a credit card via an external API, as well as mark the order as paid in your DB? Retrying that transaction would be disastrous, no?
This is a problem that affects all distributed systems irrespective of database isolation levels. For example, your call to the external API might timeout; did the charge succeed or not? Database locking doesn't help you.
The solution comes in two parts:
1) Ensure your call to the card processor is idempotent and retry it. For example, grep Stripe's documentation for the "Idempotency-Key" header. Every processor should have a similar mechanism. Conveniently, if your database transaction unit-of-work includes the remote call, it will retry automatically in an optimistic scenario.
2) Get a webhook call from the credit card processor. Even though you are retrying your transaction, you can't guarantee that your server didn't crash without a successful commit. This potentially leaves a charged card without recording the purchase; the webhook will sync it up afterwards.
Distributed systems are hard if you think past the happy path. I wish every REST API would have an equivalent of Idempotency-Key.
Yup. I'm familiar with Strip's solution to this and am really happy with it. I also wish more API's had that.
But this brings me back to my original point: having a library that blindly re-tries POST requests that result database transactions on every conflict is not going to work. Until every external API, from printers to credit card processors, has an Idempotency Key equivalent you just can't do that generically, and you will have to do that with a custom bit of code for every situation.
Isn't it easier to just acquire an advisory lock for the critical bit and take your own database's concurrency contention out of the equation?
A lock still doesn't give you a transaction across two systems that aren't transactionally connected. If you're specifically worried about retries you can create a "task" (row in a table) in the transaction and have a consumer read tasks and propagate the message.
A lock is seductive because in the happy path it looks like you have a distributed transaction. In practice making reliable calls across distributed systems involves more complexity. The hard part is figuring out what to do when your remote call times out and you aren't sure if it succeeded; once you resolve that, you can usually work with any concurrency scenario.
It sounds like you misunderstand optimistic concurrency. If your transaction touches a piece of data, and that data changes (by another transaction) while your transaction is in progress, then your transaction will rollback on commit. With a tiny bit of library code, your transaction will retry until success. In the case of severe contention you may see repeated retries and timeouts, but you will never see deadlocks - at least one transaction will successfully commit each "round".
I disagree - database locks should not be used by the average developer. Understand optimistic concurrency and use serializable isolation. If (and only if!) you have a known performance problem with highly contended data, consider a locking strategy. 99 out of 100 developers will never need it, and most of that 1% will botch locks on the first try anyways.
If your database doesn't support optimistic concurrency, get a better database.