Yes, thats why I think it's a huge problem for the industry. How the heck is it possible that so many developers with bachelor and master degrees which otherwise do a reasonable job don't even know that they have a dangerous knowledge gap there???
through that:
> in the worst case customers seing other customers data because of the wrong transaction levels.
should not happen even with wrong transaction levels, that indicates some additional serious design problems IMHO, likely related to premature optimizations
I feel fairly competent but would definitely struggle to debug a transaction isolation problem. I think part of the issue is that I have always operated at an ORM level, on personal projects and at large (>400 dev) companies.
From my understanding getting this kind of error would involve something going wrong at a pretty low level? I am not sure how a developer could cause this at the ORM level.
It's very easy to cause in the ORM because by default a chain of SELECTs may ready different committed data unless you add row level locks like FOR SHARE . In a horizontally scaled backend you need these locks usually, but it takes scanning the DB log to figure out how to get them in the right place.
You can't effectively use an ORM without detailed knowledge of the generated output unfortunately. It does not add locks for you, so it's probably just wrong in prod when horizontally scaled and requests span multiple statements.
This is one of the many problems I have with ORMs, you end up needing to know more about the ORM and the database than you would need to know if you just used stored procedures.
I don’t recall ever having a transaction isolation issue with my busy stored procedures - though I often use SELECT .. FOR UPDATE which is maybe cheating because an ORM can’t do that efficiently - but I’ve certainly seen them in ORMs.
through that:
> in the worst case customers seing other customers data because of the wrong transaction levels.
should not happen even with wrong transaction levels, that indicates some additional serious design problems IMHO, likely related to premature optimizations