Practical Guide to SQL Transaction Isolation
begriffs.com
begriffs.com
So use names/advisory locks: create a named lock called "order-1234" where 1234 is tbe ID of the order. Acquire it before doing any manipulations to the order across all its tables. Then release it. Semantics of names locks differ across RDBMS's. Unsurprisingly Postgres has saner and more flexible semantics than MySQL, but both are perfectly suitable for the purpose. This technique can save you a ton of headaches.
My plan is to use the WITH FOR UPDATE technique because I figure if I always lock the one top level object in my tree it allows me to isolate that whole subsection of the tree (so long as I'm consistent with the locking). Basically it should give a way to serialize operations on subsections of the data (which makes a lot of sense in general usage where it's partitioned naturally).
Also, locks using FOR UPDATE don't have explicit lock mechanics. You can't check if that row is locked. You can't easily control the timeout. I consider it a bandaid technique. Bandaids are great for small boo boos, but they don't work if you already have a codebase that is complex and does things in an inconsistent order.
Think of these locks as mutexes in threaded code: they don't mean anything on their own, but they simply guard against the rest of your code. Contrived semi-pseudocode example:
def add_item_to_cart(order, item):
try:
lock = acquire_named_lock('order-{}'.format(order.id), timeout=10)
order.add_item(item)
order.recalculate_total()
order.save()
lock.release()
except LockTimeoutError:
raise OrderUpdateError('Timed out trying to add item to order.')
def checkout(order):
try:
lock = acquire_named_lock('order-{}'.format(order.id), timeout=10)
order.pay()
order.generate_and_email_invoice()
order.print_shipping_label()
order.save()
lock.release()
except LockTimeoutError:
raise OrderUpdateError('Timed out trying to check out.')
Essentially these are mutexes identified by strings that are managed by your RDBMS. That's all.
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.
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.
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!
Also, I found this and similar on SO: 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.
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-i...
[1] Repeatable Read won't detect collisions caused by phantom rows, so pick carefully. Most CRUD apps don't care about phantom rows.
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.
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 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.
If all your locking is single-transaction, then I think a better technique might actually to have a separate table where you have the individual lock IDs and use SELECT FOR UPDATE on the appropriate row there[1] -- only to obtain the lock. (Of course, most of the caveats you've spelled out in this thread still apply wrt. ordering, etc.)
Reasoning: Most named lock/advisory lock implementations don't participate in the current transaction, so you can end with "stuck" locks where the lock was acquired, but never released because the transaction before the "release" action failed. It goes without saying that this is not a problem that can be solved client-side, so you end up having to have "lock cleanup" (and how can you be sure that cleaning up a lock is actually the right thing, etc.).
[1] You might also have to UPSERT rows depending on how "dynamic" your lock IDs are, etc.
For those yet to discover it, a great tool is http://www.mcternan.me.uk/mscgen/ ... obligatory JS equivalent https://mscgen.js.org/
Higher isolation levels add read locks that can block concurrent transactions. That's the biggest practical reason everyone doesn't use serializable, but this practical guide doesn't really address it. There's not much discussion of the gotchas whereby you can accidentally break concurrency in an application.
For example there are somewhat surprising sources of contention, e.g. adding rows with foreign keys adds locks to the rows being referenced in foreign table, to stop them getting deleted. Or consider gap locks in queries with range predicates.
IMO the guide is mostly theoretical / introductory rather than practical. It's an OK starting point for someone who knows almost nothing about databases that are being used as shared state for a concurrent application.
Not in postgres. REPEATABLE READ doesn't add any locks, and SERIALIZABLE adds locks, but they're optimistic. So blocking isn't something you're going to see - the relevant thing to measure is going to be the rate of transactions being rolled back due to serialization failures.
Indeed. But your point was:
> Higher isolation levels add read locks that can block concurrent transactions.
Which isn't generally true, given the example of postgres.
Not in Oracle they don't. Reads in Oracle never block. You could argue that what Oracle calls serializable is in fact only snapshot isolation.
https://www.safaribooksonline.com/library/view/designing-dat...
It's a little bit twisted but I think you could write a pl/pgsql function that act as a client of it's own database using fdw or dblink.
This is however pretty twisted and might have side effect but I'm pretty curious about that.
Technically the author is still damn right because this will be using a client library at some point as he explain.