Not that I know of. We've been doing exactly that (SELECT ... WITH UR) for many years in a long-term project (>10 years), and until today I haven't heard of any issues with this approach.
On the other hand I have to say that in this case referential integrity was no issue but may become an issue for you. But there's not much you can do about it; if the WITH UR clause is mandatory for you, then I fear you will have to face those problems, should they arise.
Is this source table also a target table in your mapping (or another one running at the same time)? That's the only case in which I considered it necessary to use a WITH UR clause.
My source is DB2, then I do lokkup on some Oracle tables and update the same DB2 table(target).
All this in same mapping.
Interesting phenomenon I noticed is , when I add the 'with UR' clause in the mapping level, it still throws deadlock error.
I added the clause at session level it works fine.
Any info on this and on the original question?
Regarding this phenomenon: after having added the WITH UR at mapping level, did you remove the SQL Override at the session level completely? As long as there's an override defined in the session, the mapping override will simply be ignored.