Two threads running the same function

Question

I have a situation where in Delphi I have two threads that perform a function that ping in a Firebird database.

I use this to sync data from my PDV (sending and receiving).

But as they run at the same time sometimes occurs a lock Dead in BD.

--

My job:

function TTPingRede(const IP: AnsiString; TimeOut: Integer = 250) : Boolean;
var
       DW: DWORD;
   Handle: THandle;
   InAddr: IPAddr;
      Arq: TextFile;
      Rep: array[1..128] of byte;`
begin
    try
       DW     := 99;
       Result := False;
       Handle := IcmpCreateFile;
if (Handle <> INVALID_HANDLE_VALUE) then
       begin
try
              TranslateStringToTInAddr(PansiChar(IP), InAddr);
              DW := IcmpSendEcho(Handle, InAddr, nil, 0, nil, @rep, 128, TimeOut);
              Result := (DW <> 0);
              vStatusREDE_ATIVA := Result;
           except
               DW     := 0;
               Result := False;
           end;
try
              Dm.qStatusRede.Close;
              Dm.qStatusRede.SQL.Clear;
              Dm.qStatusRede.SQL.Add('UPDATE TBSINCRO001');
              Dm.qStatusRede.SQL.Add('SET');
              Dm.qStatusRede.SQL.Add('   STATUS_REDE_SERVIDOR = :P0');
              Dm.qStatusRede.Params[0].AsInteger := DW;
              Dm.qStatusRede.Prepare;
              Dm.qStatusRede.ExecSQL;
if Dm.Tr_StatusRede.InTransaction then Dm.Tr_StatusRede.Commit;
           except
               on E: Exception do
               begin
                   Dm.Tr_StatusRede.Rollback;
                   GerarLogErroSincronizar('1:FUNC ENV|'+E.Message);
               end;
           end;
       end;
    finally
IcmpCloseHandle(Handle);
    end;
end;

I would like to know an implementation that I can drip into the bank without the Dead lock.

Answer 1

Competition problems usually have a common cause: shared objects

In this case, it seems that the shared object Dm is used by both threads. That means both of you threads are sending and receiving data on the same connection. Think of it as two users typing SQL commands on the same keyboard, one interferes on the other even without commit.

Somehow, you need to ensure that every thread run on an independent connection. The simplest way to do this is to create Dm1 and Dm2 and assign each to a thread. A more elaborate way would be to create a connection within the scope of thread. Think of it as two users accessing the same database from different terminals, so with different sessions and one doesn’t interfere with the other until you commit the transaction.

Answer 2

The TCP/IP stack organizes sessions between servers and clients through the process PID at each end of the connection. As two threads are part of the same process, I believe they are sharing a session with the DBMS server, which causes Deadlock. Try creating two different processes, one for each request to the server.