ZEOS + Firebird + Update-Problem

danielkuettner · 2014-09-01 13:07:39

After a while without problems, an update of a field results in Exception:

20140901 10023751  ; DB    			Connected to firebird-2.5://192.168.1.249:3050?LibLocation=fbclient.dll using G:\firebird\prcash.dk 0
20140901 10023751  ;  -    		00.011.367
20140901 10023751  ;  -    	00.011.448
20140901 10023751  ;  +    	TSQLDBZEOSStatement(00373310).004F14A4 SynDBZEOS.TSQLDBZEOSStatement.ExecutePrepared (814) 
20140901 10023751  ; SQL   		TSQLDBZEOSStatement(00373310) update Kontoauszug set KONTOAUSZUG_KATEGORIE='14000' where KONTOAUSZUG_ID=30607
20140901 10023751  ;  -    	00.006.943
20140901 10023751  ; srvr  	TSQLPrcashRestDbServer(05FCBCC0) PUT Kontoauszug -> 200
20140901 10023751  ;  -    00.019.954
20140901 10024639  ?  +    TSQLPrcashRestDbServer(05FCBCC0).0028C99B mORMot.TSQLRestServer.URI (27615) 
20140901 10024639  ?  +    	TSQLDBZEOSStatement(00373470).004F067B SynDBZEOS.TSQLDBZEOSStatement.Prepare (682) 
20140901 10024639  ?  +    		TSQLDBZEOSConnection(05F0EE90).Connect to firebird-2.5 G:\firebird\prcash.dk for 192.168.1.249 at 3050:
20140901 10024640  ? DB    			Connected to firebird-2.5://192.168.1.249:3050?LibLocation=fbclient.dll using G:\firebird\prcash.dk 0
20140901 10024640  ?  -    		00.011.359
20140901 10024640  ?  -    	00.011.441
20140901 10024640  ?  +    	TSQLDBZEOSStatement(00373470).004F14A4 SynDBZEOS.TSQLDBZEOSStatement.ExecutePrepared (814) 
20140901 10024640  ? SQL   		TSQLDBZEOSStatement(00373470) update Kontoauszug set KONTOAUSZUG_KATEGORIE='1400' where KONTOAUSZUG_ID=30607
20140901 10024641  ?  -    	00.007.075
20140901 10024641  ? srvr  	TSQLPrcashRestDbServer(05FCBCC0) PUT Kontoauszug -> 200
20140901 10024641  ?  -    00.020.102
20140901 11002013  A  +    TSQLPrcashRestDbServer(05FCBCC0).0028C99B mORMot.TSQLRestServer.URI (27615) 
20140901 11002013  A  +    	TSQLDBZEOSStatement(003735D0).004F067B SynDBZEOS.TSQLDBZEOSStatement.Prepare (682) 
20140901 11002013  A  -    	00.000.839
20140901 11002013  A  +    	TSQLDBZEOSStatement(003735D0).004F14A4 SynDBZEOS.TSQLDBZEOSStatement.ExecutePrepared (814) 
20140901 11002013  A SQL   		TSQLDBZEOSStatement(003735D0) update Kontoauszug set KONTOAUSZUG_KATEGORIE='140000' where KONTOAUSZUG_ID=30607
20140901 11002014  A EXC   		EZSQLException ("SQL Error:  deadlock update conflicts with concurrent update concurrent transaction number is 123132. Error Code: -913. deadlock The SQL: update Kontoauszug set KONTOAUSZUG_KATEGORIE=? where KONTOAUSZUG_ID=?; ") at 00477518 ZDbcInterbase6Utils.CheckInterbase6Error (809)  stack trace API 0022FD10 SynCommons.SynRtlUnwind (41376) 000094DC System.@HandleAnyException 00477518 ZDbcInterbase6Utils.CheckInterbase6Error (809) 004849B2 ZDbcInterbase6Statement.TZInterbase6PreparedStatement.ExecuteInternal (154) 00485572 ZDbcInterbase6Statement.TZInterbase6PreparedStatement.ExecutePrepared (334) 004F192B SynDBZEOS.TSQLDBZEOSStatement.ExecutePrepared (891) 004F8148 mORMotDB.TSQLRestStorageExternal.ExecuteFromJSON (1674) 004F5E24 mORMotDB.TSQLRestStorageExternal.EngineUpdate (1112) 0028A8C7 mORMot.TSQLRestServerURIContext.ExecuteORMWrite (27029) 00288CC3 mORMot.TSQLRestServerURIContext.Execute (26614) 0028CAB5 mORMot.TSQLRestServer.URI (27644) 00337D5C mORMotHttpServer.TSQLHttpServer.Request (605) 0032F93D SynCrtSock.THttpServerGeneric.Request (2014) 00335755 SynCrtSock.THttpApiServer.Execute (5073) 000CB498 System.Classes.ThreadProc 00009F82 System.ThreadWrapper 
20140901 11002014  A  -    	00.013.888
20140901 11002014  A srvr  	PUT root/Kontoauszug/30607 ERROR=400 (Bad Request)
20140901 11002014  A  -    00.015.152

