Why is my firebird database code unstable under load? (code included)

[Yaron 53]

Using Delphi 10.3.3 I built a simple web server using MARS Curiosity (https://en.delphipraxis.net/forum/34-mars-curiosity-rest-library/) and FireBird DB v3.0.4.

 

However, sometimes a simple refresh in the web browser (Ctrl+R) will trigger multiple different errors:

Project GameServicesServerApplication.exe raised exception class EIdSocketError with message 'Socket Error # 10053 Software caused connection abort.

Project GameServicesServerApplication.exe raised exception class $C0000005 with message 'access violation at 0x00730647: read of address 0x00000000'.
Project GameServicesServerApplication.exe raised exception class EDatabaseError with message 'Field 'CONSUMERNAME' not found'.

Project GameServicesServerApplication.exe raised exception class EArgumentOutOfRangeException with message 'Argument out of range'.
 

Holding Ctrl+R in the web browser easily reproduces the issue within a few seconds (the more DB calls I preform in the MARS web server function, the easier it is to trigger the crash).

The fault is not in MARS, I tested by disabling all DB calls and verifying the code doesn't crash (I even threw a Sleep(1000) in there to see if it may be related to the duration it takes the function to process).

 

Am I doing something wrong?

 

I use a connection pool to support multiple threads connecting at once, here is how I connect to the DB:

  dbParams := TStringList.Create;
  try
    dbParams.Add('Server=localhost');
    dbParams.Add('Database=c:\DB\Database.FDB');
    dbParams.Add('User_Name=SYSDBA');
    dbParams.Add('Password=SomePassword');
    dbParams.Add('CharacterSet=UTF8');
    dbParams.Add('Pooled=True');
    FDManager.AddConnectionDef(dbPoolName,'FB',dbParams);
    Try
      FDManager.Open;
    Except
      on E : Exception do {$IFDEF TRACEDEBUG}AddDebugEntry(debugFileSystem,'Exception connecting to DB : '+E.Message){$ENDIF};
    End;
  finally
    dbParams.Free;
  end;

 

Here's the MARS server definition:

Type
  [Path('test')]
  TGalleryServicesResource = class
  protected
  public
    [GET , Path('/gallery'), Produces(TMediaType.TEXT_HTML)]
    function Gallery_From_External_Source([QueryParam] code : String) : String;
  end;

 

Here is the function MARS triggers:

function TGalleryServicesResource.Gallery_From_External_Source(code : String) : String;
begin
  If GetGalleryCodeDetails(sDebugFile,code,nGalleryCode) = True then
  Begin
  End;
end;

 

And here is the database access:

function  GetGalleryCodeDetails(sDebugFile,sGalleryCode : String; var nGalleryCode : TGalleryCodeRecord) : Boolean;
var
  dbQuery : TFDQuery;
begin
  Result := False;
  dbQuery := TFDQuery.Create(nil);
  Try
    dbQuery.ConnectionName := dbPoolName;
    dbQuery.SQL.Text := 'SELECT * FROM GALLERY_CODES WHERE CODE_UID=:gallerycodeuid;';

    Try
      dbQuery.Prepare;
      dbQuery.ParamByName('gallerycodeuid').AsString := sGalleryCode;
      dbQuery.Open;
      If dbQuery.RecordCount > 0 then
      Begin
        Result := True;
        With nGalleryCode do
        Begin
          gcUserName              := dbQuery.FieldByName('CONSUMERNAME').AsString;
          gcUserEMail             := dbQuery.FieldByName('CONSUMEREMAIL').AsString;
          gcCodeUID               := dbQuery.FieldByName('CODE_UID').AsString;
          gcImageURL              := dbQuery.FieldByName('IMAGEURL').AsString;
          gcCreateTimeStamp       := dbQuery.FieldByName('CREATE_TIMESTAMP').AsFloat;
          gcOpenTimeStamp         := dbQuery.FieldByName('OPEN_TIMESTAMP').AsFloat;
          gcUseTimeStamp          := dbQuery.FieldByName('USE_TIMESTAMP').AsFloat;
          gcStatus                := dbQuery.FieldByName('STATUS').AsInteger;
        End;
      End;
    Except
      on E : Exception do {$IFDEF TRACEDEBUG}AddDebugEntry(sDebugFile,'Exception : '+E.Message){$ENDIF};
    End;
  Finally
    dbQuery.Free;
  End;
end;

 

Edited December 17, 2019 by Yaron

[Yaron 53]

I even created a simple load-testing app (https://github.com/bLightZP/Web-Server-Load-Tester) that opens the URL in multiple threads simultaneously (triggers the crashes instantly).

 

With 10 thread it crashes instantly if there's DB access, if I disable the DB access code, even 1000 threads work just fine.

And even with just 2 threads it can occasionally crash.

Edited December 17, 2019 by Yaron

[rvk 33]

I have no experience with FireDac, but shouldn't you create a TFDConnection in each thread to be used with TFDQuery?

As is shown in http://docwiki.embarcadero.com/RADStudio/Rio/en/Multithreading_(FireDAC)

 

[Yaron 53]

7 minutes ago, rvk said:

I have no experience with FireDac, but shouldn't you create a TFDConnection in each thread to be used with TFDQuery?

As is shown in http://docwiki.embarcadero.com/RADStudio/Rio/en/Multithreading_(FireDAC)

 

You may be right, I was basing my code on a comment by Jacek here

It's quite possible he was wrong, initial testing looks to confirm this.

[Yaron 53]

After rewriting every DB entry point using "TFDConnections", I managed to resolve most of the errors, now I'm only receiving these 2:

Project GameServicesServerApplication.exe raised exception class EIdSocketError with message 'Socket Error # 10053 Software caused connection abort.

Project GameServicesServerApplication.exe raised exception class EIBNativeException with message '[FireDAC][Phys][FB]deadlock

 

I'm assuming the first error is due to constant reloads in browser (it only happens in a browser, doesn't happen for me when load-testing using threads).

However, the "deadlock" exception is an issue, any ideas why it's triggered and how to avoid it?

[rvk 33]

Maybe putting a amNonBlocking in there?

FDQuery1.ResourceOptions.CmdExecMode := amNonBlocking;

 

You're not updating anything so I'm not sure why there would be a deadlock there.

You set the TFDConnection.Connected to false afterwards in the thread? (because that releases the connection back to the pool)

 

[Fr0sT.Brutal 900]

13 hours ago, rvk said:

Maybe putting a amNonBlocking in there?

FDQuery1.ResourceOptions.CmdExecMode := amNonBlocking;

 

You're not updating anything so I'm not sure why there would be a deadlock there.

You set the TFDConnection.Connected to false afterwards in the thread? (because that releases the connection back to the pool) 

 

NonBlocking AFAIK uses more threads under-the-hood so it just would add more hassle.

And disconnecting a connection is senseless, it's not much different from creating/destroying a connection object every time. The idea of connection pool is to have several alive connections to DB and use them in threads. A connection object shouldn't be used by more than one thread at the same time but when a thread finishes its query, it returns a connection to pool.

[rvk 33]

18 minutes ago, Fr0sT.Brutal said:

And disconnecting a connection is senseless, it's not much different from creating/destroying a connection object every time.

It's not if you're using a pool. When using a pool the connection is given back to the pool. It's not closed. At least that's what the documentation says.

 

Setting TFDConnection.Connected to True acquires a physical connection from the pool.

Setting TFDConnection.Connected to False releases the physical connection to the pool, but keeps the connection opened.

http://docwiki.embarcadero.com/RADStudio/Rio/en/Multithreading_(FireDAC)

 

But maybe the TFDConnection.Free already does that.

 

What's your code like now?

 

[Fr0sT.Brutal 900]

3 hours ago, rvk said:

It's not if you're using a pool. When using a pool the connection is given back to the pool. It's not closed. At least that's what the documentation says.

Hmm, very non-logical then. A really bad behavior encapsulation, IMHO.

[Yaron 53]

My code now creates and frees both TFDConnection and TFDQuery:

procedure DB_SetUserFlags(sDebugFile,sUserUID : String; iFlags : Integer);
var
  dbConn  : TFDConnection;
  dbQuery : TFDQuery;
begin
  dbConn := TFDConnection.Create(nil);
  dbConn.ConnectionDefName := dbPoolName;
  dbQuery := TFDQuery.Create(nil);
  dbQuery.Connection := dbConn;
  Try
    dbQuery.SQL.Text   := 'UPDATE USERS SET USER_FLAGS=:userflags WHERE USER_UID=:useruid;';

    Try
      dbQuery.Prepare;
      dbQuery.ParamByName('userflags').AsInteger := iFlags;
      dbQuery.ParamByName('useruid').AsString    := sUserUID;
      dbQuery.ExecSQL;
    Except
      on E : Exception do {$IFDEF TRACEDEBUG}AddDebugEntry(sDebugFile,'Exception : '+E.Message){$ENDIF};
    End;
  Finally
    dbQuery.Free;
    dbConn.Free;
  End;
end;

 

[rvk 33]

For good measure I would add Connected := false after dbQuery.ExecSQL;

(But my guess this is also done in dbConn.Free)

dbQuery.ExecSQL;
dbConn.Connected := false;

But I see you update the table in the thread. You didn't do that in your original post.

The deadlock can come from using transactions incorrectly (or updating the same record in different transactions/threads),

 

http://www.firebirdfaq.org/faq151/

 

[Yaron 53]

8 minutes ago, rvk said:

But I see you update the table in the thread. You didn't do that in your original post.

 

The deadlock can come from using transactions incorrectly (or updating the same record in different transactions/threads),

http://www.firebirdfaq.org/faq151/

Thanks, I read the FAQ entry.

I'm new to working with databases, what is the best approach to deal with deadlocks?

 

The actual DB call that triggered the deadlock was a view counter, two users viewing the same web page.

What is the best approach to handle such deadlocks?

 

[rvk 33]

2 minutes ago, Yaron said:

The actual DB call that triggered the deadlock was a view counter, two users viewing the same web page.

What is the best approach to handle such deadlocks?

By setting the correct isolation level, record version and wait-option for the transaction.

 

https://ib-aid.com/en/how-to-track-deadlocks-in-firebird/

 

As I said I'm not familiar with FireDac so I'm not sure how to set it.

 

https://stackoverflow.com/a/56337589/1037511

 

(Something like read_committed, rec_version and nowait should fix deadlocks on select)

 

[Dmitry Sh. 8]

You must control transaction under Firebird

...
ReadTransaction.Connection := FDConnection1;
ReadTransaction.Options.ReadOnly := True;
ReadTransaction.Options.Isolation := xiReadCommitted;
...
SelectQuery.Transaction := ReadTransaction;

http://docwiki.embarcadero.com/RADStudio/Rio/en/Managing_Transactions_(FireDAC)

[Lars Fosdal 1792]

 

 

 

 

20 hours ago, Dmitry Sh. said:

You must control transaction under Firebird

...
ReadTransaction.Connection := FDConnection1;
ReadTransaction.Options.ReadOnly := True;
ReadTransaction.Options.Isolation := xiReadCommitted;
...
SelectQuery.Transaction := ReadTransaction;

http://docwiki.embarcadero.com/RADStudio/Rio/en/Managing_Transactions_(FireDAC)

Is it correct that the custom transactions only apply to Interbase and Firebird?

I.e. for f.x. SQL Server, transaction handling would have to be in the query itself?

[Lars Fosdal 1792]

Note to @Daniel - See the above comment. The quote gets wonky after an edit, and once it is wonky, it can't be edited again.

[rvk 33]

1 minute ago, Lars Fosdal said:

Is it correct that the custom transactions only apply to Interbase and Firebird?

As long as you use the standard properties and values to set the isolation levels etc., they will work for all databases.

Most databases have transactions and you should be able set them the same way.

i.e. MSSQL and Oracle etc. all have Read Committed transactions levels. So transactions work the same way.

 

Only when setting special properties via the params, you'll get database specific.

 

[Lars Fosdal 1792]

@rvk - My question was due to the comment in the docwiki link that explicitly mentions Firebird and Interbase.

Likewise, in http://docwiki.embarcadero.com/Libraries/Rio/en/FireDAC.Comp.Client.TFDCustomConnection.Transaction - it says "Note: At the moment, this behavior applies only for InterBase and Firebird connections."

[rvk 33]

2 minutes ago, Lars Fosdal said:

@rvk - My question was due to the comment in the docwiki link that explicitly mentions Firebird and Interbase.

Likewise, in http://docwiki.embarcadero.com/Libraries/Rio/en/FireDAC.Comp.Client.TFDCustomConnection.Transaction - it says "Note: At the moment, this behavior applies only for InterBase and Firebird connections."

Ah, yes. But Dmitry gave an example of SelectQuery.Transaction := ReadTransaction;

So I was talking about setting the actual transaction properties rather than talking about the fact the transaction-property itself is used or not.

 

It indeed seems to be that some of the explicit transaction assignments only work for Interbase/Firebird (???).

Also for the transaction of a TDataset: http://docwiki.embarcadero.com/Libraries/Rio/en/FireDAC.Comp.Client.TFDRdbmsDataSet.Transaction

Quote

Currently, explicit transaction object assignment is supported only for InterBase and Firebird.

 

How would one set transactions with specific isolation level for MSSQL and Oracle then ???

Doesn't FireDac support these?

 

This page does mention setting isolation level but also mentions it is for Firebird/Interbase.

http://docwiki.embarcadero.com/RADStudio/Rio/en/Managing_Transactions_(FireDAC)

 

(FireDac might be even more rubisch than I initially thought if you can't set isolation level etc for other databases via the standard property)

 

[Guest]

Note to @Lars Fosdal, in the meantime, you can refresh the page. Your edits remain and the bug goes away.

[Lars Fosdal 1792]

2 minutes ago, Dany Marmur said:

Note to @Lars Fosdal, in the meantime, you can refresh the page. Your edits remain and the bug goes away.

Nope.  F5 or Ctrl-R and the visual error remains, and I still can't edit the post again.

[Guest]

If your pooling is correct (and i cannot discern that since i do not use FireDAC) then a deadlock could only happen if two update requests for the same user are sent to the database.

This can happen if multithreaded. From the point of the ExecuteSQL statement and until that query is committed. You do not do an explicit commit so i guess that it happens at dbQuery.Free.

 

HTH

[Daniel 417]

Thanks, I will look into it.

[Guest]

1 minute ago, Lars Fosdal said:

Nope.  F5 or Ctrl-R and the visual error remains, and I still can't edit the post again.

Bummer. I'm sure i had a workaround for that. I'll check next time it happens.

[Yaron 53]

I'm new to databases, is there any Delphi Firedac sample code that shows best practices when handles deadlocks?

 

My deadlock is a simple integer page-view counter, here's the actual code:

procedure SetCampaignViewCount(sDebugFile,sGalleryUID : String; nCount : Integer);
var
  dbConn  : TFDConnection;
  dbQuery : TFDQuery;
begin
  dbConn  := TFDConnection.Create(nil);  dbConn.ConnectionDefName := dbPoolName;
  dbQuery := TFDQuery.Create(nil);       dbQuery.Connection       := dbConn;
  Try
    dbQuery.SQL.Text   := 'UPDATE GALLERY_TABLE SET VIEW_COUNT=:viewcount WHERE GALLERY_UID=:galleryuid;';
    Try
      dbQuery.Prepare;
      dbQuery.ParamByName('galleryuid').AsString := sGalleryUID;
      dbQuery.ParamByName('viewcount').AsInteger := nCount;
      dbQuery.ExecSQL;
    Except
      on E : Exception do
      Begin
        {$IFDEF TRACEDEBUG}AddDebugEntry(sDebugFile,'SetCampaignViewCount Exception : '+E.Message){$ENDIF};
      End;
    End;
  Finally
    dbQuery.Free; dbConn.Free;
  End;
end;

 

Edited December 19, 2019 by Yaron