Problem with row locking - transactions - select for update
lazarus / 16-Databases

----------------------------------------------------------------------
piper62 · 2007-03-28 21:58

hi,
at first I would like to congratulate the complete FreePascal/Lazarus
team for the great work done!!!  
I used Delphi for a long time but in the last years I skipped it
because I use in 99,9% of my work Linux.

I have a problem with safe row locking for databases. I use Debian
Linux (Etch), MySQL 5.0.33, InnoDB tables and FPC 2.0.4/Lazarus
0.9.20.

With SQLDB the "normal" things are OK: selects, updates, inserts but I
can't get updates under the protection of row locking.
In the MySQLQuery Browser this works:
User1:
START TRANSACTION;
SELECT * FROM CARE_PERSON WHERE pid=1003 FOR UPDATE;

If user2 does now:
START TRANSACTION;
SELECT * FROM CARE_PERSON WHERE pid=1003 FOR UPDATE;
he gets an timout based on the lock_wait parameter.

I try to get this behaviour in my Lazarus program:
----
     sqlquery1.SQL.Clear;
     sqlquery1.ParseSQL:=False;
     sqlquery1.SQL.Add('set autocommit=0;');
     sqlquery1.ExecSQL;
     sqlquery1.SQL.Clear;
     sqlquery1.sql.Add('START TRANSACTION;');
     sqlquery1.ExecSQL;
     sqlquery1.SQL.Clear;
     v_sql:='select * from care_person where pid='+inttostr(v_patid)+'
for update;';
     sqlquery1.sql.Add(v_sql);
//     edit1.text:=v_sql; { controlling output }
     sqlquery1.Open;
----

I also tried:   "db_interface.transact1.StartTransaction;"  instead of
sending it over sqlquery1 but no success.

If I start the application now twice and simulate in this way to
different tasks the second user gets no problem with a locked record.

What do I have to do to get the record locked in the way I can get it
with direct interaction in MySQL?

Thank you for your help in advance.

Regards,
Tibor