Foreign key constraint error using transactions

General
1

Application ID

77174A3A-8AD8-F997-FF5F-8CE8CB660D00

Expected Behavior

Create an object and then relate this object to another existing one from another table using transactions.

Actual Behavior

When I try to create an object and then relate this object to another existing one from another table using transactions sometimes I get this error:

TransactionOperationError: com.backendless.services.persistence.internal.SqlDatabaseException: Cannot add or update a child row: a foreign key constraint fails (77174A3A-8AD8-F997-FF5F-8CE8CB660D00.GiftOrders.Users.doctor, CONSTRAINT 92CC31CF-0CDF-11FE-FF40-F641C9BDF500_ibfk_1 FOREIGN KEY (rel_from) REFERENCES udt.GiftOrders (objectId) ON ) at _class.setResult (/usr/local/lib/node_modules/backendless-coderunner/node_modules/backendless/lib/unit-of-work/index.js:227:24) at _class._callee$ (/usr/local/lib/node_modules/backendless-coderunner/node_modules/backendless/lib/unit-of-work/index.js:194:55) at tryCatch (/usr/local/lib/node_modules/backendless-coderunner/node_modules/regenerator-runtime/runtime.js:63:40) at Generator.invoke [as _invoke] (/usr/local/lib/node_modules/backendless-coderunner/node_modules/regenerator-runtime/runtime.js:293:22) at Generator.next (/usr/local/lib/node_modules/backendless-coderunner/node_modules/regenerator-runtime/runtime.js:118:21) at asyncGeneratorStep (/usr/local/lib/node_modules/backendless-coderunner/node_modules/@babel/runtime/helpers/asyncToGenerator.js:3:24) at _next (/usr/local/lib/node_modules/backendless-coderunner/node_modules/@babel/runtime/helpers/asyncToGenerator.js:25:9) at processTicksAndRejections (internal/process/task_queues.js:97:5) { operation: OpResult { uow: _class { app: [Backendless], payload: [Object], usedOpIds: [Object] }, operationType: ‘SET_RELATION’, table: ‘GiftOrders’, payload: { parentObject: [OpResult], relationColumn: ‘doctor’, unconditional: [Array] }, opResultId: ‘set_relationGiftOrders1’, result: null, error: [Circular] } }

The steps I follow to do this are:

  1. const unitOfWork = new Backendless.UnitOfWork();
  2. unitOfWork.setIsolationLevel(Backendless.UnitOfWork.IsolationLevelEnum.READ_UNCOMMITTED);
  3. const persistedResult = unitOfWork.create(‘MyTable1’, {code});
  4. unitOfWork.setRelation(persistedGiftOrdersResult, ‘MyTable2’, [{objectId : correctObjecId}]);
  5. unitOfWork.execute().then(result => {
    return result.isSuccess();
    })

I send the correct objectId related but in my log, I notice a different Id.

2

Hello @Valerio_Galiote

What SDK are you using?

3

Hello @Volodymyr_Ialovyi, I am using SDK for JavaScript

4

Hi Valerio,

Where does “persistedGiftOrdersResult” value come from in your code?

Regards,
Mark

5

Hello Mark,
persistedResult is persistedGiftOrdersResult, I had a mistake writing that snippet of code:

  1. const unitOfWork = new Backendless.UnitOfWork();
  2. unitOfWork.setIsolationLevel(Backendless.UnitOfWork.IsolationLevelEnum.READ_UNCOMMITTED);
  3. const persistedGiftOrdersResult = unitOfWork.create(‘GiftOrders’, {code});
  4. unitOfWork.setRelation(persistedGiftOrdersResult, ‘doctor’, [{objectId : doctorObjectId}]);
  5. unitOfWork.execute().then(result => {
    return result.isSuccess();
    })
6

Hello @Valerio_Galiote

Unfortunately, I was unable to reproduce this problem of yours. Have you encountered such an error recently?
My schema: Monosnap
My code:

const unitOfWork = new Backendless.UnitOfWork();

unitOfWork.setIsolationLevel(Backendless.UnitOfWork.IsolationLevelEnum.READ_UNCOMMITTED);

const persistedGiftOrdersResult = unitOfWork.create('GiftOrders', {'name':'name'});

unitOfWork.setRelation(persistedGiftOrdersResult, 'doctor', [{'objectId' : 'doctorObjectId'}]);
unitOfWork.execute().then(result => {
                    return result.isSuccess();
                    })

Does the parent table have other relations?
Can I try to reproduce the problem in your application?

Regards,
Inna

7

Hello @Inna_Shkolnaya,
Yes, I have experienced this problem sometimes recently.
GiftOrders table has another relation with another table named Products, and of course, please, you can try to reproduce the problem in my app.

8

Hello @Valerio_Galiote

Are you getting this error when you call your assignProductToDoctor method from the AdminService service?

I see you are calling the code along with unitOfWork.setRelation in a loop.
Also you are using READ_UNCOMMITTED. This is the lowest level of isolation, providing the minimum amount of data isolation - Transaction Isolation. READ UNCOMMITTED is a “dirty read”. This means that the SELECT could be reading uncommitted data that would fail the foreign key constraints during the INSERT.

My guess is that you are doing this in a loop and using READ_UNCOMMITTED is the most likely cause of the problem.

For now, I can suggest that you try using a different isolation level. It is also possible that you can somehow change your code so as not to use a loop.

9

Hello @Volodymyr_Ialovyi,
Yes, that is the problem, and also I tried with different isolation levels, including SERIALIZABLE; as I read is the highest level of data isolation and I get the same error.

10

Hello @Valerio_Galiote

Perhaps you can change your approach without using a loop?

Please also check if the problem is reproducible if you add a small delay in the loop.

11

Thank you @Volodymyr_Ialovyi
I will try adding a small delay in the loop