Mysql Mutliuser enviroment.

[karlrosch]

Can any one help me solve this small problem I have.

Senario.

I have set up a test table with one record in which i increment the value of one field.

I edit the database , increment the field and update it. I am using cached updates and transactions.

It all works fine until i have two user doing the same thing.

I have implemented locking.

I get an error 'Update failed 0 Records found' when the second user tries to update the table.


Here is my sample code - 'tb' is my table - 'mysql' is my mysqlconnection

procedure TForm1.Button1Click(Sender: TObject);
var
lck:integer;
begin

lck:=0;
tb.active:=true;
tb.refresh;
mysql.StartTransaction;

try
tb.LockTable (ltwrite)
except
showmessage ('locked');
mysql.rollback;
lck:=1;

end;
if lck=0 then begin
tb.refresh;
tb.edit;
tb.Fields[0].AsInteger :=tb.Fields[0].AsInteger +1;
tb.post;

tb.ApplyUpdates;
tb.CommitUpdates ;
mysql.Commit;
label1.caption:=tb.Fields[0].asstring;
label1.Refresh;
tb.UnLockTable;

end;
lck:=0

end;

Thanks

[kenny]

Do you have set the primary for this table? This error will occur if your table does not have unique key...

[Ikar]

If you need to be sure that the only user updates a table at the single moment you should add unique autoincrement field to your table.

PS: Most probably, CachedUpdates not needed in this case.