Pages: 1

#1 2015-03-11 19:31:46

alpinistbg
Member
Registered: 2014-11-12
Posts: 124

BatchAdd and forced ID's

It seems that the TSQLRestClientURI.BatchAdd(Value, SendData, ForceID) doesn't work right when ForceID=true.
For example:

Remote := TSQLHttpClient.Create('localhost', '8080', Model);
...
R.ID := X;
Remote.Add(R, True, True); // Works fine
...
Remote.BatchStart(Nil);
R.ID := Y;
Remote.BatchAdd(R, True, True);
Remote.BatchSend(IDs); // Assigns a new ID<>Y

Offline

#2 2015-03-11 21:16:25

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,661
Website

Re: BatchAdd and forced ID's

Could you please create a ticket?

Thanks for the feedback.

Online

#3 2015-03-12 07:54:45

alpinistbg
Member
Registered: 2014-11-12
Posts: 124

Re: BatchAdd and forced ID's

Done. Ticket http://synopse.info/fossil/tktview?name=490f61ec8e.

Offline

#4 2015-03-30 22:11:19

alpinistbg
Member
Registered: 2014-11-12
Posts: 124

Re: BatchAdd and forced ID's

@ab,
Since I can't figure what is the most convenient way to comment on http://synopse.info/fossil/tktview?name=490f61ec8e, I'll just post some comments on that thread.

My feedback for the fix is fairly positive - now the BatchAdd() seems to work correctly with forced ID. I'll make more tests during the next days. Currently I'm trying to implement the replication pattern discussed in http://synopse.info/forum/viewtopic.php?id=2160. Because of the BatchAdd() issue, I have tried to use TransactionBegin/Commit/Rollback as a workaround.

Consider the following snippet: 

    Remote := TSQLHttpClient.Create(FRemoteHost, FRemotePort, RemoteModel);
    ...

            {$IFDEF USETRN} if not Remote.TransactionBegin(Nil, 1) then
              raise Exception.Create('(Remote TRN) ' + UTF8ToString(Remote.LastErrorMessage));
            {$ELSE}if not Remote.BatchStartAny(MaxInt) then
              raise Exception.Create('(Remote Batch)'); {$ENDIF}

            try
              case Journal.Operation of

                TSQLEvent.seAdd:
                  if Database.Retrieve(Journal.RecordKey, Rec) then
                    SingleError :=
                      {$IFDEF USETRN} Remote.Add(Rec, True, True) <> Journal.RecordKey
                      {$ELSE}Remote.BatchAdd(Rec, True, True) < 0; {$ENDIF}

                TSQLEvent.seUpdate:
                  if Database.Retrieve(Journal.RecordKey, Rec) then
                    SingleError :=
                      {$IFDEF USETRN} not Remote.Update(Rec)
                      {$ELSE}Remote.BatchUpdate(Rec) < 0; {$ENDIF}

                TSQLEvent.seDelete:
                  SingleError :=
                    {$IFDEF USETRN} not Remote.Delete(TSQLAlprRecognition, Journal.RecordKey)
                    {$ELSE}Remote.BatchDelete(Journal.RecordKey) < 0; {$ENDIF}

              end;

              if SingleError then
                 raise Exception.Create(UTF8ToString(Remote.LastErrorMessage));

              Journal.DonorKey := Journal.ID;
              Journal.DonorSiteIdentifier := localSid;

              {$IFDEF USETRN} if Remote.Add(Journal, True, False) = 0 then
                raise Exception.Create(UTF8ToString(Remote.LastErrorMessage));
              {$ELSE}if Remote.BatchAdd(Journal, True, False) < 0 then
                raise Exception.Create('Batch Add'); {$ENDIF}

              if Assigned(FOnProgress) then
                Synchronize(@DoProgressEvent);

              {$IFDEF USETRN} Remote.Commit(1);
              {$ELSE}if Remote.BatchSend(IDs) <> HTML_SUCCESS then
                raise Exception.Create(UTF8ToString(Remote.LastErrorMessage));
              {$ENDIF}

            except
              {$IFDEF USETRN} Remote.RollBack(1);{$ENDIF}
              raise;
            end;

It can be seen that using a conditional define USETRN can switch between batches and transactions. Surprisingly for me, the transactions also *failed* to ensure the atomicity of the update.   

That code was executed once in 5 minutes with a rowset of 100 records. Each of the records was between 90-180K. Everything went just OK until the site connection speed dropped (because of INet provider), then client code failed with time-out error and on the next invocation failed to progress further because of a PK violation.  Looking at the server side I've found that there is a record inserted but corresponding journal entry was not there (i.e.   Remote.Add(Rec, True, True) succeeded but  Remote.Add(Journal, True, False) failed).

