Ordering is key to solving deadlocks in databases nearly every time. I have to employ read-modify-write inside a single query with CTEs in order to ensure data integrity and build up a vector of errors for why an insert or update fails integrity checks for potentially incorrect outside data that spans multiple records and obsessive use of ordered writing, reading and modification has been absolutely required along with explicit write locking. I wish foreign keys et al allowed a degree of composition that would allow me to say “You forgot X, Y and Z” instead of erroring out at the X and exposing the developer to a game of whack a mole. I’ve played with the idea of allowing any write, marking the record as unverified, then letting an async process verify and log errors to a column on failure, but that makes determining simple errors that accumulate very difficult. And implementing foreign key and other tree integrity checks in user code is a recipe for out of date code disasters. Ugh.
Yeah, I’ve often been frustrated with the lack of machine-readable error handling in databases. With check constraints you often end up duplicating the checking logic in the code so you can first do a query to see “would inserting/updating this record violate a constraint?” and then report the error in a sane way. Of course this isn’t race-condition proof. I don’t know of a better way to do it, though.