Help with locking a mysql record

[Riparian]

This is probably a dumb question but I can not find any reference to an answer.

 

Can someone tell me or point me in the right direction ?

 

I have a back end management section that allows more than one person to access a clients records.

 

How can I stop 2 people editing the same record at the same time as obviously the last person to update will overwrite the first.

 

Any help is greatly appreciated

 

PS I have posted this in the ms msql help by mistake- please disregard that one

[fenway]

There are two issues here... the database transaction, and the application level of allowing editing of the same "record".

[Riparian]

Hmm ... that was not much help.

 

I have spent the day learning how to Lock the tables and written these functions that appear to do absolutely zip.

 

Can you tell me if I am on the right track ?

 

function LockWrite($TABLE_STRING){

$TABLES= explode(',',$TABLE_STRING);

foreach($TABLES as $t_value){

  $qry.="$t_value WRITE, " ;

}

$last_comm=strlen($qry);

$Query="LOCK TABLES ".substr($qry,0,$last_comm-2);

mysql_query($Query)or die('170 config'.mysql_error());

}

function UnLockTables(){

$Query = "UNLOCK TABLES";

mysql_query($query);

}

 

Cheers

 

Brian

[luca200]

Locking tables is not a solution, as you can't extend locks between different http calls.

You can use a timestamp that shows you the last time your record was updated.

 

So, when your user asks for editing the record, you read the timestamp and save it in session.

Later, when you have to update the record, you have 2 options:

1) the simplest one: you build your UPDATE sql statement adding "WHERE timestamp = 'xxxxx'"; this way, if someone updated the record before, your statement simply won't do nothing. The problem is that you are unaware of this unless you make some investigation.

2) the other way is to lock the table, make a read to check that the timestamp is not changed, and, if true, make your update; if false, warn the user about someone updating before him.

 

Needless to say your update statement must update the timestamp 

[aschk]

MySQL tables (MyISAM) now do row level locking as part of their basic procedure.

Thus if someone tries to edit a row while another person is editing it, it waits.

However, I don't honestly see this as being a problem.

 

If person 1 changes the information of someone, then person 2 changes that same information afterwards (having not seen the change that person 1 did), then surely they are still inputting the right information anyway?

[fenway]

MySQL tables (MyISAM) now do row level locking as part of their basic procedure.

Since when?

[aschk]

Ooh my bad, i meant InnoDB... 

[fenway]

If person 1 changes the information of someone, then person 2 changes that same information afterwards (having not seen the change that person 1 did), then surely they are still inputting the right information anyway?

Not necessarily.

 

The lazy way is simply to have a modified datetime field for ech record, retrieve it on page load, and then compare it before you write the data back.

[Riparian]

Hi Guys and thank you very much for the discussion.

 

Just for aschk I think you may have missed the problem.

 

If 2 ppl have the same file open at the same time and person 1 makes a change e.g. adds a line to a text field then saves it, person 2 "still has the original data" in their browser. When person 2 saves his edits  then the original data is stored with the changes made by person 2. The edits of person 1 is lost and noone knows about it.

 

I am afraid I can't see the advantage of the timestamp method because even if person 2 is notified that the file has been  modified by person 1  there is nothing person 2 can do to save his edits without having to load the file again and start over entering his edits again. ??

 

I thought about saving the open record numbers in a table to stop them being opened twice. Then having the calling program release them on save but this fails if the calling program terminates in an unforseen manner and does not go through a remove function.

 

Well thats the way I see it but I am by no means an expert and any advise is greatly appreciated.

 

Cheers

 

Brian

 

 

[fenway]

I am afraid I can't see the advantage of the timestamp method because even if person 2 is notified that the file has been  modified by person 1  there is nothing person 2 can do to save his edits without having to load the file again and start over entering his edits again. ??

You can also mark the record as being edited... but that's annoying for the user.

 

You have to decide what's more important... the user or the dta.