The error popped up irregularly (each time fixing DB by hand) - it seems that the slow connection speed was causing this, and sometimes the failure was in Remote.TransactionBegin(Nil, 1).

I suspect some transaction primitives mishandling at the  server side in case of a dropped/timed-out connection. Or may be I'm making it in a wrong way?

Offline

#5 2015-03-31 08:04:11

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,661
Website

Re: BatchAdd and forced ID's

First of all, transactions are not safe to be used from the client side, depending on the DB backend.
So batch process is always to be preferred for remote ACID behavior.

To help implementing the replication pattern, I've added the TSQLRestTempStorage "asynchronous write" class.
But from your current implementation, I'm not sure it would be what you need.

I do not understand well why there is the issue with BatchSend().
If the info is correct from the client side, all the Batch content should be sent as once, so there should be no problem.
Or perhaps the problem occurs before the Remote.BatchSend() returns its status? That is, the data is sent successfully, but the client fails to receive the status from the server?

Online

#6 2015-03-31 10:29:14

alpinistbg
Member
Registered: 2014-11-12
Posts: 124

Re: BatchAdd and forced ID's

I didn't say batches don't work, may be it was unclear, sorry. Transactions don't work. I've used them as a workaround because of the previous issue with the forced ID's.

To help implementing the replication pattern, I've added the TSQLRestTempStorage "asynchronous write" class.
But from your current implementation, I'm not sure it would be what you need.

So what's the idea?

Offline

#7 2015-03-31 11:12:51

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,661
Website

Re: BatchAdd and forced ID's

IMHO you should use a TSQLRestBatch in this case, and not rely on individual Remote.Add/Remote.Update methods.

You could use a local TSQLRestBatch variable, instead of Remote.BatchAdd() method.

Online

#8 2015-04-06 14:52:00

alpinistbg
Member
Registered: 2014-11-12
Posts: 124

Re: BatchAdd and forced ID's

It seems that I just can't get it right! Please help and show me where I'm wrong ... using the above code (i.e. with BatchStartAny/BatchSend) and two client computers, after some time the server starts issuing warnings "TSQLRestServerDB.TransactionBegin failed -> no transaction" and "Active Transaction -> ignore AutomaticTransactionPerRow". By my observation that happens when the client computers overlap each other with their batches. Here is a little snip of the log (long INSERTs are truncated):

