So I’m making a php website that sometimes requires a big data input from an admin-type user. This would not be frequent and only would happen to update or add certain data to the DB.
During this upload time which probably will take a minute or two, I cannot have other Users try to pull the data and and manipulate it as they would do normally as that could cause some major errors.
One solution would be to stop the servers for a time and put the site under maintenance for and admin-type user to upload the data locally.
However, there will never be more then 1-2 Users at a time on the site and these updates are short (1-2 mins) and infrequent. This makes this situation very improbable but still possible.
I was thinking of making some sort of entry in the User table that an admin could toggle before and after an update, and that my code would check before every User data manipulation. Then if after a User tries to save while that value is on, they would just have a pop-up or something that tells them to wait a few minutes.
Would this be OK? Is there a better way of going about this?
Advertisement
Answer
There is a way of locking a table so as to be the sole user of that table.
There includes READ locks and WRITE locks but in this situation a WRITE lock would probably be the solution.
A WRITE lock has the following features:
- The only session that holds the lock of a table can read and write data from the table.
- Other sessions cannot read data from and write data to the table until the WRITE lock is released.
To lock a table in mysql, simply use:
LOCK TABLE table_name WRITE;
If needed for more than one table, simply add them with a comma:
LOCK TABLES table_name1 WRITE,
table_name2 WRITE,
... ;
When the queries are finished, you can simply use UNLOCK TABLES; to unlock the tables.
Visit https://www.mysqltutorial.org/mysql-table-locking/ for a more complete tutorial on mysql table locking.