Hi,
I have 35 functions that update 1 to 3 tables, in a transaction. However, they don't just execute updates, but they do queries as well.
Table 1 does row update only actions, and some queries. Table 2 does queries and row level updates to existing rows, and sometimes deletes and adds rows. Table 2 may have queries within the transaction before and after the row deletions and or insertions, and or updates. Table 3 does row updates based on the results of Table 2 actions above.
My first action will be to make sure the table 3 updates are all done at the end. Table 1 is independent of the other 2 tables, and probably can be first or last. So Table 2 must come before table 3 changes.
My first concern is that with Table 2 actions, I do queries, then updates, then more queries, then more updates sometimes. Do I have to reorg my code not to do this.
My second concern is that Table 3 is the table that with servlet threads has to be fast. Table 3 is used for simply queries by itself. But it seems like row-level locks will stop those queries.
If I have to, I can put the table set maintainence code described above in a single cluster-wide process, from one thread. The speed of updates does not matter. Just that the queries against table 3 are fast. And that there's never any deadlock.
I don't know differences between Oracle and Innodb, so I have questions there. (I plan to upgrade to Oracle later.)
Basically, I'm looking for pointers about what to watch out for. Of course, I could force a full table lock on table 2 and then table 3 at the beginning of each updater function, but then my Table3 servlet query thread would suffer. So that doesn't seem like a solution.
Also, I'm worried about just relative to table 2 itself, that some functions do quereies, updates based on the query, new queries based on the update, and then more updates, including flowing results thru to updates to table 3. This seems really nasty.
Recommendations? Andy
There could be simultaneous updates to the same rows of 2 tables, and I have taken care to hit the tables with the updates in the same order. One of the tables has 2 indices, and seems like a table lock is needed to update the index? Some of the functions query table 1, update table 1, then optionally query table 2, then update table 2, in a repeat loop. This table holds all the parent child relatioships in a tree of all my content, so it is high volume updating across all the users.