The error occurs in 32bit and 64bit, with Forms and with Service. On client side I used ajax-PUT requests.

The error is permanent and never goes away, if Exception was fired. There are no other client connections to the DB, only the mORMot-Server with one client.

After restarting mORMot server all is good again.

Last edited by danielkuettner (2014-09-01 13:12:41)

ab · 2014-09-01 18:04:35

I do not know if it is something at mORMot level, or something at ZDBC level...

How do you create your transactions?
-> BATCH mode and setting AutomaticTransactionPerRow parameter to BatchStart() is preferred.

What is your thread safety settings on mORMot side?
-> see "11.5. Thread-safety" paragraph in the SAD 1.18 pdf. You may try to change the execORMWrite to amBackgroundThread.

"11.5. Thread-safety" paragraph in the SAD 1.18 pdf wrote:

This default behavior can be tuned, using TSQLRestServerURI.AcquireExecutionMode[] property and AcquireExecutionLockedTimeOut[] when amLocked is set:
Command Description Default
execSOAByMethod for method-based services amUnlocked
execSOAByInterface for interface-based services amUnlocked
execORMGet for ORM reads i.e. Retrieve* methods amUnlocked
execORMWrite for ORM writes i.e. Add Update Delete TransactionBegin Commit Rollback methods amLocked +
timeout of 2000 ms
For instance, some external databases (like MS SQL) expect any transaction to be executed within the same connection, so in the same thread context for SynOleDB.pas, since it uses a per-thread connection pool. When the server is remotely access via HTTP, the incoming requests will be executed from any thread of the HTTP server thread pool. As a result, you won't be able to manage a transaction over MS SQL from the client-side with the default settings.
To fix it, you can ensure all ORM write operations will be executed in a dedicated background thread, by setting either:
AcquireExecutionMode[execORMWrite] := amBackgroundThread;
AcquireWriteMode := amBackgroundThread; // same as previous

Using amBackgroundThread will let all ZDBC commands be executed in the same thread context, so it may resolve DB-driver-level potential issues as you are suffering...

EgonHugeist · 2014-09-01 18:07:12

Hi Daniel,

i don't know enough about mORMot to know how it works with transactions. Was there a broken connection before? Something to know why a deadlock occours?

ab · 2014-09-01 18:12:44

@EgonHugeist
Thanks for joining - your expertise is very welcome!
My personal guess is that it has something to do with multi-threading.

In short:
- mORMot.pas by default, lock all write access to the DB with a critical section;
- SynDBZeos.pas does create one connection to the DB per thread.
Perhaps the multi-thread abilities of Firebird client has some problems to scale at the mORMot pace.

Thanks to god, mORMot has a lot of tuning for thread process, so running all DB writes to amBackgroundThread may be a good idea.
Perhaps also execORMGet:

AcquireExecutionMode[execORMWrite] := amBackgroundThread;
AcquireExecutionMode[execORMGet] := amBackgroundThread;

Such a configuration may help, also for other providers.

danielkuettner · 2014-09-01 18:57:50

Thanks for your answers.

I will try the option with the amBackgroundThread.

But you should know, my configuration is as simple as possible:

