I am using xampp - php 5.2.1 and MySQL 5.0.37 with Innodb storage engine.

I am writing a multi user appointments diary. The main page shows today and tomorrow with reserved time slots and also free time slots. You can also navigate to other future dates.

When a user clicks on a timeslot they either create a new appointment or edit the existing one.

I need to stop two people doing the same thing at the same time. i.e. stop both people editing / creating the same appointment. From my 'c' programming days I would have locked a part of a file while I did my update, so I'm thinking about locking a database row unless there is a better way.

It would be necessary for users to view the existing apointment while someone was editing it.

This is an interesting situation. I don't think you should do a formal database lock but rather an implied one through PHP. Add a column to the table (like a timestamp) indicating it's being edited. The first PHP attempt to edit that record inserts the timestamp. When that PHP user finishes editing the record, the timestamp is cleared. This creates a virtual lock.

Then, on the PHP side, you can indicate to a user if a record is currently being edited by checking that timestamp column. You can also create an automated process that unlocks records that were never cleared.

That's how I would do it off the top of my head.

Best Wishes,
Larry

Writer/Web Developer/Instructor
Forum Moderator

Hi Larry,

Thank you for the prompt reply.

It did cross my mind to do what you have suggested, if for some reason the user that is editing the record loses the database connection (e.g. closed the browser window without finishing the edit), then no one could edit the record that was 'php locked' until the lock was cleared.

I assume that formal database locks are unlocked when connections are lost.

Then you have to determine when to clear the locks; of course you wouldn't want to clear a lock that was in use.

There would be other situations where a database row lock would be useful for example updating stock records, updating staff records etc etc.

I guess that I could expand on your suggestion i.e. allow a user two minutes to finish their editing, if another user comes along after two minutes let them take a 'php lock'. I'd need to record their timestamp lock and compare before updating the database to ensure that they still 'owned' the 'php lock'.

I was hoping for a simpler way though.


(I also quickly re-read the Transactions section of the book for inspiration.)

> It did cross my mind to do what you have
> suggested, if for some reason the user that is
> editing the record loses the database connection
> (e.g. closed the browser window without finishing
> the edit), then no one could edit the record that
> was 'php locked' until the lock was cleared.

That's true. That's why I thought you'd use an automated process to free up locked records. Although I would think it'd be more likely that the user stops editing the record rather than lose a DB connection.

> I assume that formal database locks are unlocked
> when connections are lost.

I don't know offhand. That'd be something to look into, of course. The related issue is getting a lock to last beyond the connection. Because when the PHP script has finished running, the DB connection is closed. This would normally unlock the row, even though it should still be locked because the user is currently editing that.

Best Wishes,
Larry

Writer/Web Developer/Instructor
Forum Moderator

After taking advice from several parties, here is my solution (as yet untested, but the theory seems right – unless anyone advises otherwise!!!):

Incorporate the following columns in the table:
LOCK_KEY (UNSIGNED INT up to 4,000,000,000 ish)
LOCK_EXPIRY_TIME (5 minutes into the future).
Depending on your system, you might also add USERNAME so that you know who has got the lock.

When a user wants to edit a row, first check that there isn’t a current active LOCK_EXPIRY_TIME, if the current time is past the expiry time, or there isn’t an expiry time set, then the user can set the lock.

Setting the lock
~~~~~~~~~~~~

Generate a random number or string to use as the LOCK_KEY. This must be retained by the script (php variable $_SESSION[‘lock_key’]) and will be entered into the database shortly.

Get the time and add 5 minutes (or however long you want LOCK_EXPIRY_TIME)

Update the row with the LOCK_KEY and LOCK_EXPIRY_TIME, WHERE id = id_to_update AND there is not a current LOCK_EXPIRY_TIME obtained by another user that was acquired just before we tried to get our lock.

We now have the lock for 5 minutes before it is available to other users.

Updating the row
~~~~~~~~~~~~~

Check that the LOCK_EXPIRY_TIME hasn’t passed and that LOCK_KEY = $_SESSION[‘lock_key’]

Re-take the lock (as above “Setting the lock”) because if it’s 4 minutes and 59 seconds until expiry we may not have enough time to do our UPDATE.

If the expiry time has passed, and the LOCK_KEY in the database is the same our $_SESSION[‘lock_key’] it means that no-one else has tried to lock this row, we can therefore re-acquire the lock with an UPDATE …. WHERE WHERE id = id_to_update AND LOCK_KEY = $_SESSION[‘lock_key’]

Re-check LOCK_KEY to $_SESSION[‘lock_key’] to ensure that no-one acquired the lock just before we attempted to re-take it. If they did display error message.

Once the row has successfully updated, or even as part of the UPDATE, SET LOCK_EXPIRY_TIME to NULL so that other users can edit this row.

Okay, I think I'm there!!!

Here's my code to get a "php lock". The query checks to see if a lock can be taken, and if it can it takes it. Subsequent user can use the same query and either obtain or be denied the lock. After 5 minutes the lock expires and other users may take the lock.

The user that gains the lock has it for AT LEAST 5 minutes as no-one can be granted a lock within this time, and the user has the lock while ever they have the lock_key as the lock key would be changed when another user took the look (after the initial 5 minutes).

One good thing is everything happens in one query statement therefore two users should not be able to apply for and gain a lock simultaneously (I think). There WHERE clause is evaluated before the SET.

Thanks again for your input Larry.


$_SESSION[‘lock_key’]=rand(1,1000000000);

$query="UPDATE diary SET lock_expiry_time = ADDTIME(NOW(),'00:05:00'),lock_key=” . $_SESSION[‘lock_key’] .” WHERE NOW() > lock_expiry_time AND entry_id=" . $_SESSION['entry_id'] . " LIMIT 1";

$result=mysql_query($query);

if($result)
die('query failed');

if(!mysql_affected_rows())
echo 'lock could not be obtained';

entry_id is my unique identifier.

Now I can update my diary via a php form and then update it as long as I have the lock_key.

As part of the row update, i.e. in the same query as updating the columns of the row, clear down the lock and key so that they are available to other users.



Edited 6 time(s). Last edit was three years ago by Yelgnidroc.

You're quite welcome for the help. Glad you've got it working and thanks for posting your solution.

Best Wishes,
Larry

Writer/Web Developer/Instructor
Forum Moderator