I am working on a web app that will receive meter readings from our customers’ electric submeters. Data will be sent as JSON strings from the computer that reads the meters to a web app on a VPS which will send the data to a mySQL database. Many of our customers only have only one or two meters but some have up to 300. One has 800. I am concerned that if two large facilities transmit their data at the same time I may have conflicts with the locked tables, What is the best way to handle this? I would like to plan for this before the system is built and runs into problems. All my previous db experience has been with very small systems, 3 users max.
There is no need to lock the tables and you certainly dont need transactions. Keep the web application responsive by doing little or no processing, simply receive the data and write it to a table. Have a service that reads the table (again no locking) and process it.
if you’re only adding new records, there will be no locking table problem.
it’s only when you modify records that you must handle them.
you can simulate it in enormous amounts with a desktop app that connect via tcp to your web app and send the http header + json data.
i would try if a transaction with collected data for a time is faster.
what is the time span of this meter readings?