1. I use the mORMot internal methods by putting an update from a ajax-client (https://server/root/SQLRecord/id ...).
2. There is no code from myself on serverside
3. There are no client-request concurrently if the error occurs
4. The error occurs first after several updates without problems

Could it be, that the connection to DB won't be disconnected? The Exception message looks like there is always an active connection to FB if the update should executed.

I can make the updates after starting the server several times with absolut no errors. Then I make one update next morning and the error occurs.

Thanks,
Daniel

ab · 2014-09-01 19:14:19

@danielkuettner
So, try the following:

AcquireExecutionMode[execORMWrite] := amBackgroundThread;
AcquireExecutionMode[execORMGet] := amBackgroundThread;

Since with default settings, there is one connection by thread, there may be some multi-connection problems.

@EgonHugeist
I'm no FireBird expert - does the client disconnect the server after a timeout?

martin.suer · 2014-09-01 20:20:23

Firebird transactions work a little different than in other DBs. Firebird uses mvcc (Multi Version concurrency Control). To those not familar with this interesting concept, it is worth reading about it.
To make a Long Story short, you should try to change the transaction Isolation Level from the Firebird default of snapshot to readcommited.

EgonHugeist · 2014-09-01 21:25:33

@AB

ab wrote:

I'm no FireBird expert - does the client disconnect the server after a timeout?

Me too. I was simply thinking about: Why could another transaction raise a deadlock if he just uses one client. Actually Zeos doesn't handle possible reconnect issues(planned on 7.3). It might be possible a broken connection did force a new connection handle (done by your thread-safe-frame???) and the old connections+transaction aren't closed. AFAIK your access implementation do miss this case too. Intital issue i have: i have no idea how to setup a testcase for such situations like a bad WLAN or HTTP request.
Multitreading might be the evel too, but i couldn't see this in the toppic thread.
Anyway let's wait for reply..
What i know: PostgreSQL f.e. has a idle timeout: see http://sourceforge.net/p/zeoslib/tickets/72/

mingda · 2014-09-02 00:42:41

I think perhaps the pool connection has broke, I has a application not use mORMot but use ADO, even at localhost after sometimes the pool connection possible broke due to various reason, then need restart the server application, see my before post and the interrelated ticket

http://synopse.info/forum/viewtopic.php?id=1630
http://synopse.info/fossil/tktview?name=f024266c08

Last edited by mingda (2014-09-02 01:13:50)

ab · 2014-09-02 07:47:04

@EgonHugeist
What are your thoughts about how to implement DB re-connection?

My "naive" proposals is to identify (per DB engine) the error codes corresponding to connection errors, then re-create the connection and the statement when these kind of errors are found.
We would benefit of an explicit "connection lost" error flag from ZDBC.

I've just added some error codes to the ticket details.
You may use it as reference.

If we have those at ZDBC level, I may be able to easily implement re-connection at mORMot level.
It may be a first step for you to fully implement reconnection for ZDBC 7.3.

danielkuettner · 2014-09-02 09:01:09

@AB
I've changed my server and set the two options. I will report if it was helpful.

If not I will test with ODBC-Fb and look if the error exists.

BTW how do mORMot handle the DB-Connection? Will the connection be created after starting the server and then never closed? Or will the connection created every time on every GET/PUT request?

@EgonHugeist
My guess is like your statements. There must be always an open connection to Fb to the special field/row. When a client tries to update this field/row after a while, the open connection blocks the new update. Perhaps there is a cache on client-fb-connection keeping the connection open?

ab · 2014-09-02 09:05:48

You may also try SynDBFireDac connection, if you have FireDac installed.
This library features auto-reconnection AFAIK.

Remote DB connection is created once per each thread, the first time a request is received in this thread.
Then the connection remains alive.

As a temporary workaround, we may easily add a connection re-creation from scratch, after some time out.
For instance, if the latest connection is 5 minutes old, then we may drop it and re-create a new connection.
I suppose performance penalty would not be huge, and it would fix your particular issue.

What do you think, Daniel and EgonHugeist?

danielkuettner · 2014-09-02 09:08:07

@AB
After implementing the two options, all updates are invisible! After restart the mORMot server I can the the changes to db.

I've also tried to set

aDB.UseCache := false;

but no changes are visible (but after a restart of mORMot).

Last edited by danielkuettner (2014-09-02 09:36:30)

EMartin · 2014-09-02 10:43:05

I have worked with frameworks that implement different workarounds to this problem. RemObjects (my very old version) use the timeout solution, mentioned by AB, when the connection reach the inactive time is destroyed and removed from the connection pool (if using). AFAIR the default value for timeout is the one minute.

Another solution is used by SymmetricDS (www.symmetricds.org). Consist in define the sql statement to verify if RDBMS engine (is per engine) is alive and interval check to execute the statement (one thread per engine). When the check fail actions can be taken. The statements usually are:

- Oracle: "select 1 from dual;"
- Firebird: "select 1 from rdb$database;"
- and so for each engine ...

This last solution allow know for sure there is a problem with the engine.

But the best solution are if the engine reports the problem through error codes.

Best regards.

ab · 2014-09-02 11:11:24

@daniel
What do you mean with "but after a restart of mORMot"?
Does it mean that after a while, updates are not taken in account (meaning that the DB link is broken with Firebird, probaly), then it works after a restart?
In other terms, no change when setting amBackgroundThread?
Or is amBackgroundThread performing worse that the default settings?

@EMartin
Connection re-creation every minute may be resource consuming: for PostgreSQL, there is one process on the server per client connection.

We may implement a mix of the two: after one minute of connection, we run the "ping" statement to ensure it is still working.
Doing the "ping" at every request will slow down the process a lot, since every query will be in fact two queries - so I would not do it at every request.
Another problem of this "ping" is that sometimes, the connection is OK, e.g. "SELECT 1 FROM DUAL" works with Oracle, but if the SQL engine is broken (e.g. the writer service is broken), the next statement will fail.
So it is not perfect either.

We may implement a dual solution:
1. Every minute, do a "ping" (if we know how to do it with the corresponding DB) before the next request;
2. Every 10 minutes, force the connection re-creation.
Of course, we may add two specific options for tuning the behavior at TSQLDBConnectionProperties level.

EMartin · 2014-09-02 13:46:04

@ab

I did not mean one ping per request, I did mean a thread running check SQL statement through of the configurable interval, and when the check fail, process/events can be invoked/fired. Of course after a successful check the engine may be shutdown and the next SQL statement fail.

Anyway I think all this should be done by Zeos framework (for example), is a database problem neither ORM or HTTP.

Best regards.

mingda · 2014-09-02 13:51:11

Can if after a exception occur, we do a "ping" to check if the connection is break, if break then recreate it else do nothing, this will not affect perform, this method after one client operation failed, then succedent client operation will success, if can monitor exact connection broke exception, then will better, since there are many db connection method, check at TSQlDBConnectionProperties level should conformable.

Last edited by mingda (2014-09-02 13:54:41)

ab · 2014-09-02 15:32:42

@EMartin
We can not test a connection on another background thread, since some DB engines (e.g. OleDB AFAIR) do expect the connection to take place always in the same thread which created it.
Also, "ping" could take a long time, e.g. in case of network connection error (like an unplugged cable), until a timeout may appear. This wait time is not good for responsiveness...
Re-creating the connection every 10 minutes sounds like a not wrong so idea, since it is usually fast, and won't suffer from potential time out issue.

@mingda
We may indeed perform a true select (e.g. a TableHasRow command, aka "SELECT ID FROM ANYORMTABLE LIMIT 1"), at ORM level, in case of exception.
If we retry only once, it should do the trick, nice and easy.
Doing this, in addition to auto connection re-creation after a defined timeout, does make sense to me.

EMartin · 2014-09-02 15:49:12

@AB

I understand you, but I mean that the background thread is another connection that when fail you know that there is a problem and you can take actions.

I have implemented a custom solution in Firebird, when a sql statement fail and the exception is not EIBinterbaseError (IBX) the connection is created and re execute the statement, but all this is specific to Firebird.

I am sure that any solution on your part will be optimal, the mORMot framework speaks for you.

Best regards.

danielkuettner · 2014-09-02 15:59:08

AB wrote:

What do you mean with "but after a restart of mORMot"?
Does it mean that after a while, updates are not taken in account (meaning that the DB link is broken with Firebird, probaly), then it works after a restart?
In other terms, no change when setting amBackgroundThread?
Or is amBackgroundThread performing worse that the default settings?

Yes, when set amBackgroundThread you see absolute no changes with mORMot-clients. But the changes are written to Fb (I can see it with FlameRobin).
And not only after a while, you see nothing just after starting the server. Thats also a bit hard to test, because I have to look at FlameRobin and Exceptions wouldn't show in mORMot clients (I suggest) but only in log file.

Now to the original problem. I think it were absolut enough, when the active connection to DB would be released after a minute of inactivity. All the other suggestions are oversized and slower (mORMot should remain fast).

All work fine until a time of inactivity.

@ab
BTW, how do you implement a timer (we say after one minute) in mORMot? I wanted use a timer in a mORMot service to flush the db-cache and this was not so trivial. Do you have a component for this in mORMot?

Last edited by danielkuettner (2014-09-02 18:49:37)

EMartin · 2014-09-02 16:00:05

I did mean when the exception inherits from EIBInterBaseError I recreate the connection and reexecute the statement. At this point I have the certainty that the SQL statement syntax and parameters is OK because the sql statement has been prepared and executed before. I keep the connection and sql statement objects for performance.

EgonHugeist · 2014-09-02 20:04:52

ab wrote:

What are your thoughts about how to implement DB re-connection?
My "naive" proposals is to identify (per DB engine) the error codes corresponding to connection errors, then re-create the connection and the statement when these kind of errors are found.

exactly this is suppressed in our FB implementation, by a dev-member "AVZ"(which is a FB "guru"). Check ZDbcInterbaseUtils.pas -> PrepareStatement. I don't know whay this is done...

ab wrote:

We would benefit of an explicit "connection lost" error flag from ZDBC.
I've just added some error codes to the ticket details.
You may use it as reference.

I'll check your suggestion. Personaly i was thinking about registering a "TProcedure() of objet" as a callback routine if a connection lost happens. Flags are various and need to be handled from caller level again. So we need either a common flag or a callback function. (i would prefere this because each ZDBC interface could be closed and nil the handles silently).

ab wrote:

If we have those at ZDBC level, I may be able to easily implement re-connection at mORMot level.
It may be a first step for you to fully implement reconnection for ZDBC 7.3.

might be a good idea!
But there must be done much more than trapping this error flags. What should really happen if a "Connection lost" happens? Best case would be reconnect with same handle which would catch such dead-lock situations too. Might be possible to implement(even if i don't think this is possible) need to check the API's. If this is not possible.. we need a shutdown or a admin who kills the unused connections than.

BTW. Ping is supported by IZConnection, Arnaud.

ab wrote:

My guess is like your statements. There must be always an open connection to Fb to the special field/row. When a client tries to update this field/row after a while, the open connection blocks the new update. Perhaps there is a cache on client-fb-connection keeping the connection open?

Possible if prepared non commited stmt-handle blocks the transaction of another connection. Thus i don't think this is the case. Our FB implmentation seems to be stable even if i changed a lot for better performance. The SQLite statement thing you did point me was another thing. Before the performance upgrades did happen, we had a complete different (strange) behavior for this plain. So after my changes we did return to a true alpha state. Thanks to synopse we did resolve these issues too.

mingda · 2014-09-03 01:11:39

another hint, since the TSQLDBConnectionProperties can has some connections in pool, if db server's problem cause one connection break, generally else connection should also break, In my a little background service, if exception occur, i simple clear the connection pool, then every things later will ok, perhaps not need recreate one connection, since other connecitons also possible have breaked.

      try
        fDMInvoiceAssessment.InvoiceAssessment;
      except
        fDMInvoiceAssessment.ClearConnectionPool;
        HandleException;
      end;

procedure TDMInvoiceAssessment.ClearConnectionPool;
begin
  fProps.ClearConnectionPool;
end;

danielkuettner · 2014-09-03 08:56:34

@ab
amBackgroundThread seems to work. Up Today I had no Exception more (I looked in the log, because mORMot is a little bit mysterious in relation to updates...).
If you could make the updates visible, it would be an option. As you said there wouldn't be performance issues, but I can't belief it so really in case of many clients.

danielkuettner · 2014-09-04 08:12:58

@mingda
Can you explain this a little bit:

mingda wrote:

In my a little background service...

Do you have a second mMORmot server-service? Or do you you have a second TSQLRESTServer?

ab · 2014-09-04 08:46:38

We have just added the new TSQLDBConnectionProperties.ConnectionTimeOutMinutes property to specify a maximum period of inactivity after which all connections will be flushed and recreated, to avoid potential broken connections issues.
In practice, recreating the connections after a while is safe and won't slow done the process - on the contrary, it may help reducing the consumpted resources, and stabilize long running n-Tier servers.
ThreadSafeConnection method will check for the last activity on this TSQLDBConnectionProperties instance, then call ClearConnectionPool to release all active connections if the idle time elapsed was too long.
See http://synopse.info/fossil/info/b77a46e29f5a62

Warning: no connection shall still be used on the background (e.g. in multi-threaded applications), or some unexpected issues may occur.
For instance, ensure that your mORMot ORM server runs all its statements in blocking mode for both read and write:

aServer.AcquireExecutionMode[execORMGet] := am***;
aServer.AcquireExecutionMode[execORMWrite] := am***;

here, safe blocking am*** modes are any mode but amUnlocked, i.e. either amLocked, amBackgroundThread or amMainThread.

danielkuettner · 2014-09-04 11:30:44

@ab
Thank you for your fast solution. I will test it and report, if it works.

Daniel

mingda · 2014-09-04 12:20:55

danielkuettner wrote:

Do you have a second mMORmot server-service? Or do you you have a second TSQLRESTServer?

No, a normal application not use mORMot, only use SynDB to connection DB.

danielkuettner · 2014-09-04 18:32:03

After testing I've same Exception:

First update were at 13:37.

20140904 13373719  < SQL   		TSQLDBZEOSStatement(0000000002DB85F0) update Kontoauszug set KONTOAUSZUG_KATEGORIE='1400' where KONTOAUSZUG_ID=30607
20140904 13373962  =  -    	00.003.169
20140904 13373962  = srvr  	TSQLPrcashRestServerDB(0000000002967150) PUT Kontoauszug -> 200

First Exception came on 20:17:

20140904 20175744  9 SQL   		TSQLDBZEOSStatement(0000000002DBA990) update Kontoauszug set KONTOAUSZUG_KATEGORIE='210' where KONTOAUSZUG_ID=30617
20140904 20175745  9 EXC   		EZSQLException ("SQL Error:  deadlock update conflicts with concurrent update concurrent transaction number is 123957. Error Code: -913. can't format message 13:87 -- message file C:\\firebird.msg not found The SQL: update Kontoauszug set KONTOAUSZUG_KATEGORIE=? where KONTOAUSZUG_ID=?; ") at 00000000004797AB ZDbcPostgreSqlMetadata.TZPostgreSQLDatabaseInfo

Server was 64bit service.

danielkuettner · 2014-09-05 07:05:07

Now I've set TSQLDBConnectionProperties.ConnectionTimeOutMinutes = 1
and running service under 32bit and Debug.
I'll call if the Exception happens again.

ab · 2014-09-05 15:49:49

It should be better with the right option.
I hope so.

danielkuettner · 2014-09-06 14:17:27

I almost can't believe it, but it seems to work now (under 32 and 64bit).
Thanks to all!

ab · 2014-09-06 14:48:56

Sound quite logical to me, on the contrary.

Yes, thanks you all for your ideas: the final ORM-level implementation of flushing all connections after a period of inactivity sounds quite simple and stable.

BTW, TSQLDBConnectionProperties.ConnectionTimeOutMinutes = 1 is perhaps a bit paranoid, I guess TSQLDBConnectionProperties.ConnectionTimeOutMinutes = 5 is enough for most use.
And ensure you use this property in conjunction with:

AcquireExecutionMode[execORMWrite] := amBackgroundThread;
AcquireExecutionMode[execORMGet] := amBackgroundThread;

Thanks a lot for your feedback!

danielkuettner · 2014-09-06 15:17:37

@AB
AcquireExecutionMode[execORMWrite] := amBackgroundThread;
AcquireExecutionMode[execORMGet] := amBackgroundThread;

were the options, where no updates were visible more. I've successful tested with not set these options.

BTW, TSQLDBConnectionProperties.ConnectionTimeOutMinutes = 1 is very fast, you don't feel any performance issues.

Last edited by danielkuettner (2014-09-06 15:24:56)

alpinistbg · 2014-11-26 08:06:05

With my humble experience with Firebird, I want to point your attention to some earlier posts:

ab wrote:

In short:
- mORMot.pas by default, lock all write access to the DB with a critical section;
- SynDBZeos.pas does create one connection to the DB per thread.
Perhaps the multi-thread abilities of Firebird client has some problems to scale at the mORMot pace.

and:

martin.suer wrote:

Firebird transactions work a little different than in other DBs. Firebird uses mvcc (Multi Version concurrency Control). To those not familar with this interesting concept, it is worth reading about it.
To make a Long Story short, you should try to change the transaction Isolation Level from the Firebird default of snapshot to readcommited.

So martin.suer noted that Firebird uses MVCC for concurrency control, not locking as other DBs. The word "Deadlock" from the error message should be read as "Conflict" in that context. As long as mORMot creates different connections for each thread, those conflicts will be inevitable.

On the other hand, for Firebird there is no such thing as DML outside a transaction, so I can't guess the effect of Zeos tiNone transaction isolation used in:

constructor TSQLDBZEOSConnection.Create(aProperties: TSQLDBConnectionProperties);
begin
  inherited Create(aProperties);
  ...
  fDatabase.SetTransactionIsolation(tiNone);
end;

Perhaps that is the reason for:

danielkuettner wrote:

AcquireExecutionMode[execORMWrite] := amBackgroundThread;
AcquireExecutionMode[execORMGet] := amBackgroundThread;
were the options, where no updates were visible more. I've successful tested with not set these options.

In the background process all DMLs are executed properly, but there is a need for an explicit Commit at all clients in order to see the changes if isolation is tiReadCommited or higher.

I guess that playing with TSQLDBConnectionProperties.ConnectionTimeOutMinutes just triggers some implicit Commit and that is the reason everything to look fine at the surface. I suggest explicit StartTransaction/Commit to be used at the Client instead of changing the execution mode and the connection timeout property.

AFAIK the Oracle also uses MVCC and since MSSQL 2008 there is an option to run the server in either locking or MVCC mode. So this can be an issue on other RDBMSes too.

Regards,

ab · 2014-11-26 08:32:09

In practice, ConnectionTimeOutMinutes would never re-connect to the server within a transaction.
Unless the transaction was pending for ConnectionTimeOutMinutes minutes - which is a very bad design!
So I suppose this is not the issue here, even if ConnectionTimeOutMinutes should better check that there is no pending transaction.

alpinistbg · 2014-11-26 08:47:37

As I said, no idea what happens with tiNone isolation in ZEOS. But using multiple connections/threads will make conflicts very easy in MVCC. Even for things which are quite natural in RDBMS with locking.

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

@alpinistbg
But the problem is not a special problem of MVCC, it's a multithreading one.

But, in my tests I haven't this issue in case of much threads. Only after I while of inactivity, and therefore we have created this ConnectionTimeout property.

Last edited by danielkuettner (2014-11-26 10:22:28)

miab3 · 2014-11-26 10:57:24

@danielkuettner

Could you try ZEOS 7.2 and Firebird with enabled 'hard_commit=TRUE'?

Michal

danielkuettner · 2014-11-26 11:22:48

@miab3
Yes, I will test it and give you feedback.

danielkuettner · 2014-11-26 13:59:14

@miab3
Deadlock right now with option hard_commit=true.
Sorry.

miab3 · 2014-11-26 14:17:00

@danielkuettner, @ab,

Perhaps wrong is selected (set) TransactIsolationLevel?
http://www.devrace.com/en/fibplus/articles/479.php
http://www.devrace.com/en/fibplus/articles/3292.php

Currently in ZEOS is:

  Params.Add('isc_tpb_version3');
  case TransactIsolationLevel of
    tiReadCommitted:
      begin
        Params.Add('isc_tpb_read_committed');
        Params.Add('isc_tpb_rec_version');
        Params.Add('isc_tpb_nowait');
      end;
    tiRepeatableRead:
      begin
        Params.Add('isc_tpb_concurrency');
        Params.Add('isc_tpb_nowait');
      end;
    tiSerializable:
      begin
        Params.Add('isc_tpb_consistency');
      end;
    else
    begin
      { Add user defined parameters for transaction }
      Params.Clear;
      Params.AddStrings(Info);
	  	 		 	  
    end;
  end;

The default is tiNone(else).

Michal

Last edited by miab3 (2014-11-26 15:08:15)

EMartin · 2014-11-26 14:43:56

The most recommended transaction management is:

Transaction Isolation Level: read commited
Transaction parameters (no textual names):
- hard commit: true, commit retaining (soft commit) es very danger, degrading the Firebird engine.
- wait: in deadlock case (conflict with another transaction) the transaction (A) wait to another transaction (B) rollback their work, if the other transaction (B) commit their work then transaction (A) raise deadlock error, on the contrary transaction (A) commit their work.
- wait timeout N seconds: same wait but waiting the N seconds
- nowait: in deadlock case (conflict with another transaction) the transaction (A) raise immediatly deadlock error.

It is also highly recommended use:

DB.BeginTransaction;
try
  ...
  DB.CommitTransaction;
except
  DB.RollbackTransaction;
end;

Same connection can be used in different threads but respecting the former (try ... except).

All this, is in my experience reading (Firebird gurus) and using Firebird. With other engines (MSSQL, Oracle, Informix, DB2, MySQL) using ADO and applying the same this works.

Best regards and sorry for by english.

danielkuettner · 2014-11-26 15:50:56

I don't know if TransactionIsolationLevel is important for the problem. We have "one" Props under mORMot which is shared by the threads.

danielkuettner · 2014-11-26 19:47:51

@EMartin

After reading this article: http://ibexpert.net/ibe/index.php?n=Doc … ockingMode
I think you have write.

First we should check that ReadCommited is active.

Then that every Transaction will be committed or rollbacked.
With AutoCommit this is guaranteed.

After "this" deadlocks shouldn't happens more, only real deadlocks.

It were also good, when there were an option "ClearConnectionPoolIfDeadlock". Then the ConnectionTimeOutMinutes is not for deadlocks only for memory.

EMartin · 2014-11-26 20:21:35

@danielkuettner

ReadCommited, al least in Firebird, must be default configuration "out of box". With AutoCommit I prefer have the control on start-commit-rollback, but it's a matter of taste. Certainly the deadlocks should be rare. On the other hand when clearing the connection pool if the connection is in the pool mean that is not in use whereby the connection it is free of transactions and can be destroyed.

Best regards.

ab · 2014-11-26 20:31:20

First of all, you may just try to use "tmMainConnection" parameter for the properties' ThreadingMode.
For Firebird and PostgreSQL, it was found to be more stable.

I've refactored SynDB.pas to implement the ConnectionTimeOutMinutes parameter in a much more cleaner way:
- with a per-thread timing and reconnection;
- disallowing transactions if ThreadingMode=tmThreadPool (in this case, commit/execute/rollback should be in the same thread/connection).
So it would fix any potential threading issue e.g. with OleDB.
See http://synopse.info/fossil/info/8156aa113a

mORMot Open Source

#1 2014-09-01 13:07:39

ZEOS + Firebird + Update-Problem

#2 2014-09-01 18:04:35

Re: ZEOS + Firebird + Update-Problem

#3 2014-09-01 18:07:12

Re: ZEOS + Firebird + Update-Problem

#4 2014-09-01 18:12:44

Re: ZEOS + Firebird + Update-Problem

#5 2014-09-01 18:57:50

Re: ZEOS + Firebird + Update-Problem

#6 2014-09-01 19:14:19

Re: ZEOS + Firebird + Update-Problem

#7 2014-09-01 20:20:23

Re: ZEOS + Firebird + Update-Problem

#8 2014-09-01 21:25:33

Re: ZEOS + Firebird + Update-Problem

#9 2014-09-02 00:42:41

Re: ZEOS + Firebird + Update-Problem

#10 2014-09-02 07:47:04

Re: ZEOS + Firebird + Update-Problem

#11 2014-09-02 09:01:09

Re: ZEOS + Firebird + Update-Problem

#12 2014-09-02 09:05:48

Re: ZEOS + Firebird + Update-Problem

#13 2014-09-02 09:08:07

Re: ZEOS + Firebird + Update-Problem

#14 2014-09-02 10:43:05

Re: ZEOS + Firebird + Update-Problem

#15 2014-09-02 11:11:24

Re: ZEOS + Firebird + Update-Problem

#16 2014-09-02 13:46:04

Re: ZEOS + Firebird + Update-Problem

#17 2014-09-02 13:51:11

Re: ZEOS + Firebird + Update-Problem

#18 2014-09-02 15:32:42

Re: ZEOS + Firebird + Update-Problem

#19 2014-09-02 15:49:12

Re: ZEOS + Firebird + Update-Problem

#20 2014-09-02 15:59:08

Re: ZEOS + Firebird + Update-Problem

#21 2014-09-02 16:00:05

Re: ZEOS + Firebird + Update-Problem

#22 2014-09-02 20:04:52

Re: ZEOS + Firebird + Update-Problem

#23 2014-09-03 01:11:39

Re: ZEOS + Firebird + Update-Problem

#24 2014-09-03 08:56:34

Re: ZEOS + Firebird + Update-Problem

#25 2014-09-04 08:12:58

Re: ZEOS + Firebird + Update-Problem

#26 2014-09-04 08:46:38

Re: ZEOS + Firebird + Update-Problem

#27 2014-09-04 11:30:44

Re: ZEOS + Firebird + Update-Problem

#28 2014-09-04 12:20:55

Re: ZEOS + Firebird + Update-Problem

#29 2014-09-04 18:32:03

Re: ZEOS + Firebird + Update-Problem

#30 2014-09-05 07:05:07

Re: ZEOS + Firebird + Update-Problem

#31 2014-09-05 15:49:49

Re: ZEOS + Firebird + Update-Problem

#32 2014-09-06 14:17:27

Re: ZEOS + Firebird + Update-Problem

#33 2014-09-06 14:48:56

Re: ZEOS + Firebird + Update-Problem

#34 2014-09-06 15:17:37

Re: ZEOS + Firebird + Update-Problem

#35 2014-11-26 08:06:05

Re: ZEOS + Firebird + Update-Problem

#36 2014-11-26 08:32:09

Re: ZEOS + Firebird + Update-Problem

#37 2014-11-26 08:47:37

Re: ZEOS + Firebird + Update-Problem

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

Re: ZEOS + Firebird + Update-Problem

#39 2014-11-26 10:57:24

Re: ZEOS + Firebird + Update-Problem

#40 2014-11-26 11:22:48