Row-level Locking

Guest · Post by **Guest** » Tue 11 Jan 2005 10:49

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

Paul · Post by **Paul** » Thu 13 Jan 2005 08:48

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.

Guest · Post by **Guest** » Fri 11 Mar 2005 00:49

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.

Ikar · Post by **Ikar** » Fri 11 Mar 2005 07:27

Lock method is uncompatible with FetchAll property set to False.

Guest · Post by **Guest** » Fri 11 Mar 2005 10:44

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

Ikar · Post by **Ikar** » Mon 14 Mar 2005 13:29

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:

Code: Select all

procedure TForm3.MyTable1BeforeEdit(DataSet: TDataSet);
begin
  MyTable1.Connection.StartTransaction;
  MyTable1.Lock(lrImmediately);
end;

procedure TForm3.MyTable1AfterPost(DataSet: TDataSet);
begin
  MyTable1.Connection.Commit;
end;

procedure TForm3.MyTable1AfterCancel(DataSet: TDataSet);
begin
  MyTable1.Connection.Rollback;
end;

Guest · Post by **Guest** » Fri 18 Mar 2005 18:52

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

Ikar · Post by **Ikar** » Mon 21 Mar 2005 09:25

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

Guest · Post by **Guest** » Mon 28 Mar 2005 04:46

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

danzman · Post by **danzman** » Mon 28 Mar 2005 04:49

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

Ikar · Post by **Ikar** » Mon 28 Mar 2005 08:53

EMyError with ErrorCode ER_LOCK_WAIT_TIMEOUT = 1205

danzman · Post by **danzman** » Mon 28 Mar 2005 12:23

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

Ikar · Post by **Ikar** » Mon 28 Mar 2005 13:11

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

danzman · Post by **danzman** » Mon 28 Mar 2005 21:58

Thanks, Ikar,

Will be looking forward to it.

Devart Forums

Row-level Locking

Row-level Locking

Row level lockign