sqlite Deadlocks

danielkuettner · 2014-11-25 09:01:20

Hi AB,

today I had a deadlock in sqlite at this line of code:

  DB.ExecuteFmt('update zv set status=1, Datum_von="%" where rowid=%', [FormatDateTime('YYYY-MM-DD hh:mm:ss', Now), id]);

This line was called only 2 times (no such much I guess).

The service "DoEU" was registered with:

  DB:= TSQLPrcashRestServerDB.Create(Model,cSQLiteFile,false); //no user-auth
  DB.DB.LockingMode:= lmExclusive;
  DB.DB.Synchronous:= smOff;
  
  ...

  DB.ServiceRegister(THBCI,[TypeInfo(IHBCI)],sicShared).SetOptions(['DoEU','GetKontoauszug'], [optExecinPerInterfaceThread]);

I've found following article:
http://beets.radbox.org/blog/sqlite-nightmare.html

Do you know it?

in mOMRot default Timeout is 2s, right?
If one transaction must wait and HAVE_USELEEP=1, timeout is reached and the Deadlock follows.

Am I wrong?

ab · 2014-11-25 12:06:09

I'm not sure at all this is a SQLite3 "deadlock".
There is one single SQLite3 database instance with mORMot, so the "sqlite nightmare" won't simply be possible.
The timeout are available before accessing the SQLite3 layout, which is protected by a global lock.
I do not understand how a deadlock would happen.
It is not possible at SQLite3 level, since there is a global mutex when reaching the engine.

How did you set the transactions?
You should NOT use raw SQL as you wrote above.
Just an ORM Retrieve + Update would be enough, and very stable, even without an SQL backend.
For transactions, please use Batch*() methods instead of manual StartTransaction/Commit pattern.
Even the Datum_von field should be replaced by a TModTime kind of field.

Please try to isolate the problem, and provide some code to reproduce the issue.

BTW I will start implementing replication in the next days.
Your feedback would be very valuable.

danielkuettner · 2014-11-25 16:49:32

You've probably right. Not sqlite but Firebird. I guess this could be the issue: http://synopse.info/forum/viewtopic.php?id=1980&p=2.
Before the connection is cleared, the deadlock arise.

ab · 2014-11-25 19:46:10

So do we have anything to change for clearing the connection?
Did you try changing the timeout auto-re-connection value?

danielkuettner · 2014-11-25 20:39:34

No stress. It's only my guess. Let me look further, perhaps I can understand better, what's going wrong.

In my code I use the ORM for sql CRUD's (DB.ExecuteFmt). Perhaps this is a problem. I will first try to use only Props.Execute...

The error is strange. Today after this error ,I've tested with 600 transfers (money), serval times, no error. But if my server runs one week without transfers and thean I have two(!), the issue occurs.

mingda · 2014-11-26 08:02:23

I also found some problem relative TSQLDBConneciton TSQLDBConnectionProperties and the ClearConnectionPool, I first made this proposal ClearConnectionPool to fix the break connection, but now seem this fix is wrong, for Connection in ConnectionPool, every connection has different thread id, so every connection should free in their owner thread, this is particular for TOleDBMSSQLConnectionProperties, this also make use TSQLDBConnectionProperties.execute in thread should very careful, for RestServer we have EndCurrentThread call to release connection, but if directly use ConnectionProperties, then will no way to release the connection, current i have a backgroud thread to run, just after a day run, will not work, there is no memory leak, so i just change all TSQLDBConnectionProperties.execute to explicit new a Connection, then release it, today will test it, for release connection problem, this example can see this,

unit Unit6;

interface

uses
  Classes, SynDB;

type
  TTestThread = class(TThread)
  private
    { Private declarations }
    fProps: TSQLDBConnectionProperties;
  protected
    procedure Execute; override;
  public
    procedure SetSQLDBProperties(aProps: TSQLDBConnectionProperties);
  end;

implementation

{ TTestThread }

procedure TTestThread.Execute;
begin
  { Place thread code here }
  fProps.Execute('select * from test', []);
end;

procedure TTestThread.SetSQLDBProperties(aProps: TSQLDBConnectionProperties);
begin
  fProps := aProps;
end;

end.

unit Unit5;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, SynCommons, SynDB, SynOleDB, StdCtrls, Unit6;

