Table User: userid, etc

Table Resources: resourceid, userid, etc

If I want to restrict deletion of a user to only be possible after all the resources are deleted, I'm forced into using higher-than-default isolation levels in most DBs. This has significant performance implications. It's also much easier to make a mistake - for example, if when creating a resource I check that the user exists prior to starting the transaction, then start the tran, then do the work, it will allow insertion of data into a nonexistent user.

Add your user check as a where clause on the resources insert?

Can you give an example? I’m not aware of a mechanism like that that will protect you from concurrency artifacts reliably - certainly not a general one.

start transaction;

select id from users where id = ? for update;

if row_count() < 1 then raise 'no user' end if;

insert into sub_resource (owner, thing) values (?, ?);

commit;

??

Do that in most relational dbs in the default isolation level (read committed), and concurrently executing transactions will still be able to delete users underneath you after the select.

If we take postgres as an example, performing the select takes exactly zero row level locks, and makes no guarantees at all about selected data remaining the same after you’ve read it.