Devart Forums

Hi,

I am trying to use row-level locking. We tried with TCustomMyDataSet.Lock, but won't work for us. If you have any locking related example then please send me. We are using InnoDB as a table type for the same.

regards,
Sanjivan

Go to the first record of MyTable1. Execute the following code in Application1.

MyConnection1.StartTransaction;
MyTable1.Lock(lrImmediately);

After that, if you execute the same code in Application2 then MySQL will be
waiting for committing a transaction in Application1.
If you try to edit this record in Application2 then MySQL will be waiting for
committing a transaction in Application1.

lrImmediately parameter value corresponds to SELECT ... FOR UPDATE statement
lrDelayed parameter value corresponds to SELECT ... LOCK IN SHARE MODE
statement
Please see about FOR UPDATE and LOCK IN SHARE MODE in MySQL documentation.

Stiil, i am not able to do this? Here is the code.
procedure TForm1.Button1Click(Sender: TObject);
var
custom : TCustomMyDataSet;
connection : TCustomDAConnection;
value : string;
begin
connection := TCustomDAConnection.Create(nil);
custom := TCustomMyDataSet.Create(nil);
if MyConnection1.Connected then
Begin
ShowMessage('connected');
Try
MyConnection1.StartTransaction;
MyQuery1.FetchAll := false;
custom.Lock(lrImmediately);

custom.SQL.Add('insert into xx (xx1, xx2) Values(''12'', ''testStore''');
custom.Execute;
// value := custom.FieldValues['StoreName'];
ShowMessage( 'I got value');
MyConnection1.Commit;
except
MyConnection1.Rollback;
end;
end
else
ShowMessage('NO RESULT');
end;
end.

Lock method is uncompatible with FetchAll property set to False.

By the way, doing Fetchall = true, it won't work. Do you have any example on this locking. May be this will hepful?

No, we don't have samples for MyDAC with locking.

The most primitive schema of using row-level locking:

• Create InnoDB table at the server
• Create at your project TMyTable(TMyQuery), and associate it with the table
• Create event handlers:

Fine, I am having success on locing. But i have small problem. I want to set autocommit = false;
How it can be done. As i am using myquery , i want to keep autocommit =false, on that .

Please guide me on the same.

Thanks in advance

According to MySQL ideology for this behaviour it is needed to start transaction (MyConnection.StartTransaction). Pay attention that only InnoDB-tables support transactions.

What kind of message is generated when a record/row is locked?

When accessing a locked record is there a message generated back by the server?

EMyError with ErrorCode ER_LOCK_WAIT_TIMEOUT = 1205

Can you show me a code snippet as to how I would proceed, starting the transaction, open table for edit, show error error if encountered, proceed when OK or not OK, etc

Thanks

In one-two days we are planning a new build of MyDAC with the new Lock Demo included

Thanks, Ikar,

Will be looking forward to it.