LOCK and FREE records in database

[adrian_quah]

hey guys, hope u can help with this.

i've a few tables in which records can be locked while a user is editing it. So, for example the table 'Customers' has this field called 'Record_Status' whereby 0 indicates LOCK, while 1 indicates FREE. LOCK means the record can be viewed and not edited at that time. Once the user is done editing the record, it again returns to FREE. my concern is that once set to LOCK, some users may use the record or leave their browser idle...thus it remains as LOCK..

How can i implement a system where after say, 10 minutes, a record goes to FREE again?

[planetsim]

well have a feild in that table called time
when a person posts... you get the time use time();

then from that in your code somewhere have a mysql update and update the code like this
$timeout=time()-600;
mysql_query("UPDATE table_name SET status='1' WHERE time < $timeout");

[adrian_quah]

thanks planetsim, its a good idea..but can there be another way where i dont need to add the new time field? cos the database structure is quite fixed at the moment 😉

gracias,
Adrian

[ahundiak]

Maybe you can store the time directly in the lock field? Or maybe you can create a new lock table just for storing the lock times.

You can also rethink the need for locking in the first place. One thing I have done is to store the original values of a record in a hidden field in the edit form. Then, when the user actually presses the update button, I first check to see if the record has been changed since it was checked out for editing. If it was changed then the update fails.

[adrian_quah]

thanks guys, i'd consider it. would certainly implement it if my db structure hasnt been fixed. meanwhile, if u've got more tips, pointers or arrows (hehe!) tell me ok!

😃

[adrian_quah]

Just wondering...does MS SQL have a function or feature that prevents or handles concurrent changes being allowed / made to the db tables..for the same record?

If it does, it means much less hassle. Thanks 🙂