Updating main table from different temp tables using stored procedure - MySQL

Question

I am having the following architecture where there are multiple python process writing data into temp tables in batches.

For simplicity, lets consider there are two python process (p1 and p2) and it pushes the data into 2 temp tables each as shown in the diagram.

I need to create a stored procedure which will fetch the data from temp tables (t1, t2, t3 and t4) and update the main table.

Questions:

if i create two stored procedures, (ie. one of each process) , will there be a lock while writing to database. How do we handle that.

For example, both P1 and P2 trigger at the same time and uploads data in temp tables. Now, if we two stored procedures S1 and S2 getting triggered. S1 writes the data into main table. However, S2 will get a WRITE_LOCK (my assumption).


If we have only one stored procedure overall, still the data will be updated sequentially and the python job P2 will have to wait until P1 is getting copied. 

The data we are handling is in GBs and has millions of records. 

Can you please suggest a suitable solution for this design ?

question from:https://stackoverflow.com/questions/66056963/updating-main-table-from-different-temp-tables-using-stored-procedure-mysql

Answer 1

Waitting for answers