I am using a migration tool that is explicit calling this MySql function to migrate tables in ‘cluster mode’; meaning that it holds a lock until all migration scripts are executing, just to avoid that another microservices isn’t doing the same at the same time for example.
Any suggestions on using another default function in Singlestore? Or do we have to implement a stored procedure that does the same (create the table, insert a record, dropping the table when everything is done, then trying to insert a value with the same PK => this would fail if the same migration is already running).
If you want multiple fine-grained locks that you can control from the application level, something like you describe would make sense.
You could create a table with multiple rows, one row per lock, and use SELECT FOR UPDATE with a filter to find just one row in that table, to serialize work around that lock.
If you just want to make a database read-only for a while, do some SELECTs from it, then make it read-write again, use FLUSH TABLES WITH READ LOCK; then UNLOCK TABLES; as described here.
Thx for your advice. I will go for the table and the updates. I don’t want to lock (read or write) the data itself. I just want to make sure only one replica of our microservice performs the schema updates (they will always be compatible with previous - adding nullable fields, new tables, new indexes etc).
Thanks for your help. I will try to create a table with multiple rows, one row per lock, and use SELECT FOR UPDATE with a filter to find just one row in that table, to serialize work around that lock. But for now I am little bit busy because I am writing an assignment on health care by taking help from https://www.topessaywriting.org/samples/healthcare here. After completing my assignment, I will surely get back here and will do as you share in your post.