0

I am doing develop Indy based application. Server has several Indy TCP Server components. So It works under multi-threads and handles mysql db. I have faced one problem. That is about the exceptions of MySQL DB in threads. When serveral threads attack to same db table, then It says me like follows

UniQuery_Mgr: Duplicate field name 'id'
UniQuery_Mgr: Field 'grp_id' not found //of course grp_id field is really existed.
Assertion failure (C:\Program Files (x86)\unidac539src\Source\CRVio.pas, line 255)
Commands out of sync;  You can't run this command now
ReceiveHeader: Net packets out of order: received[0], expected[1]
UniQuery_Mgr: Cannot perform this operation on a closed dataset

How to do I ? UniQuery_Mgr is TUniQuery component. and my query handling code is normally like this

Code 1

  sql := 'SELECT * FROM data_writed;';//for example
  UniQuery_Mgr.SQL.Clear;
  UniQuery_Mgr.SQL.Add(sql);
  UniQuery_Mgr.ExecSQL;

Code 2

  try
    sql := 'SELECT * FROM gamegrp_mgr;';
    UniQuery_Mgr.SQL.Clear;
    UniQuery_Mgr.SQL.Add(sql);
    UniQuery_Mgr.ExecSQL;

    if UniQuery_Mgr.RecordCount > 0 then
    begin
      MAX_GAME_GROUP_COUNT := UniQuery_Mgr.RecordCount + 1;

      UniQuery_Mgr.First;
      i := 1;
      while not UniQuery_Mgr.Eof do
      begin
        Game_Group_ID[i] := UniQuery_Mgr.FieldByName('grp_id').AsInteger;
        Game_Game_ID[i] := UniQuery_Mgr.FieldByName('game_id').AsInteger;
        UniQuery_Mgr.Next;
        Inc(i);
      end;
    end;
  except
    on E : Exception do
    begin
      EGAMEMSG := Format('GAME group read error: <%s> @ %s',[ E.ToString, DateTimeToStr(now)]);
      Exit;
    end;
  end;

Code 3

  try
    sql := 'UPDATE data_writed SET write_gamegrp = ' + QuotedStr('0') + ';';
    UniQuery_Mgr.SQL.Clear;
    UniQuery_Mgr.SQL.Add(sql);
    UniQuery_Mgr.ExecSQL;
  except
    on E : Exception do
    begin
      EGAMEMSG := Format('data updating error: <%s> @ %s',[ E.ToString, DateTimeToStr(now)]);
      Exit;
    end;
  end;

My handling DB components is bad ? Other thread-safe method is existed???

david chan
  • 31
  • 5
  • Are you sharing the same `UniQuery_Mgr` object across multiple threads? Don't do that. Give each thread its own query component. You might have to give each thread its own DB connection, too. Consider using a DB pool. Put a few connections in a pool. When any thread needs to run a query, pull an available connection from the pool, run the query, and put the connection back in the pool. – Remy Lebeau Jan 05 '16 at 17:04
  • Thank you for your help. Of course I have shared UniQuery_Mgr in several threads. I will use its own query component in indivisual thread as you teached. But, I haven't the concept of pool....... Sorry but, could you give me simple code for pool? If then I think it will make my code perfectly. – david chan Jan 05 '16 at 18:40
  • will ask one more.... To give each thread its own DB connection and Query component, should I create DB connection and Query component dynamically? Or create DB connection statically, but create query component dynamically only? I have searched google, but there isn't good pooling example of unidac... Unidac manual also didn't give explaine in detail.... – david chan Jan 06 '16 at 10:29
  • I don't use UniDAC, but in general my projcts either 1) create a `TDataModule` with separate connection/query components for each thread, or 2) have each thread create the components dynamically as needed. Different projects do different things. – Remy Lebeau Jan 06 '16 at 17:05

0 Answers0