For the first time, I am going to need to implement table locking with mysql. I was hoping to get some feedback as to whether or not this process will do what I need.
I have a table that visitors will read data from. I need to allow administrators to modify this table. The thing is that it is crucial that visitors to not get partially modified data.
My idea is to lock the table from being written to while visitors are reading it. Then, when admin need to make changes, the table is locked from being read.
visitor.php:
- Lock Table Write
- Query to retrieve data
- Unlock Table
admin.php
- Lock Table Read
- Query to modify data
- Unlock Table
I have a few questions about this process. First, will the "admin.php" lock wait for all visitors that have it write locked to be done (In other words, does MySQL handle the threading of this automatically)? I would try to test this myself, but the queries only last a second, and it's difficult to simulate multiple requests to the server at the same time. Second, what happens if a visitor locks the table, and something interupts the script before it can unlock. Will it remain locked until the server is rebooted? Third, I thought that myisam tables allow locking, but I've read some posts that people say only innodb allows locking. I'm using myisam now, and don't want to change unless necessary.
table locking
Moderator: General Moderators