type
  TForm5 = class(TForm)
    Button1: TButton;
    Memo1: TMemo;
    Button2: TButton;
    Button3: TButton;
    procedure FormCreate(Sender: TObject);
    procedure FormDestroy(Sender: TObject);
    procedure Button1Click(Sender: TObject);
    procedure Button2Click(Sender: TObject);
    procedure Button3Click(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
    fProps: TSQLDBConnectionProperties;
  end;

var
  Form5: TForm5;

implementation

{$R *.dfm}

procedure TForm5.Button1Click(Sender: TObject);
begin
  Memo1.Text := UTF8ToString(fProps.Execute('select * from test', []).FetchAllAsJSON(true));
end;

procedure TForm5.Button2Click(Sender: TObject);
var
  testThread: TTestThread;
begin
  testThread := TTestThread.Create(true);
  testThread.SetSQLDBProperties(fProps);
  testThread.FreeOnTerminate := true;
  testThread.Start;
end;

procedure TForm5.Button3Click(Sender: TObject);
begin
  fProps.ClearConnectionPool;
end;

procedure TForm5.FormCreate(Sender: TObject);
begin
  fProps := TOleDBMSSQLConnectionProperties.Create('localhost', 'testdb', '', '');
end;

procedure TForm5.FormDestroy(Sender: TObject);
begin
  fProps.Free;
end;

end.

after you run a thread, then close the program or call ClearConnectionPool will see EAssertionFailed, don't know if your problem is this relative, thanks!

danielkuettner · 2014-11-26 08:23:05

This was the last thread about our problem:
http://synopse.info/forum/viewtopic.php?id=1980

Now I mean TimeOut is not a solution. For request it is, but not for updates,inserts,deletes.

But I don't understand/know the process of ClearConnectionPool. I only see FreeAndNil(fMainConnection).

Is it possible that I make an Props.update in my service and in Background ClearConnectionPool is calling?

ab · 2014-11-26 08:36:44

The problematic method is the overriden TSQLDBConnectionPropertiesThreadSafe.ClearConnectionPool:

procedure TSQLDBConnectionPropertiesThreadSafe.ClearConnectionPool;
begin
  EnterCriticalSection(fConnectionCS);
  try
    inherited; // clear fMainConnection
    fConnectionPool.Clear;
    fLatestConnectionRetrievedInPool := -1;
  finally
    LeaveCriticalSection(fConnectionCS);
  end;
end;

In fact, fConnectionPool.Clear will clear all connections from the current thread, whereas some providers (like OleDB) expect the connections to be cleared from the thread which created them...
I would rather change the implementation, and set a flag so that each thread would be freed the first time it is access via function TSQLDBConnectionPropertiesThreadSafe.ThreadSafeConnection.
I do not have time for this now.
Could you please create a ticket, with a link to this forum thread, so that I would fix it when I return?
Thanks!

danielkuettner · 2014-11-26 08:49:41

@AB
Thanks, this will be better e.g. for OleDB.
I'll create this ticket.

@mingda
First we have to find out, if Timeout is necessary for OleDB.

For Firebird it seems to be necessary. But perhaps Zeos has a problem. When I look at infos for Firebird, they say deadlocks are extremly rarely, but in my case this deadlock error comes without the "new" Timeout-Option in 100%.

alpinistbg · 2014-11-26 09:40:24

danielkuettner wrote:

For Firebird it seems to be necessary. But perhaps Zeos has a problem. When I look at infos for Firebird, they say deadlocks are extremly rarely, but in my case this deadlock error comes without the "new" Timeout-Option in 100%.

@danielkuettner,
Please, see http://synopse.info/forum/viewtopic.php … 858#p13858. "Deadlocks" is not the most suitable word, "conflicts" is and IMHO they will happen quite often in Firebird as in your case.

I think the Firebird case worth further investigations, since using it improperly may cripple the otherwise fine MVCC idea with mORMot.

danielkuettner · 2014-11-26 10:02:16

@alpinistbg
Ok, I've also read your post in http://synopse.info/forum/viewtopic.php?id=1980&p=2 and understand.
Thanks.

ab · 2014-11-26 20:40:42

@all ticket has been fixed.
See my other posts.

Feedback is welcome!

mORMot Open Source

#1 2014-11-25 09:01:20

sqlite Deadlocks

#2 2014-11-25 12:06:09

Re: sqlite Deadlocks

#3 2014-11-25 16:49:32

Re: sqlite Deadlocks

#4 2014-11-25 19:46:10

Re: sqlite Deadlocks

#5 2014-11-25 20:39:34

Re: sqlite Deadlocks

#6 2014-11-26 08:02:23

Re: sqlite Deadlocks

#7 2014-11-26 08:23:05

Re: sqlite Deadlocks

#8 2014-11-26 08:36:44

Re: sqlite Deadlocks

#9 2014-11-26 08:49:41

Re: sqlite Deadlocks

#10 2014-11-26 09:40:24

Re: sqlite Deadlocks

#11 2014-11-26 10:02:16

Re: sqlite Deadlocks

#12 2014-11-26 20:40:42

Re: sqlite Deadlocks

Board footer