phpmyadmin Question posted in
The official documentation can be found here.

How to efficiently sync data between multiple clients with MySQL? – Phpmyadmin

I am setting up an arduino based network that has multiple controllers (phones, web page, PC app) to configure a relay system (base). This network uses MySQL database to store and sync configuration between clients and the base. All clients and the base can manipulate configuration and clients should see real-time configuration of the base. It means if a client changes the configuration, all other clients and the base should see this change. How can I sync data in this network and avoid conflicts? Is there any efficient algorithms?

I used database server’s timestamp to track changes. Whenever db changes, it saves timestamp. All clients get this timestamp and compare it to last fetched one. But the problem is when 2 or more clients access this database simultaneously, conflict occurs and I loose configuration changes of all clients except one.
Using timestamp doesn’t seem to be a good idea. If the base goes offline, we can’t save the changes made on the base during offline time. Because as soon as it gets back online, it fetches configuration from database.
Sorry for my poor English.

Tags:

2

Answers


  1. 0

    The database should be the synchronization point: before making an update the clients should lock the data in a transaction, make their changes and then commit them.

    One of the way to lock the data is to add for update to your select statements. There’s more information in the MySQL documentation

    Login or Signup to reply.
  2. 0

    If you have unreliably connected devices, and multiple clients modifying a common set of data, you inherently have a fairly complex application design.

    One way you could do this is to store history in your table.

    So, instead of

    ID     config_1    config_2 ...  timestamp
----------------------------------------------
1          true         44         24-may-2019:20:00:02
2          false        88         26-may-2019:10:10:06

    You might do

    ID     config_1    config_2 ...  timestamp
----------------------------------------------
1          true         44         24-may-2019:20:00:02
1          false        44         21-may-2019:11:34:34
1          false        22         10-oct-2018:19:22:22
2          false        88         26-may-2019:10:10:06

    In this case, you’re not updating records so don’t need to worry about locking – you simply insert new values, and “the last one wins”.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search