#1 2018-02-15 12:31:32

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

ESQLite3Exception doesn't rollback a batch

Hi there!

I am sending a batch to a TSQLRestServerDB:

  Batch := TSQLRestBatch.Create(Client, Nil, MaxInt);
  ...
  Batch.Add(Barcode, True,True); // several times
  ...
  Client.BatchSend(Batch);

The corresponding batch content (JSON) is as follows:

'["automaticTransactionPerRow",2147483647,
"POST@Barcodes",{"RowID":3625024892,"BarcodeNumber":1000111,"Barcode":"111","Range":"2341","PriceID":615024892,"UserID":580044892,"Active":1,"DateUpload":135431771610},
"POST@Barcodes",{"RowID":3625034892,"BarcodeNumber":1000112,"Barcode":"222","Range":"2341","PriceID":615024892,"UserID":580044892,"Active":1,"DateUpload":135431771610},
"POST@Barcodes",{"RowID":3625044892,"BarcodeNumber":1000113,"Barcode":"333","Range":"2341","PriceID":615024892,"UserID":580044892,"Active":1,"DateUpload":135431771610},
"POST@Barcodes",{"RowID":3625054892,"BarcodeNumber":1000114,"Barcode":"333","Range":"2341","PriceID":615024892,"UserID":580044892,"Active":1,"DateUpload":135431771610}]'

The table 'Barcodes' have an unique index on the 'Barcode' field. Note that on the 4-th post the field 'Barcode' have the same value as the 3-rd post. The sqlite3 engine throws an exception (as expected):

#0 fpc_raiseexception at :0
#1 SQLITE3_CHECK(77908916, 19, 0xd6e5cc 'Step') at C:\AI\IDE\fpcupdeluxe\ccr\mORMotFPC\SynSQLite3.pas:5330
#2 TSQLREQUEST__STEP(<error reading variable>) at C:\AI\IDE\fpcupdeluxe\ccr\mORMotFPC\SynSQLite3.pas:5249
#3 TSQLRESTSERVERDB__INTERNALEXECUTE(0x5fbaa1c 'INSERT INTO Barcodes (RowID,BarcodeNumber,Barcode,Range,PriceID,UserID,Active,DateUpload) VALUES (:(3625054892):,:(1000114):,:(''333''):,:(''2341''):,:(615024892):,:(580044892):,:(1):,:(135431771610):);', true, 0x0, 0x0, 0x0, 0x0, 0x0, <error reading variable>) at C:\AI\IDE\fpcupdeluxe\ccr\mORMotFPC\SQLite3\mORMotSQLite3.pas:1211
#4 TSQLRESTSERVERDB__INTERNALBATCHSTOP(<error reading variable>) at C:\AI\IDE\fpcupdeluxe\ccr\mORMotFPC\SQLite3\mORMotSQLite3.pas:1958
#5 TSQLSERVERMIST__INTERNALBATCHSTOP(<error reading variable>) at C:\AI\_\MIST\zoo\Model\DataModel.pas:2107
#6 TSQLRESTSERVER__ENGINEBATCHSEND(<incomplete type>, 0x5f3be64 '["automaticTransactionPerRow",2147483647,"POST@Barcodes', 0x5f3c3a0, 0, <error reading variable>) at C:\AI\IDE\fpcupdeluxe\ccr\mORMotFPC\SQLite3\mORMot.pas:42945
#7 TSQLRESTSERVER__BATCH(0x5e500a8, <error reading variable>) at C:\AI\IDE\fpcupdeluxe\ccr\mORMotFPC\SQLite3\mORMot.pas:41717
#8 TSQLRESTSERVERURICONTEXT__EXECUTESOABYMETHOD(<error reading variable>) at C:\AI\IDE\fpcupdeluxe\ccr\mORMotFPC\SQLite3\mORMot.pas:39736
#9 TSQLRESTSERVERURICONTEXT__EXECUTEORMWRITE(<error reading variable>) at C:\AI\IDE\fpcupdeluxe\ccr\mORMotFPC\SQLite3\mORMot.pas:40204
#10 TSQLRESTSERVERURICONTEXT__EXECUTECOMMAND(<error reading variable>) at C:\AI\IDE\fpcupdeluxe\ccr\mORMotFPC\SQLite3\mORMot.pas:39620
#11 TSQLRESTSERVER__URI({URL = 0x4918934 'client/Batch', METHOD = 0x4921f5c 'PUT', INHEAD = 0xd41838 'RemoteIP: 127.0.0.1', INBODY = 0x5f3be64 '["automaticTransactionPerRow",2147483647,"POST@Barcodes', OUTHEAD = 0x0, OUTBODY = 0x0, OUTSTATUS = 400, OUTINTERNALSTATE = 73, RESTACCESSRIGHTS = 0xbce280, LOWLEVELCONNECTIONID = 404, LOWLEVELFLAGS = [LLFSECURED]}, <error reading variable>) at C:\AI\IDE\fpcupdeluxe\ccr\mORMotFPC\SQLite3\mORMot.pas:41306
#12 TSQLRESTSERVERNAMEDPIPERESPONSE__INTERNALEXECUTE(<error reading variable>) at C:\AI\IDE\fpcupdeluxe\ccr\mORMotFPC\SQLite3\mORMot.pas:43552
#13 TSQLRESTTHREAD__EXECUTE(<error reading variable>) at C:\AI\IDE\fpcupdeluxe\ccr\mORMotFPC\SQLite3\mORMot.pas:36529
#14 CLASSES$_$TSTRINGLIST_$__$$_SORT at :0
#15 ?? at :0

