Locking a database row for other users

[deanes02]

I have a MySQL database accessed by a php web interface.  If one user is updating a row of the DB I want that row to be locked for all other users.  The solution I came up with was to put in an extra column called 'lock_user'.  When a user begins to edit a row the lock_user column for that row is filled with their login name (User A).  If another user (User B) then tries to access the row he/she is told that the row is locked for editing by User A.  When User A logs out the 'lock_user' column is cleared for that row and User B can then edit the row.

 

So it all works very well IF User A logs out.  However the problem is that if User A simply closes his browser window or leaves his session time out, the job is locked for all users because no command was sent to the DB to say clear the 'lock_user' column.  I have created a command for the administrator to be able to clear the lock on any particular job but he is now getting annoyed with having to this all the time.

 

Is there any way you can stop a user closing the web page with out having logged out first AND stopping their session timing out?

 

OR (preferably)

 

Is there any way you can automatically clear the contents of the 'lock_user' column when the browser window is closed or the session has timed out?

 

OR

 

Is there a better solution?

 

Any help would be greatly appreciated.  I can clarify anything as needs be.

 

THANKS!

[yzerman]

Solution 1: Set up a cron job to unlock the table every 60 seconds. If the user is still playing with the table - refreshing the page should repopulate the table with his username.

 

Solution 2: Use a java timer that counts how long the user has been idle (you know those pages - "You have been idle for 10 minutes, do you want to continue?" - and logs him out after so long and clears the database table of his userid.

 

IMO Your best bet is to use a cron job to clear the table row every 60 seconds though.

[deanes02]

thanks for your reply!  I don't think solution 2 would work because the java timer would be client based and if the user simply closed the browser window this timer would be lost too resulting in the same problem.

 

Solution 1: Set up a cron job to unlock the table every 60 seconds. If the user is still playing with the table - refreshing the page should repopulate the table with his username.

 

If i used this solution would the user not lose all of the data they were inputting on page refresh.  The data would still be in the database but if the user was filling out the lengthy form the values would be lost - wouldn't they?

 

Do you know of any way that I could code the following - if a user logs in a timer is set going on the server - if any row of the table has been locked for more than 15 minutes (or whatever) clear the lock?

 

 

[taith]

if you put a timestamp onto the rows, you can simply stop someone from updating data to it for a set amount of time... not so much a lock, as a block...

 

such as

 

if(time()-$row[timestamp]>900||$_SESSION[id]==$row[editor]){

#do queries

}

 

so only one person can do the queries, unless the timestamp has become too old...

[deanes02]

Is there any way for me to find out how long an inactive session lasts on my (hosted) server before the session expires?  If i set the timestamp to longer than that i'd be right in saying that User A would have been logged out by the server anyway before the lock was cleared - wouldn't I?

[taith]

only way around that... would be to increase the timeout... 1/2 hour or however long you need... you could try setting a cookie... but you'd run into the same problems... another option... would be to put a javascript clock, after a certain time, it gives a warning, if ok pushed, it triggers an ajax to up the row's timestamp, if not, it redirects...

[deanes02]

In phpinfo()  I get session.gc_maxlifetime 1440

 

session.gc_maxlifetime specifies the number of seconds after which data will be seen as 'garbage' and cleaned up. Garbage collection occurs during session start.

 

So i think that the system will automatically destroy sessions after 24 minutes (1440 seconds).

 

Is this right?  If so I'm totally sorted (I think!)

[yzerman]

thanks for your reply!  I don't think solution 2 would work because the java timer would be client based and if the user simply closed the browser window this timer would be lost too resulting in the same problem.

 

Solution 1: Set up a cron job to unlock the table every 60 seconds. If the user is still playing with the table - refreshing the page should repopulate the table with his username.

 

If i used this solution would the user not lose all of the data they were inputting on page refresh.  The data would still be in the database but if the user was filling out the lengthy form the values would be lost - wouldn't they?

 

Do you know of any way that I could code the following - if a user logs in a timer is set going on the server - if any row of the table has been locked for more than 15 minutes (or whatever) clear the lock?

 

 

 

Thats what I was saying to do with solution 2.

 

With your cron job, you are not interfering with the users browser, or what he has already typed in. Your only clearing his  username from the table.

 

So your cron would run a mysql query like this:

 

UPDATE table SET locked ='';

 

This way, its only clearing that username from the locked field.