When I lock a table, if another script is trying to access the same data, what happens if a client side browser is waiting on that data to return? Does it just hang there while PHP awaits MySQL to return?
Yes.
You only need to lock a table when there is the possibility of data conflicts.
There are some subtleties, but basically it works like this:
Never on a SELECT
--2 users should both be able to view the same record
NEVER on an INSERT
--You're the one creating the record, other users are creating their own records with no conflict
Question is on UPDATE and DELETE
You need to lock a record when AND ONLY WHEN two users have rights to update OR delete the same record.
For example: Suppose you assign unique userids and allow users to update their own user info:
UPDATE user SET ( your SQL HERE )
WHERE userid=$myUniqueUserId
Is a statement that doesn't REALLY require record locking. Can you see that there is no potential for conflict?
On the other hand, suppose you've got some sort of workgroup system where users can delete others' comments.
DELETE FROM comment WHERE id=1
Could easily conflict with
UPDATE comment SET text='$newtext' WHERE id=1
if many users have the right to update the same tables.
What happens without record locking?
The database queues the queries in the order it receives them. Last query in wins.
In the above case the DELETE would remove the record, and the user trying to UPDATE would update nothing since the record was gone
IF the commands were resequenced:
UPDATE would update the record, and DELETE would then delete the updated record
IF user one tried to UPDATE text='HI' and user two tried to UPDATE text='BYE', the record would be updated 2 times and the last query would set text=='BYE'. User 1 would be pissed.
PLEASE NOTE, however -- the results would be same even WITH record locking!
What you need to do is TEST THE ENVIRONMENT -- who would be confused or potentially pissed in the event of two users messing with the same data??
In other words in most PHP /MySQL type applications, it's better to manage record locking programmatically., and
A typical method is to create a 'lockedFlag' column. When there's a potential for conflict you
SELECT lockedflag FROM multiusertable WHERE id=1
IF($lockedflag){echo "Sorry, record is locked.";}
else {
mysql_query("UPDATE multiusertable SET lockedflag=1 WHERE id=1");
echo "FORM TO UPDATE RECORD...etc."
You need some method to remove the the lock if the user bails without saving etc.
In my experience record lockking isn't needed in 90% of the applications I've written.
This topic can be considered with a LOT more subtlety, however. Oracle, for example, runs seminars on record locking, and you have to pay to go.
Hope this helps.