But this doesn't result in (batch) transaction rollback, which IMHO is a serious flaw, instead it commits (!?) the first 3 inserts. I am using a batch intentionally for the enclosing transaction. At this point in my program I'm making a data import and I want to mass-insert several thousands of records but without risking inconsistency in the cited column. How can I ensure that the whole batch will be commited/rollback'd at once? Do I missing something?

Using: Lazarus 1.7 i386-win32-win32/win64, FPC 3.1.1, SynopseCommit.inc='1.18.3977'

Best regards,

Offline

#2 2018-02-19 11:19:26

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

Re: ESQLite3Exception doesn't rollback a batch

@ab
Do you consider that issue as a negligible one? I believe it shows the broken 'atomicity' of the batch processing. Given the excerpt from the mORMot SAD Document, "12.3.3. Unit Of Work pattern" :

The ability to handle several TSQLRestBatch classes in the same time will allow to implement the Unit Of Work pattern. It can be used to maintain a list of objects affected by a business transaction and coordinates the writing out of changes and the resolution of concurrency problems, especially in a complex SOA application with a huge number of connected clients.

In a way, you can think of the Unit of Work as a place to dump all transaction-handling code.
The responsibilities of the Unit of Work are to:

- Manage transactions;
- Order the database inserts, deletes, and updates;
- Prevent concurrency problems;
- Group requests to maximize the database performance.
The value of using a Unit of Work pattern is to free the rest of your code from these concerns so that you can otherwise concentrate on business logic.

and some Web definition of UOW:

The Unit of Work pattern is used to group one or more operations (usually database operations) into a single transaction or “unit of work”, so that all operations either pass or fail as one.

Then I think TSQLRestBatch (with its partial commit) is not what Unit-Of-Work is expected to be. It is just a way to speed up database operations, at least for SQLite, not quite sure for other RDBMS.

I'll appreciate at least some feedback on that issue. Thank you for your time and attention!

Kind regards,

Offline

#3 2018-02-19 17:57:12

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 13,167
Website

Re: ESQLite3Exception doesn't rollback a batch

The transactions are per table - see TSQLRestServer.EngineBatchSend.

So in your case, RunTableTransactions[] is set, and then in the "except on E: Exception do ...." block at the end of the method, there is a rollback on the table:

            RunTableTransactions[i].RollBack(CONST_AUTHENTICATION_NOT_USED);

In case of a full SQLite3 storage, RunTableTransactions[] is set to the main TSQLRestServer instance, i.e. RunningRest=self and RunMainTransaction=true.

I wonder why it is not the case on your side...
Could you try with a newer version (yours is somewhat old)?

Offline

#4 2018-02-20 13:42:07

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

Re: ESQLite3Exception doesn't rollback a batch

The exception got caught in except clause in mORMotSQLite3.pas, line 1212. Then TSQLRestServer.EngineBatchSend() continues execution normally and even processes the following "POST@..." records before making commit. The except clause in the file mORMot.pas, line 43800 has no chance, since there is no re-raise for the exception.

Here is (almost) the same stack dump with SynopseCommit.inc='1.18.4314':

