You may already be familiar with the syntax:
select ... for update skip locked;
It appeared (internally) some time back in Oracle 8i to implement some features of advanced queueing, but was only legalised and documented in one of the more recent versions of Oracle. If, like me, you checked the manuals to understand what this did you probably came to the conclusion that it allowed you to issue a query that selected, locked, and returned the rows in a table that matched your query and were not already locked. This isn’t quite correct – the rows your query selects, locks and returns are the ones that can be locked … and that may not give you the results you expect.
I’m in Detroit at the moment, about to start a consulting assignment, and Mark Bobak mentioned this one to me on the drive into town from the airport. As he said: “It’s one of those things that’s obvious after you’ve worked out what’s going on.” If you want to read more, here’s a link to the blog item he wrote about it.