17:04:29	Enter	 TSQLRestServerDB(000CBFD0).URI(PUT repl/Batch inlen=129869)
17:04:29	Leave	 00.000.038
17:04:29	Service call	 TSQLRestServerDB(000CBFD0) Batch
17:04:29	Enter	 TSQLRestServerDB(000CBFD0).0057C56E 
17:04:29	Leave	 00.000.032
17:04:29	Cache	 TSQLDatabase(000DC418) lpr_test_project_s.db3 cache flushed
17:04:29	SQL	 TSQLDatabase(000DC418) 59us lpr_test_project_s.db3 BEGIN TRANSACTION;
17:04:29	SQL	 TSQLRestServerDB(000CBFD0) 5.53ms  INSERT INTO AlprRecognition (RowID,Site,Lane,Status,PlateText,PlateConfidenceX100,CreatedOn,Features,JSONResult,Snapshot) VALUES (:(108268719):,:('USER-PC'):,:(2):,:(1):,:('G2EEN'):,:(7432):,:(135237667995):,:(''):,:('{ "version" : 2, "data_type" : "alpr_results", "epoch_time" : -1895883568, "img_width" : 640, "img_height" : 512, "processing_time_ms" : 6.33002991000000E+002, "regions_of_interest" : [{ "x" : 0, "y" : 0, "width" : 640, "height" : 512 }], "results" : [{ "plate" : "G2EEN", "confidence" : 7.43161010000000E+001, "matches_template" : 0 ....
17:04:29	SQL	 TSQLRestServerDB(000CBFD0) 109us returned=43026 select max(rowid) from DataJournal
17:04:29	SQL	 TSQLRestServerDB(000CBFD0) 824us  INSERT INTO DataJournal (RowID,SiteIdentifier,TableIdentifier,RecordKey,Operation,Stamp,DonorKey,DonorSiteIdentifier) VALUES (:(43027):,:(8719):,:(2):,:(108268719):,:(0):,:(135237667995):,:(9999):,:(8719):);
17:04:29	SQL	 TSQLDatabase(000DC418) 494.92ms lpr_test_project_s.db3 COMMIT TRANSACTION;
17:04:29	Server	 TSQLRestServerDB(000CBFD0)   PUT repl/Batch SOA-Method -> 200 with outlen=17 in 501271 us
17:04:30	Enter	 TSQLRestServerDB(000CBFD0).URI(PUT repl/Batch inlen=273589)
17:04:30	Leave	 00.000.042
17:04:30	Service call	 TSQLRestServerDB(000CBFD0) Batch
17:04:30	Enter	 TSQLRestServerDB(000CBFD0).0057C56E 
17:04:30	Leave	 00.000.015
17:04:30	SQL	 TSQLDatabase(000DC418) 24us lpr_test_project_s.db3 BEGIN TRANSACTION;
17:04:30	SQL	 TSQLRestServerDB(000CBFD0) 6.57ms  INSERT INTO AlprRecognition (RowID,Site,Lane,Status,PlateText,PlateConfidenceX100,CreatedOn,Features,JSONResult,Snapshot) VALUES (:(371134149):,:('KA'):,:(4):,:(1):,:('4BM5'):,:(8249):,:(135237668437):,:(''):,:('{ "version" : 2, "data_type" : "alpr_results", "epoch_time" : -1895469568, "img_width" : 1280, "img_height" : 1024, "processing_time_ms" : 1.35053405800000E+003, "regions_of_interest" : [{ "x" : 0, "y" : 0, "width" : 1280, "height" : 1024 }], "results" : [{ "plate" : "4BM5", "confidence" : 8.24889530000000E+001, "matches_template" : 0 ....
17:04:30	SQL	 TSQLRestServerDB(000CBFD0) 156us returned=43027 select max(rowid) from DataJournal
17:04:30	SQL	 TSQLRestServerDB(000CBFD0) 1.00ms  INSERT INTO DataJournal (RowID,SiteIdentifier,TableIdentifier,RecordKey,Operation,Stamp,DonorKey,DonorSiteIdentifier) VALUES (:(43028):,:(4149):,:(2):,:(371134149):,:(0):,:(135237668438):,:(30082):,:(4149):);
17:04:30	Enter	 TSQLRestServerDB(000CBFD0).URI(PUT repl/Batch inlen=89064)
17:04:30	Leave	 00.000.155
17:04:30	Service call	 TSQLRestServerDB(000CBFD0) Batch
17:04:30	Enter	 TSQLRestServerDB(000CBFD0).0057C56E 
17:04:30	Leave	 00.000.017
17:04:30	SQL	 TSQLDatabase(000DC418) 394.19ms lpr_test_project_s.db3 COMMIT TRANSACTION;
17:04:30	Server	 TSQLRestServerDB(000CBFD0)   PUT repl/Batch SOA-Method -> 200 with outlen=17 in 405556 us
17:04:30	SQL	 TSQLDatabase(000DC418) 391.81ms lpr_test_project_s.db3 ROLLBACK TRANSACTION;
17:04:30	Warning	 TSQLRestServerDB(000CBFD0) TSQLRestServerDB.EngineBatchSend: TSQLRestServerDB.TransactionBegin failed -> no transaction
17:04:30	Warning	 TSQLRestServerDB(000CBFD0) TSQLRestServerDB.EngineBatchSend: TSQLRestServerDB.TransactionBegin failed -> no transaction
17:04:31	SQL	 TSQLRestServerDB(000CBFD0) 503.38ms  INSERT INTO AlprRecognition (RowID,Site,Lane,Status,PlateText,PlateConfidenceX100,CreatedOn,Features,JSONResult,Snapshot) VALUES (:(108278719):,:('USER-PC'):,:(1):,:(1):,:('H32O'):,:(7204):,:(135237668103):,:(''):,:('{ "version" : 2, "data_type" : "alpr_results", "epoch_time" : -1895783568, "img_width" : 640, "img_height" : 512, "processing_time_ms" : 4.96411011000000E+002, "regions_of_interest" : [{ "x" : 0, "y" : 0, "width" : 640, "height" : 512 }], "results" : [{ "plate" : "H32O", "confidence" : 7.20362930000000E+001, "matches_template" : 0 ....
17:04:31	SQL	 TSQLRestServerDB(000CBFD0) 242us returned=43028 select max(rowid) from DataJournal
17:04:31	SQL	 TSQLRestServerDB(000CBFD0) 249.60ms  INSERT INTO DataJournal (RowID,SiteIdentifier,TableIdentifier,RecordKey,Operation,Stamp,DonorKey,DonorSiteIdentifier) VALUES (:(43029):,:(8719):,:(2):,:(108278719):,:(0):,:(135237668103):,:(10000):,:(8719):);
17:04:31	Server	 TSQLRestServerDB(000CBFD0)   PUT repl/Batch SOA-Method -> 200 with outlen=17 in 1141657 us
17:04:32	Enter	 TSQLRestServerDB(000CBFD0).URI(PUT repl/Batch inlen=152689)
17:04:32	Leave	 00.000.044
17:04:32	Service call	 TSQLRestServerDB(000CBFD0) Batch
17:04:32	Enter	 TSQLRestServerDB(000CBFD0).0057C56E 
17:04:32	Leave	 00.000.026
17:04:32	Warning	 TSQLRestServerDB(000CBFD0) Active Transaction -> ignore AutomaticTransactionPerRow
17:04:32	SQL	 TSQLRestServerDB(000CBFD0) 818.33ms  INSERT INTO AlprRecognition (RowID,Site,Lane,Status,PlateText,PlateConfidenceX100,CreatedOn,Features,JSONResult,Snapshot) VALUES (:(108288719):,:('USER-PC'):,:(2):,:(1):,:('293H'):,:(6663):,:(135237668114):,:(''):,:('{ "version" : 2, "data_type" : "alpr_results", "epoch_time" : -1895772568, "img_width" : 640, "img_height" : 512, "processing_time_ms" : 6.83242981000000E+002, "regions_of_interest" : [{ "x" : 0, "y" : 0, "width" : 640, "height" : 512 }], "results" : [{ "plate" : "293H", "confidence" : 6.66304930000000E+001, "matches_template" : 0 ....
17:04:32	SQL	 TSQLRestServerDB(000CBFD0) 320us returned=43029 select max(rowid) from DataJournal
17:04:32	SQL	 TSQLRestServerDB(000CBFD0) 487.90ms  INSERT INTO DataJournal (RowID,SiteIdentifier,TableIdentifier,RecordKey,Operation,Stamp,DonorKey,DonorSiteIdentifier) VALUES (:(43030):,:(8719):,:(2):,:(108288719):,:(0):,:(135237668114):,:(10001):,:(8719):);
17:04:32	Server	 TSQLRestServerDB(000CBFD0)   PUT repl/Batch SOA-Method -> 200 with outlen=17 in 559313 us

Offline

#9 2015-04-06 19:33:45

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,661
Website

Re: BatchAdd and forced ID's

Perhaps there is an issue, with the current "optimistic" implementation.

We may have to wait for the existing transaction to be released, and retry later.

An additional parameter to the BatchSend() method (or to the TSQLRestBatch instance itself), may be needed.
What do you think?

Online

#10 2015-04-06 21:51:33

alpinistbg
Member
Registered: 2014-11-12
Posts: 124

Re: BatchAdd and forced ID's

IMHO, as long the transactions in sqlite can't be nested, they (it) shall be treated as mutual resource, i.e. through synchronization mechanism, perhaps with timed wait and some error code in case of failure.
Or may be some immediate return with result code like EWOULDBLOCK will be more appropriate?

I've tried with Database.AcquireWriteMode := amBackgroundThread; hoping it will serialize the batches, but it has no effect. Trouble is that the situation persists and the program must be restarted.

Offline

#11 2015-04-07 09:27:03

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,661
Website

Re: BatchAdd and forced ID's

Could you please create a ticket, with a link to this forum thread?

Thanks!

Online

#12 2015-04-07 10:17:34

alpinistbg
Member
Registered: 2014-11-12
Posts: 124

Re: BatchAdd and forced ID's

Ticket http://synopse.info/fossil/tktview?name=c47b9ef580

Offline

#13 2015-04-07 15:28:38

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,661
Website

Re: BatchAdd and forced ID's

I've ensured that BATCH process would take place within the expected execORMWrite context.
See http://synopse.info/fossil/info/b979edb102

It should solve your issue.

But I'm still not 100% confident by the current implementation.
Server-side direct access of ORM methods (e.g. from interface-based services directly using the TSQLRestServer instance) would by-pass the whole execORM* features, so it would need special care of batch process.
I'll investigate further.

Online

#14 2015-04-10 18:24:40

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,661
Website

Re: BatchAdd and forced ID's

I've also committed http://synopse.info/fossil/info/423461d0e5

This patch includes a safer implementation: BATCH process, when run directly via EngineBatchSend() on the server side, will now explicitly wait for a transaction to be available.

Online

Pages: 1

Board footer

Powered by FluxBB