Using a table as a mutex

[teunis]

MyDac 5.10.0.9 Delphi 6
Different sessions should be able to get a unique number from a mutex
TableMutex with some filelds one field is called nr

Code: Select all

MyQ.SQL.Clear;
MyQ.SQL.Add('LOCK TABLES TableMutex WRITE');
MyQ.ExeCute;
MyQ.SQL.Clear;
MyQ.SQL.Add('SELECT key,nr FROM  TableMutex ORDER BY key DESC');
MyQ.Active:= TRUE; MyQ.First;
i:= MyQ.FieldByName('key').asInteger;
nummer:= MyQ.fieldByName('nr').asInteger; Inc(nummer);
MyQ.SQL.Clear;
MyQ.SQL.Add('UPDATE TableMutex SET nr = ' + IntToStr(nummer));
MyQ.SQL.Add('WHERE key = ' + IntToStr(nummer));
MyQ.ExeCute;
MyQ.SQL.Clear;
MyQ.SQL.Add('UNLOCK TABLES');
MyQ.ExeCute

There are about 4 sessions going on.
I still get about 30 double numbers on 600 transactions
Do I do something wrong or forget a special thing?
Please, let me know. My clients are starting to punish me

[Dimon]

To solve the problem you should lock the table for reading, like this:

Code: Select all

MyQ.SQL.Add('LOCK TABLES TableMutex READ'); 

[teunis]

Thanks,
I thought that a WRITE LOCK had an implicit READ LOCK
I will give it a try today with your comment (command)
and will keep you informed about the success.
Teunis
P.S: I guess the function LockTable(ltRead) does the same thing?

[teunis]

I gave it a try with "LOCK TABLES READ"
If I do that my session is also not allowed to alter the nr.
So I made a modification:

Code: Select all

MyQ.SQL.Clear;
MyQ.SQL.Add('LOCK TABLES TableMutex READ');
MyQ.ExeCute;
MyQ.SQL.Clear;
MyQ.SQL.Add('SELECT key,nr FROM  TableMutex ORDER BY key DESC');
MyQ.Active:= TRUE; MyQ.First;
i:= MyQ.FieldByName('key').asInteger;
nummer:= MyQ.fieldByName('nr').asInteger; Inc(nummer);
// The WRITE LOCK overrules the READ LOCK
MyQ.SQL.Clear;
MyQ.SQL.Add('LOCK TABLES TableMutex WRITE;');
MyQ.SQL.Add('UPDATE TableMutex SET nr = ' + IntToStr(nummer));
MyQ.SQL.Add('WHERE key = ' + IntToStr(nummer));
MyQ.ExeCute;
MyQ.SQL.Clear;
MyQ.SQL.Add('UNLOCK TABLES');
MyQ.ExeCute 

Unfortunately the same problem in the same quantity
I would like to have the exclusive access to the table read it write to it and when I am finished other sessions can do the same and in this way get a unique nummer!
Any more suggestions are apreciated. Thanks Teunis

[Dimon]

To solve the problem, use the following code:

Code: Select all

  MyQ.SQL.Clear;
  MyQ.SQL.Add('LOCK TABLES TableMutex WRITE');
  MyQ.ExeCute;
  MyQ.SQL.Clear;
  MyQ.SQL.Add('SELECT key,nr FROM TableMutex ORDER BY key DESC');
  MyQ.Active:= TRUE;
  MyQ.First;
  MyQ.Edit;
  MyQ.FieldByName('nr').AsInteger := MyQ.FieldByName('nr').AsInteger + 1;
  MyQ.Post;
  MyQ.UnLockTable;