I'm struggling to understand what the issue that the author is getting at. The point of a database is that it's ACID compliant, wrap insets/updates/deletes in a transaction and no such drift would occur. What am I missing?
I don't think you are missing anything. I think he is just pointing out that technically nothing is enforcing this synchronization, so if someone forgets to wrap things in a transaction, it could get out of sync.
Depending on your DBMS and isolation level, using a transaction might not fix things. That being said I don’t think (at least for Postgres) most people are using an isolation level that could cause this.
Much more likely I think is that you can’t use the db to prevent invalid states here (unique constraint, etc) and you’re dependent on other areas of the code correctly implementing concurrency controls. Race condition in resource A causes problems in your permissions table now.
And just from a general engineering perspective, you should assume things are going to fail and assess what your path forward looks like when they do. Recovery script sounds like a good idea for a critical area.
I just want to point out you have to take care about that, yes you can have a trigger or a transaction to make sure it happens but it isn't there out of the box
charcircuit|2 months ago
ahsisibssbx|2 months ago
Much more likely I think is that you can’t use the db to prevent invalid states here (unique constraint, etc) and you’re dependent on other areas of the code correctly implementing concurrency controls. Race condition in resource A causes problems in your permissions table now.
And just from a general engineering perspective, you should assume things are going to fail and assess what your path forward looks like when they do. Recovery script sounds like a good idea for a critical area.
eliocs|2 months ago