I have a table where 9 out of 10 columns are accessed and updated only by app "A". The tenth column, however, is only accessed and updated by app "B".
App "A" does not make very many updates (about 1 every 5 minutes). These changes must get commited to the database quickly.
App "B" updates the table several times a second. These changes are less critical and some waiting time is acceptable.
My problem is that all the updates from app B to the 10:th column, makes the changes that app "A" wants to make to column 1-9 slow. I guess this is because Oracle locks the entire row for each update from app "B".
The obvious solution to this would be to split the table in two where columns 1-9 end up in one table and column 10 in another, with a one-to-one relationship between them.
But I would really like to keep it all in one table, to keep the data model as clean as possible. So: could there be some other solution to this. Can the connection from app "B" somehow have a lower prio? Or can this be solved by setting the tranaction isolations differently in app "A" and "B"?
Well, if you are up against hard locking-constraints you might not have any other choice. If the timing and throughput requirements of the application(s) are not being met under-load, then a structural change might be necessary.