Here’s a problem I solved for a client recently:
In a test-run of a mixed OLTP system with a bit of degree of concurrency, the test would stall from time to time with many sessions (at one point 80 of them) waiting on event: “enq: TX – row lock contention”.
The statement being run by the sessions reporting the wait was always the same: “update tableX set foreign_key_column = :b2 where primary_key = :b1″ – and there was no way that two sessions were trying to update the same row (I know because I set an autonomous trigger to catch all the values on every attempted insert, update, and delete – and because I know the way the code test is supposed to work).
After thinking about it for a bit, I decided to re-run the test after executing: “alter system set shared_servers = 300″. That solved the problem.
So, if you take the empirical approach to tuning Oracle systems, would you now use this method for dealing with waits for that event ? ;)