Threading and insert/update issues.
-
I have a database that receives weather information. The main table is (somewhat simplified) LocationID int, Hour int, HiTemp Float, LoTemp Float, TimedTemp Float Temperature data is first written to a holding table - LocationID int, Hour int, whichVariable int, value float. Then it is merged into the main table (using a MERGE statement) All this works fine when the temperature data written to the holding table goes in one variable at a time, executes the MERGE, loads the next variable, etc. If I have multiple programs adding multiple locations and different variables all at the same time, the system deadlocks. I could force one instance of the outside program inserting the data to use a mutex to guarantee undisturbed calls to MERGE but it is quite possible we will have multiple instances of the outside program running. What is the best approach to take to allow merging of the data?
-
I have a database that receives weather information. The main table is (somewhat simplified) LocationID int, Hour int, HiTemp Float, LoTemp Float, TimedTemp Float Temperature data is first written to a holding table - LocationID int, Hour int, whichVariable int, value float. Then it is merged into the main table (using a MERGE statement) All this works fine when the temperature data written to the holding table goes in one variable at a time, executes the MERGE, loads the next variable, etc. If I have multiple programs adding multiple locations and different variables all at the same time, the system deadlocks. I could force one instance of the outside program inserting the data to use a mutex to guarantee undisturbed calls to MERGE but it is quite possible we will have multiple instances of the outside program running. What is the best approach to take to allow merging of the data?
Why are you using merge instead of an insert?
Never underestimate the power of human stupidity RAH
-
Why are you using merge instead of an insert?
Never underestimate the power of human stupidity RAH
The routine uses the MERGE statement to determine if a record for the particular observation exists. If it does not, it inserts a new record. If a record does exist, it updates the appropriate field. Problem comes in that there are three raw import records for the particular observation. So the first merge may try to insert something for a high temperature while at the same time the system is trying to insert for the low temperature. I can use a cursor to cycle through the imported data row by row but want to avoid the speed hit. I also can't change the main table to hold a record for each individual type of temperature because of the huge number of records (~17 million per observation cycle)
-
I have a database that receives weather information. The main table is (somewhat simplified) LocationID int, Hour int, HiTemp Float, LoTemp Float, TimedTemp Float Temperature data is first written to a holding table - LocationID int, Hour int, whichVariable int, value float. Then it is merged into the main table (using a MERGE statement) All this works fine when the temperature data written to the holding table goes in one variable at a time, executes the MERGE, loads the next variable, etc. If I have multiple programs adding multiple locations and different variables all at the same time, the system deadlocks. I could force one instance of the outside program inserting the data to use a mutex to guarantee undisturbed calls to MERGE but it is quite possible we will have multiple instances of the outside program running. What is the best approach to take to allow merging of the data?
Have only one thread perform the MERGE portion?
-
I have a database that receives weather information. The main table is (somewhat simplified) LocationID int, Hour int, HiTemp Float, LoTemp Float, TimedTemp Float Temperature data is first written to a holding table - LocationID int, Hour int, whichVariable int, value float. Then it is merged into the main table (using a MERGE statement) All this works fine when the temperature data written to the holding table goes in one variable at a time, executes the MERGE, loads the next variable, etc. If I have multiple programs adding multiple locations and different variables all at the same time, the system deadlocks. I could force one instance of the outside program inserting the data to use a mutex to guarantee undisturbed calls to MERGE but it is quite possible we will have multiple instances of the outside program running. What is the best approach to take to allow merging of the data?
Here's an idea: Set up a table that is a queue table. Just insert all the data into this table - no merge needed. Then you have one process that reads from this table performing merges and deleting the row from the queue once the merge has been performed. As you are only performing inserts then deletes on the queue I can't see a deadlock occurring on the queue - as the process will be issuing one merge at a time you should avoids deadlocks on your merge table too.
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
-
Here's an idea: Set up a table that is a queue table. Just insert all the data into this table - no merge needed. Then you have one process that reads from this table performing merges and deleting the row from the queue once the merge has been performed. As you are only performing inserts then deletes on the queue I can't see a deadlock occurring on the queue - as the process will be issuing one merge at a time you should avoids deadlocks on your merge table too.
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
I used a mutex to control the calls to the MERGE sections and it is running. If performance becomes and issue, I will split the program in two so a multi threaded section pulls data and inserts to a temporary table and a single threaded program calls for merging the data.
-
I used a mutex to control the calls to the MERGE sections and it is running. If performance becomes and issue, I will split the program in two so a multi threaded section pulls data and inserts to a temporary table and a single threaded program calls for merging the data.
Sounds good - I always revert to the old carving code in stone method when all else fails so I hope the mutex works:thumbsup:
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens