6 ms·
Transactions Are Not Locks (2022)
- deleted 3y ago[deleted]
- SubiculumCode 3y ago[flagged]
- aeonik 3y agoDon't store the state like this. Just append an update to an immutable data store, and program the logic to unfold from the latest records. See Diatomic: https://docs.datomic.com/pro/getting-started/brief-overview.html https://docs.datomic.com/pro/getting-started/brief-overview....
- convolvatron 3y agoi like this model, but i think the goal was to enforce a constraint (non-negative balance) and fail the operation, reporting that result to the requester. how do you do that in the monotonic style?
- pico303 3y agoYou don’t. The original suggestion isn’t a good one for a constrained system like bank balances.
- btown 3y agoIn theory, in event sourcing, a reader would enumerate every proposed transaction, in order, and skip any invalid ones (or ones invalidated by a separate invalidation stream). This is expensive, so a materialized Balances table might be created and maintained - not by the database per se, but by a dedicated stream ingesting process. The user might also asynchronously see a set of failed operations - synchronous is harder, but doable if you block on polling for the stream processing result, or have an “await key” primitive. Or just wait a second or two to refresh the page. There’s a lot of machinery involved, and a subtle logic change or error could invalidate balances for your entire client base. The transactional approach, with event sourcing used as an audit utility used by humans to verify correctness, is often a better path.
- maxbond 3y agoThis episode of Signals & Threads discusses a system (an exchange for OTC securities) which is both event sourced and which has constraints. It works in the same way you've described modulo some implementation details. https://signalsandthreads.com/state-machine-replication-and-why-you-should-care/ https://signalsandthreads.com/state-machine-replication-and-... Most of us are never going to work with a system with the scale and SLA to necessitate this approach, but it's an enjoyable interview nonetheless.
- Salgat 3y agoA separate invalidation stream sounds like a really bad idea and is asking for race conditions. Just put the invalidation events on the same stream with the information necessary to revert the original transaction (such as the balance to restore in addition to stating the original transaction is invalidated); it's okay to denormalize on the same stream in these situations.
- convolvatron 3y agohaving a separate 'acceptor' log onto the same history actually works. thanks.
- jimbob45 3y agoThat sounds like you’re suggesting Event Sourcing?
- Spiwux 3y agoAre you suggesting everybody should use event sourcing instead of using transactions? Because the general consensus on event sourcing is "don't use it unless you absolutely have to."
- strken 3y agoUnfortunately financial systems are the one example everyone uses for transactions, and also the most obvious place where event sourcing is close to mandatory.
- kmeisthax 3y agoIf you maintained financial records the way that database example code says you should, you'd be a terrible accountant. Standard practice for accountancy is to record each transaction twice: once for the debited account and again for the credited account. You even create accounts for expenses and income; which are explicitly allowed to go negative (otherwise you couldn't record income). This is known as double-entry accounting, and it's equivalent to what you deride as "event sourcing". To put it back into the language of databases, double-entry accounting is the third normal form of accounting. You wouldn't store a "sum total of blog posts on a website" row in your database schema now would you? Why would you have a "sum total of transactions" or single entries for your transactions that only show that money came in but not where it went?
- crdrost 3y agoI think you are objecting to a poorly chosen example and that is okay. It _is_ a poorly chosen example. The exact pedantry that you are making is kind of poorly chosen, in double-entry bookkeeping you would say that you record the transaction proper either once or n + 1 times, depending on how pedantic you are being. (Double-entry bookkeeping discovered the feature that in filesystems is called “journaling,” you always write first to the transaction log, which is the “1” if you answer 1, then you update the n account ledger pages involved in the transaction with their individual deltas plus the transaction log ID.) A real bank also allows accounts to go negative all the time, which is another criticism. The “event sourcing” is actually just the transaction log, which is not double entry, which is why I say that the pedantry is poorly chosen. The ledger, in combination with “closing the books for the year,” actually instantiates a generic construction for persisting data structures, see lectures at [1], where you take periodic snapshots of a value to limit your worst-case reconstruction time while storing a tractable buffer of deltas. The point of the ledger thus is to denormalize the data to make it efficient to answer the question, “how did this client's balance change over time?” without seeking over the whole transaction log. [1] https://courses.csail.mit.edu/6.851/spring21/lectures/ https://courses.csail.mit.edu/6.851/spring21/lectures/
- yowlingcat 3y agoThis is not just an ignorant idea but a stupid idea. Just because you can't figure out the right way to do it in vanilla SQL doesn't mean it can't be done [1]. This strawman is just a different form of the NoSQL strawman [2]. Edit: Ah, looks like I've struck a nerve with the event sourcing Datomic fanboys. Enjoy using your golden hammer and turning every problem into a nail! By the way, if you ever make my way into my organization and waste my time with this nonsense, I WILL fire you. Nobody should tolerate your desire to solve already-solved problems with poorer quality solutions you have selected for their novelty. At that point, I'll kindly ask you to please explain to me what a write ahead log is and what it does, because if you don't, I'll happily explain it for you. [1] https://brandur.org/idempotency-keys https://brandur.org/idempotency-keys [2] https://www.youtube.com/watch?v=b2F-DItXtZs https://www.youtube.com/watch?v=b2F-DItXtZs
- paranoidrobot 3y agoRe your edit: I suspect that down voter's issue with your post is the aggressive nature of it.
- deleted 3y ago[deleted]
- CaliforniaKarl 3y agoFor the row-locking example, wouldn’t it be better to include both A and B in the SELECT, so that the FOR UPDATE can lock both records? Even if using strict serialization, I wonder if FOR UPDATE-ing both rows would help stop (or delay) other conflicting transactions.
- CraigJPerry 3y agoYeah without including A and B there’s a risk of deadlock
- rowls66 3y agoTo avoid deadlock, you need to ensure that locking happens in the same order in all concurrent transactions. So account A must be locked before account B and never in the opposite order. So if account B is the account being debited and therefore the account that needs to have a sufficient balance, then account A must be locked first. If account A is the account to be debited, then explicitly locking account B is not necessary. It will be locked when its updated.
- karmakaze 3y agoI think I agree but was hard to parse the message. My summary would be to always lock A and B in the same order (e.g. smallest account number first) regardless of which is being reduced or increased. In addition, an UPDATE statement counts as lock so doesn't need a SELECT ... FOR UPDATE if it's the 2nd one that you want to do.
- CraigJPerry 3y agoIf transaction 1 and 2 are running the example and the ordering is something like: txn1 = db.begin() # implicit exclusive row lock established here on A row, exclusive rather than shared read because we say for update currBalance1 = txn1.query('select balance from accounts where name = A for update’) … <snipped some of the example code> … txn1.execute('update accounts set balance = balance - $amount where name = A') # Concurrently tx2 begins, tx1 has locked row A so far txn2 = db.begin() # we’re exclusively locking row B in txn2 currBalance2 = txn2.query('select balance from accounts where name = B for update’) # This is the first point in transaction 1 where we take an exclusive lock on B, however we are deadlocked now txn1.execute('update accounts set balance = balance + $amount where name = B') The solution is what the parent suggested, take the exclusive locks on rows A&B at the same time because if transaction 2 is locking B&C you can’t afford to do 2 separate for update calls because b might get locked as you’ve just locked A and were about to lock B select balance from accounts where name = 'A' or name = ‘B’ for update
- paulddraper 3y agoBank accounts... It's always bank accounts
- bastawhiz 3y agoIn fairness, it's a great example. Moving money between accounts requires updating the state of at least two records. Moreover, nearly everyone working with databases has one or at least understands how a bank account should work. And as far as relevant examples go, I can't think of anything else where the correctness of the operation is quite as critical.
- yencabulator 3y agoIt's a bad example because bank accounts don't work like this. Accounting etc use an append-only ledger.
- throwawaybkkg 3y agoThere is no one true way that bank accounts work. I work for a big bank and I've come to accept that there are valid cases for UPDATING and even DELETING transactions in your Core Banking system. It is true that your general ledger is append-only, but that doesn't apply to what happens before you close your books.
- paulddraper 3y agoYeah, there's a reason and that's the reason. I just wish it were applicable to something else.
- ludston 3y agoIt's a deceptive example because banks are distributed systems (in that they interoperate with other banks/financial systems, and often have distributed internal database systems across multiple products too). Regular db transactions are insufficient for ensuring consistency across a distributed system, primarily because one of the systems may fail to commit their transaction after the other has succeeded. Even if there is only a single database, protecting data against concurrency issues is still non-trivial even with transactions.
- withinboredom 3y agoIf you have external side-effects, and your transaction fails, undo whatever you did in the external system. Don't just roll-back your database, but forget to "request your money back" from that system. And remember, don't use transactions when you cannot compensate the failure. For example, if you were to transfer money to an external bank, you probably can't just "undo" the sending of money. Your bank is on the hook for that money, so if, for example, the account you withdrew money from would now be negative ... you probably want it to still be negative if a failure happens ... instead of staying zero after rolling back the transaction. Otherwise, that other bank is going to come asking for that money you said you sent them.
- JoshGlazebrook 3y agoThis is a major pain point with dealing with Stripe. If you want to write a record in your local db (to record a cancellation), then call the stripe api to say cancel a subscription, if the api call fails, you can rollback your db changes by failing the transaction. If the api call succeeds, but your transaction fails to write to the db and rollsback, then what? You then have to rely on the Stripe webhooks to "sync" your local db state with what stripe has, oh but don't forget stripe webhooks don't have any guaranteed order in which they are delivered, so just ignore all of the event data, you have to fetch the current state of the stripe object your event is associated with and make sure the handling of these events are idempotent. It's not wise to rely solely on Stripe being the source of truth, but there is no way to really implement 2 phase commit with external services like this. Maybe you fire off an event into a pubsub or queue to handle the actual writing to your db, but what if the webhook gets to your service first? Shit is hard.
- tynorf 3y agoYou might be interested in this: https://brandur.org/idempotency-keys https://brandur.org/idempotency-keys
- yencabulator 3y agoYou record a want-to-cancel state, and then only after Stripe acknowledges the cancellation you transition from want-to-cancel to cancelled. State machines.
- karmakaze 3y agoFor complete/correctness: select balance from accounts where name = 'A' for update; > Now, other transactions won’t be able to read this row until our transaction is committed or rolled back (for update can only be used inside a transaction). This is true for SERIALIZABLE isolation. Depending on the configured isolation level (e.g. READ COMMITTED aka snapshot) other concurrent transactions can read (but not update) the locked row, which can lead to write skew.
- maxbond 3y agoETA: I misread, I thought you were referring to the table lock section, but you were referring to the row lock section. You're correct, FOR UPDATE locks do not prevent concurrent reads, which could cause a concurrency problem if you're not very careful (and Murphy's law applies). --- The article is correct. That section contains this snippet: lock table accounts; The default lock mode for LOCK is ACCESS EXCLUSIVE, which will prevent concurrent reads. ACCESS EXCLUSIVE (AccessExclusiveLock) Conflicts with locks of all modes [...]. This mode guarantees that the holder is the only transaction accessing the table in any way. https://www.postgresql.org/docs/current/explicit-locking.html https://www.postgresql.org/docs/current/explicit-locking.htm...
- yencabulator 3y agoThe part that has "for update" doesn't have "lock table".
- maxbond 3y agoMy bad, thanks.
- Alpi 3y agoFor update actually does block concurrent SELECT FOR UPDATES, so the article is correct
- karmakaze 3y ago
- deleted 3y ago[deleted]
- RDNI 3y ago[flagged]
- comvidyarthi 3y agoI don’t understand the title of the article. The article essentially talks about what RepeatableRead isolation levels of Postgresql cannot do and how to remedy that by Serialize Isolation. But both need lock at some level, and tradeoff performance guarantees for weaker/stronger consistency guarantees.
- karmakaze 3y agoIt's not a great article, as some of the clarification comments and discussions are surfacing. It is an import topic. Often reading the database documentation directly around Transaction Isolation and Locking sections will be more precise, complete and be specific for the database/version you are using.
- cryptonector 3y agoDon't look before you jump, instead look after you jump, because then you can rollback and undo the hurt :) You can do this with deferred triggers, for example. Here's where a COMMIT trigger would be handy -- something that runs when the client's COMMIT begins executing (though other triggers could still run in the COMMIT trigger were to execute any DMLs).
- redkin 3y ago[dead]
- redkin 3y ago[dead]
- grahamlee 3y agoLamport’s time/clocks paper solves this exact problem, using this exact example, without locks.
- juped 3y agoWhy did the author introduce the weird broken "move a negative amount of money" behavior and then never follow up on its brokenness? That was too distracting.
- gavinray 3y agoIn databases, "locks" have a very specific meaning. What engineers would call a "lock", is referred to as a "latch" in DBMS internals terminology. A "lock" then is a higher level abstraction that functions as a logical lock rather than a fine-grained physical one. Locks are generally used to control access to database objects like rows, pages, tables, etc, to ensure data integrity and transactional consistency. "Latches" are low-level synchronization primitives used to protect access to internal DB structures.
- nuttingd 3y agoThe check constraint solution isn't a solution to the concurrency issue: it only prevents a negative balance. The balance would still be wrong for the whole range where balance > 0. Two (positive) deposits would not store the correct ending balance.