#0 fpc_raiseexception at :0
#1 SQLITE3_CHECK(78104964, 19, 0xd85e84 'Step') at C:\AI\IDE\fpcupdeluxe\ccr\mORMotFPC\SynSQLite3.pas:5345
#2 TSQLREQUEST__STEP(<error reading variable>) at C:\AI\IDE\fpcupdeluxe\ccr\mORMotFPC\SynSQLite3.pas:5264
#3 TSQLRESTSERVERDB__INTERNALEXECUTE(0x63b1084 'INSERT INTO Barcodes (RowID,BarcodeNumber,Barcode,Range,PriceID,UserID,Active,DateUpload) VALUES (:(3628054892):,:(1000114):,:(''333''):,:(''123''):,:(615024892):,:(580044892):,:(1):,:(135432434243):);', true, 0x0, 0x0, 0x0, 0x0, 0x0, <error reading variable>) at C:\AI\IDE\fpcupdeluxe\ccr\mORMotFPC\SQLite3\mORMotSQLite3.pas:1190
#4 TSQLRESTSERVERDB__INTERNALBATCHSTOP(<error reading variable>) at C:\AI\IDE\fpcupdeluxe\ccr\mORMotFPC\SQLite3\mORMotSQLite3.pas:1933
#5 TSQLSERVERMIST__INTERNALBATCHSTOP(<error reading variable>) at C:\AI\_\MIST\zoo\Model\DataModel.pas:2107
#6 TSQLRESTSERVER__ENGINEBATCHSEND(<incomplete type>, 0x634b604 '["automaticTransactionPerRow",2147483647,"POST@Barcodes', 0x634bb40, 0, <error reading variable>) at C:\AI\IDE\fpcupdeluxe\ccr\mORMotFPC\SQLite3\mORMot.pas:43734
#7 TSQLRESTSERVER__BATCH(0x62600a8, <error reading variable>) at C:\AI\IDE\fpcupdeluxe\ccr\mORMotFPC\SQLite3\mORMot.pas:42499
#8 TSQLRESTSERVERURICONTEXT__EXECUTESOABYMETHOD(<error reading variable>) at C:\AI\IDE\fpcupdeluxe\ccr\mORMotFPC\SQLite3\mORMot.pas:40513
#9 TSQLRESTSERVERURICONTEXT__EXECUTEORMWRITE(<error reading variable>) at C:\AI\IDE\fpcupdeluxe\ccr\mORMotFPC\SQLite3\mORMot.pas:40981
#10 TSQLRESTSERVERURICONTEXT__EXECUTECOMMAND(<error reading variable>) at C:\AI\IDE\fpcupdeluxe\ccr\mORMotFPC\SQLite3\mORMot.pas:40397
#11 TSQLRESTSERVER__URI({URL = 0x4948ab4 'client/Batch', METHOD = 0x495225c 'PUT', INHEAD = 0xd548c0 'RemoteIP: 127.0.0.1', INBODY = 0x634b604 '["automaticTransactionPerRow",2147483647,"POST@Barcodes', OUTHEAD = 0x0, OUTBODY = 0x0, OUTSTATUS = 400, OUTINTERNALSTATE = 73, RESTACCESSRIGHTS = 0xbe10a0, LOWLEVELCONNECTIONID = 400, LOWLEVELFLAGS = [LLFSECURED]}, <error reading variable>) at C:\AI\IDE\fpcupdeluxe\ccr\mORMotFPC\SQLite3\mORMot.pas:42088
#12 TSQLRESTSERVERNAMEDPIPERESPONSE__INTERNALEXECUTE(<error reading variable>) at C:\AI\IDE\fpcupdeluxe\ccr\mORMotFPC\SQLite3\mORMot.pas:44341
#13 TSQLRESTTHREAD__EXECUTE(<error reading variable>) at C:\AI\IDE\fpcupdeluxe\ccr\mORMotFPC\SQLite3\mORMot.pas:37236
#14 CLASSES$_$TSTRINGLIST_$__$$_SORT at :0
#15 ?? at :0

The reason I didn't specify a table in TSQLRestBatch.Create(Client, Nil, MaxInt), is the batch actually contains 'POST@...' in several tables. I'have simplified the example to post it in the forum.

Regards,

Offline

#5 2018-02-20 17:27:32

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 13,167
Website

Re: ESQLite3Exception doesn't rollback a batch

This is indeed a problem.
I've just introduced boRollbackOnError in TSQLRestBatchOptions.
See https://synopse.info/fossil/info/9ffe23dac2

Offline

#6 2018-02-20 18:20:14

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

Re: ESQLite3Exception doesn't rollback a batch

