I’d like for a client session to lock a table, so that it can continue to read from and write to that table. At the same time other client sessions should only be able to read from the table. Once the lock is removed, then all client sessions should be allowed to read from and write to the table. Also once the lock is removed, all client sessions that tried to write to the table while it was locked should execute. I don’t want any errors thrown on attempted inserts during the lock.
I know that a read lock wouldn’t work because the client session that creates the lock can’t write to the table while the lock exists. Also a write lock wouldn’t work because other client sessions can’t read from the table. Is there any way for me to accomplish this?
Answers:
Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.
Method 1
You can’t do it with table locks using the LOCK TABLES statement, for the reasons you stated in your question.
You could do it by creating row locks on all the rows of the table:
SELECT COUNT(*) FROM mytable FOR UPDATE;
The row locks would block any other sessions from writing until you commit your transaction, but they could read as long as they don’t try to use locking read queries. Meanwhile, you could read or write the table since you already have locks on all the rows.
Another solution is to get a global lock, but don’t use LOCK TABLES. I’ve coded systems where all participating clients need to acquire a lock with the GET_LOCK() function before writing to a table. But if they only need to read, they just skip the request for that lock. This would work, but it would require all clients to include code to support the same access policy.
All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0