mORMot Open Source

alexdmatveev

Member

Registered: 2014-09-12

Posts: 87

problem in time of concurent access to table

Hello,

we have a mormot managed server that provides to client operations with MySQL database.
The server works intensively (but not critically) as I see.

Problem is so:
we have one basic table to store Transactions objects. Every transactions must be written there.
Everything works good with mormot as single client for this table.

2 days ago the developer from our customer created new application that writes data into the same table.
During writing by this application we have problem with mormot server and clients.

I provide the deleper's mail to me (maybe it will help to understand the problem and provide suitable solution).

Hello everybody,

I have written an application to move the transactions history from our secondary DB appllication to mormot DB application. When I add  transactions to the "transactions" table (using the autoincrement id) and a client application is sending, all clients are blocked.

To unblock, the server softwares need to be restarted and also every communicator (client side) that is blocked needs to be restarted.
From what I have seen the server side is not using the ID as autoincrement field and after some retries it blocks.

Could you write me how long you need to solve this problem?

Best regards,

Could you show me the way to search suitable solution?

Thanks a lot.

Chaa

Member

Registered: 2011-03-26

Posts: 245

Re: problem in time of concurent access to table

How exactly you connect to MySQL (SynDBZeos/SynDBODBC/SynDBFireDAC)?

I had a similar problem with SynDBFireDAC and Firebird.
FireDAC connection work in AutoCommit mode (FireDAC automatically starts and commit transaction).
But if TSQLDBConnectionProperties.UseCache = True (by default), then statemets cached and transactions not committed.
After some time active transactions lock tables and server blocks.

At the moment I set TSQLDBConnectionProperties.UseCache to False.

alexdmatveev

Member

Registered: 2014-09-12

Posts: 87

Re: problem in time of concurent access to table

I use Unidac components to establish MySQL connection on the server.
I see your idea, thanks a lot.
I will try it today/tomorrow and will post the result.

alexdmatveev

Member

Registered: 2014-09-12

Posts: 87

Re: problem in time of concurent access to table

Unfortunatelly, the offered solution with UseCache=false does not work.

Code snippet from server app:

  aProps := TSQLDBUniDACConnectionProperties.Create('MySQL', 'server', 'root', '*****');
  aProps.UseCache := False;
  aProps.SpecificOptions.Values['Server'] := 'localhost';
  aProps.SpecificOptions.Values['Port'] := '3306';
  aProps.SpecificOptions.Values['UseUnicode'] := 'True';
  aProps.SpecificOptions.Values['Charset'] := 'utf8';
  aProps.ThreadingMode := tmThreadPool;

  try
    aModel := CreateDataModel;
    VirtualTableExternalRegisterAll(aModel, aProps);

Has anybody other ideas?

Thanks a lot.

Last edited by alexdmatveev (2016-11-09 10:05:36)

alexdmatveev

Member

Registered: 2014-09-12

Posts: 87

Re: problem in time of concurent access to table

the show is going on...

I can not understand the problem but after our tests it looks like mORMot Rest server does not use Autoincrement fields directly but via internal cache.

Is it possible?

The message from our customer that makes tests too:

The Server is not using the autoincrements, it has an internal counter setted when the server software start. When my software makes the insert using autoincrements it use some new values and after when the Server try to save data it has a duplicated key error.
I have just tested it the autoinc is setted to 57099 but the Server inserts value with ID 56711

Could you show me the way to make the server use autoincrement directly?

thanks.

Chaa

Member

Registered: 2011-03-26

Posts: 245

Re: problem in time of concurent access to table

See:

TSQLRestStorageExternal.EngineAddUseSelectMaxID
TSQLRestStorageExternal.EngineAddForcedID
TSQLRestStorageExternal.OnEngineAddComputeID

You can use your own handler to get autoincremented IDs, or use MAX(ID)+1 for IDs.

alexdmatveev

Member

Registered: 2014-09-12

Posts: 87

Re: problem in time of concurent access to table

after an hour deep in mormot sources and docs... I give up.
Pls show me how to get access to TSQLRestStorageExternal object to set EngineAddUseSelectMaxID=True.

Is it part of client or server sides?

Thanks a lot.

Last edited by alexdmatveev (2016-11-10 13:12:32)

Chaa

Member

Registered: 2011-03-26

Posts: 245

Re: problem in time of concurent access to table

It's server side.

var
    Server: TSQLRestServer;
    External: TSQLRestStorageExternal;
begin
    External := Server.StaticVirtualTable[TSQLMyTable] as TSQLRestStorageExternal;
end;

alexdmatveev

Member

Registered: 2014-09-12

Posts: 87

Re: problem in time of concurent access to table

five stars for support
thanks a lot.

tomorrow we will test it but on first look it works.