How can I ensure the user can insert and edit data while updating table

Question

I am designing schema. Any idea is welcome!

Right now I have tableA, tableA_cache, tableB, tableC, tableD. If any row insert to tableB tableC or tableD, the trigger will insert data to tableA.
For data integrity, periodically I have to delete all the rows in tableA then re-populate data from tableB, tableC...etc.

Normally, user query tableA_cache so that even if tableA has no data, user can query tableA_cache. However, during re-population, tableA will be keep writing. At the same time, if user insert a row in tableB (or C, D), the trigger should insert data to tableA. Since tableA is locked, the response time for insert will be extremely slow.

Is there a better way to handle this case? or a direction I can look into. (There is no guarantee that in specific timing no user will use the system.)

Answer 1

Get rid of all the complex design.

Plan A: Get rid of A and A_cache and the trigger and the rebuild; run queries against B and C. Perhaps you need better indexes? Or "Summary tables"?

Plan B: (More directly in line with your design.) Get rid of A_cache. Do this, which will avoid A ever being unreachable:

1. Create A_new from B and C
2. RENAME TABLE A TO A_old, A_new TO A; -- atomic and instantaneous
3. DROP TABLE A_old;

Answer 2

You can use SERIALIZED LEVEL of transaction for data integrity.
SQL:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
START TRANSACTION;
...make you sql job...
COMMIT;
SET TRANSACTION ISOLATION LEVEL <as needed>;