* Databases are not as transaction-safe (in their default setup) as one is led to believe. They are ACID for some non-isolated definition of I. It's popped up a number of times in the last few months on HN and was news to me: https://news.ycombinator.com/item?id=38736904
* Even if an ACID transaction protects you from concurrency issues, it won't protect you (more specifically - your data) from logic issues. Bad code will make your database data wrong, even after you push a fix to the code.
* The transactions can only span one database, which can limit your system design a bit.
Embrace eventual consistency and use a persistent, append-only message queue, and these problems go away. The downside is you'll be accused of chasing fads and doing resume-driven development ;)
Yes they are. Transaction-safe does not mean "protecting against concurrency issues". It just means that a transaction either succeeds or fails, nothing more and nothing less. Then there is isolation which is an orthogonal thing. So let's use the correct wording here.
> it won't protect you (more specifically - your data) from logic issues
Nothing protects against logic issues though.
> Embrace eventual consistency and use a persistent, append-only message queue, and these problems go away
I agree that eventual consistency should be embraced, but it doesn't make the mentioned problems go away. How would a persistent, append-only message queue allow for solve the C of CAP while retaining A and P? Of course it can't. How does it protect you from logic issues? Of course it can't.
> Transaction-safe does not mean "protecting against concurrency issues".
That is the I in ACID.
> It just means that a transaction either succeeds or fails, nothing more and nothing less.
That is the A in ACID.
If you can say isolation is orthogonal, I can say atomicity is orthogonal.
In any case, I was refuting the idea that transactions in a standard ACID database would protect you from, e.g., two message queue workers picking up and processing the same message when they are not supposed to. But if that's not a part of 'transaction-safe', then you're essentially backing up my point.
>> it won't protect you (more specifically - your data) from logic issues
> Nothing protects against logic issues though.
That's a given. If you destructively update your business data with bad logic, the data's gone. You're screwed. You probably won't even know it happened until you get complaints. And once you get complaints, you won't know how many rows in your database were affected, or what their values should be.
With an append-only MQ, you will let a few bad actions happen - logic errors, and consistency errors resulting from too much 'eventual'. You can spot these actions since you didn't delete them. Then you can append the corrections to the MQ.
That is isolation. It's the first time hearing someone call that "transaction safety". In my opinion they are not synonyms. If anything, transaction safety would be something that the database authors have to consider, not the user of a database. A quick google search and chatgpt question agree.
> That is the A in ACID.
Yeah, that is what I assumed you meant when you said "transaction safety".
> If you can say isolation is orthogonal, I can say atomicity is orthogonal.
Yes you can indeed.
> In any case, I was refuting the idea that transactions in a standard ACID database would protect you from, e.g., two message queue workers picking up and processing the same message when they are not supposed to. But if that's not a part of 'transaction-safe', then you're essentially backing up my point.
Then I think it's just a language matter. There is no automatic guarantee against e.g. two message queue workers picking up the same message. There is no guarantees like that in general for transactions and there is fine-granular control with things like "read for update" or "skip locked" for that. I don't think any database ever claimed otherwise.
However, using databases allows the user to opt into these things.
> With an append-only MQ, you will let a few bad actions happen - logic errors, and consistency errors resulting from too much 'eventual'. You can spot these actions since you didn't delete them. Then you can append the corrections to the MQ.
Or, you allowed a user the withdraw cash at two ATMs at the same time, and now their balance is negative even if it must not ever be negative. You just broke the law and might use your banking license and go bankcrupt.
So yeah, in those cases an append-only MQ simply doesn't help you. You need some system that supports ACID.
> That is isolation. It's the first time hearing someone call that "transaction safety".
I wouldn't call isolation transaction safety. ACID is transaction safety. Isolation is a subset of transaction safety. 'Safe' isn't even my word. I was replying to a comment. Can you honestly say that you'd substitute:
Most users want to use a message queue in a transaction safe way, so they ended up implementing it in database.
with:
Most users want to use a message queue in an [atomic] way [but not necessarily consistent, isolated, or durable - which are orthogonal concerns], so they ended up implementing it in database.
From this point forward I honestly can't follow the line of argument:
> If anything, transaction safety would be something that the database authors have to consider, not the user of a database [a]
> There is no automatic guarantee against e.g. two message queue workers picking up the same message. There is no guarantees like that in general for transactions [b] and there is fine-granular control with things like "read for update" or "skip locked" for that [c]. I don't think any database ever claimed otherwise [d].
So: the user doesn't need to think about their transactions' safety [a], but if they choose to think about it, they can opt-into safety features [c], but those features won't work as advertised [b]. Actually they weren't advertised [d].
> Or, you allowed a user the withdraw cash at two ATMs at the same time, and now their balance is negative even if it must not ever be negative.
Firstly, that's call an overdraft and it's business-as-usual for ATMs. ATMs (and banks in a broader sense) track money using eventual consistency (ledgers & reconciliation), not transactions (in the 'SQL DBMS Transaction' sense). [2]
Secondly, why did you pick the two-ATMs-at-the-same-time hypothetical if you're downplaying isolation as a transaction safety concern? That is the purpose of isolation. If your system succeeds at one-thing-at-a-time, but fails with two, isolation is the property which has been violated.
> You just broke the law and might use your banking license and go bankcrupt.
Allowing overdrafts does not break the law. You know what breaks the law in banking? UPDATE statements. Mutations which destructively overwrite banking data. Moving customers' money around by mutating balances in-place. Which is why they don't work that way [2, 3].
> So yeah, in those cases an append-only MQ simply doesn't help you. You need some system that supports ACID.
And here we've come full circle! I can start quoting from the top again:
>> Databases are not as transaction-safe (in their default setup) as one is led to believe. They are ACID for some non-isolated definition of I.
I think we can cut it short. Your OP never claimed that databases automatically apply the desired acid properties. I think his point was simply that's it's possible with them and not without them (without building your own acid system). No need to discuss it further.
I think you merely interpreted them in a very unfortunate way.
There are Transaction Coordinators that will allow you to span transactions across multiple systems - databases, OR MQ Brokers.
This means you can begin a DB transaction, enlist the MQ GET operation, and then commit or rollback the whole lot. It obviously slows things down a tad, but ensures consistency across all actors.
* Databases are not as transaction-safe (in their default setup) as one is led to believe. They are ACID for some non-isolated definition of I. It's popped up a number of times in the last few months on HN and was news to me: https://news.ycombinator.com/item?id=38736904
* Even if an ACID transaction protects you from concurrency issues, it won't protect you (more specifically - your data) from logic issues. Bad code will make your database data wrong, even after you push a fix to the code.
* The transactions can only span one database, which can limit your system design a bit.
Embrace eventual consistency and use a persistent, append-only message queue, and these problems go away. The downside is you'll be accused of chasing fads and doing resume-driven development ;)