Not quite sure it helps. The Results[Count] will actually hold an ID from EngineAdd() when mPOST. Also, as shown in the stack trace, using different tables will call InternalBatchStart()/InternalBatchStop() and the exception is namely into InternalExecute() (#3) called by InternalBatchStop() (#4):

#1 SQLITE3_CHECK(78104964, 19, 0xd85e84 'Step') at C:\AI\IDE\fpcupdeluxe\ccr\mORMotFPC\SynSQLite3.pas:5345
#2 TSQLREQUEST__STEP(<error reading variable>) at C:\AI\IDE\fpcupdeluxe\ccr\mORMotFPC\SynSQLite3.pas:5264
#3 TSQLRESTSERVERDB__INTERNALEXECUTE(0x63b1084 'INSERT INTO Barcodes (RowID,BarcodeNumber,Barcode,Range,PriceID,UserID,Active,DateUpload) VALUES (:(3628054892):,:(1000114):,:(''333''):,:(''123''):,:(615024892):,:(580044892):,:(1):,:(135432434243):);', true, 0x0, 0x0, 0x0, 0x0, 0x0, <error reading variable>) at C:\AI\IDE\fpcupdeluxe\ccr\mORMotFPC\SQLite3\mORMotSQLite3.pas:1190
#4 TSQLRESTSERVERDB__INTERNALBATCHSTOP(<error reading variable>) at C:\AI\IDE\fpcupdeluxe\ccr\mORMotFPC\SQLite3\mORMotSQLite3.pas:1933
...

InternalBatchStop() does not return anything after the exception caught into InternalExecute(). It has no impact on Results[].

Offline

#7 2018-02-20 22:52:10

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 13,167
Website

Re: ESQLite3Exception doesn't rollback a batch

You are right: it will help only if EngineAdd() is a single TSQLRestStorage class, not for a TSQLRestServerDB.
With a fix, it becomes https://synopse.info/fossil/info/f927a214c3

There was no exception in TSQLRestServerDB.InternalBatchStop in line 1933.
This part of the code only occurs if there is a single POST/INSERT pending.
It will now raise an exception via https://synopse.info/fossil/info/4e42b0c81b

For multiple POST/INSERT, I guess there is already a ESQLite3Exception raised in the loop.

BTW TSQLRestStorageExternal.InternalBatchStop - for external DB - and TSQLRestStorageMongoDB.InternalBatchStop - for MongoDB - have a similar behavior.

Is it OK now?

Offline

#8 2018-02-21 10:34:05

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

Re: ESQLite3Exception doesn't rollback a batch

ab wrote:

There was no exception in TSQLRestServerDB.InternalBatchStop in line 1933.
This part of the code only occurs if there is a single POST/INSERT pending.
It will now raise an exception via https://synopse.info/fossil/info/4e42b0c81b

For multiple POST/INSERT, I guess there is already a ESQLite3Exception raised in the loop.

Actually my code goes like this:

  Batch := TSQLRestBatch.Create(Client, Nil, MaxInt);
  ...
  for {several times} do begin
    Batch.Add(Barcode, True,True); 
    Batch.Add(Journal, True,True); 
  end;
  ...
  Client.BatchSend(Batch);

Which effectively calls InternalBatchStart()/InternalBatchStop() pair for every single row in the batch (Didn't know then about 2-nd level batching). So the  ESQLite3Exception fires exactly in call to InternalExecute() at TSQLRestServerDB.InternalBatchStop, line 1933. InternalExecute() catches it and (previously) it went unnoticed by InternalBatchStop(). Now it re-raises it, though under a different name, and this is an improvement.

ab wrote:

Is it OK now?

It is getting better smile

Now I'm concerned about the semantics of the boRollbackOnError batch option. I have tried my code and now it correctly rollbacks the whole batch, but I didn't include boRollbackOnError into the batch options!

That happened because the call of InternalBatchStop() at line 43738 raises EORMBatchException way before checking the option at line 43790 and no matter the option was included or not - gets caught at line 43810 and everything roll-backed.

May be an additional try ... catch will be needed with a quick logic to mute the EORMBatchException in case the boRollbackOnError was not included.

PS: Noticed that PerformAutomaticCommit at line 43714 also calls InternalBatchStop with possibly the same effect.

PPS: About the Results[] array and multiple POSTs in a same table:

Browsing through TSQLRestServerDB methods, InternalBatchStart/InternalBatchStop pair and enclosed MainEngineAdd I saw that all INSERTs are merged in one single INSERT in form:

  INSERT INTO table (col1,col2,...) 
  VALUES
    (v1, v2,...),
    (v1, v2,...),
    ...

Taking in mind the MainEngineAdd() will return the ID from the Batch JSON (which was generated or supplied, it doesn't matter), the Results[count] will be assigned to the returned value and OK := true. With ESQLite3Exception raised at the time of  InternalBatchStop, the whole statement will fail, i.e. no rows will be inserted from the time of the last InternalBatchStart but the Results[n..n+m] will hold some 'good looking' IDs, which in fact are not IDs of successfully inserted rows. Thus, the caller can not rely on contents of the Results[].

Regards,

Last edited by alpinistbg (2018-02-21 14:42:33)

Offline

Board footer

Powered by FluxBB