This problem cannot really be solved without application participation, because to keep the data up to date across joins at some point you need cross table lock

This problem cannot really be solved without application participation, because to keep the data up to date across joins at some point you need cross table locking, which would become highly inefficient. This is the whole cqrs thing by the way