Transactions on Database Trigger function

Working with Data
1

Hi,

I would like to know if there is a way to implement transactions on Database trigger function?

Below code actually gets triggered if there is any modification on kafka_connect_booking collection document and that document gets inserted into booking_vinay collection after some data transformation(using aggregation). Once, the transformed document gets inserted in booking_vinay collection I need to delete that document from kafka_connect_booking collection. I have used the below code to achieve it but I think it would be better to handle this with a transaction instead of relying on .then().
So, could you please let me know how exactly I can use transactions in my code which would lock a particular document which I am processing in kafka_connect_booking collection.

Code snippet:

exports = function(changeEvent){

const pipeline = [
  {
    $project: {
      _id: 0, 
      bookingNumber: 1, 
      salesforceAccountId: 1
    }
  },
  {
     $addFields: {
      bookingNumber: changeEvent.fullDocument.bookingNumber, 
      salesforceAccountId: changeEvent.fullDocument.salesforceAccountId, 
      }
    }
  }, {
    $merge: {
      into: 'booking_vinay', 
      on: ['bookingNumber'],
      whenMatched: 'replace', 
      whenNotMatched: 'insert'
    }
  }
];

const kafkaConnectBookingCollection = context.services.get("xxxx").db("xxxx").collection("kafka_connect_booking");

return kafkaConnectBookingCollection.aggregate(pipeline).toArray().then(bookings => {
    console.log(`Successfully moved ${changeEvent.fullDocument.bookingNumber} data to booking_vinay collection.`);
    kafkaConnectBookingCollection.deleteOne({ bookingNumber: changeEvent.fullDocument.bookingNumber });
    console.log(`Successfully deleted ${changeEvent.fullDocument.bookingNumber} data from kafka_connect_booking collection.`);
    return bookings;
  })
  .catch(err => console.error(`Failed to move ${changeEvent.fullDocument.bookingNumber} data to booking_vinay collection: ${err}`));
};
1 Like
2

Hi @Vinay_Gangaraj,

Transactions are supported in Realm functions and triggers:

However, $merge stage is not supported in transactions so you will need to change your code for it.

Best
Pavel

1 Like
3

Hi @Pavel_Duchovny,

Thanks for your response.

I would like to explain a bit more about my issue. When there is a change event triggers on kafka_connect_booking collection an event gets triggered and that document I am processing and inserting into vinay_collection using aggregate and deleting that document from kafka_connect_booking collection.

Now, majore concern in this cycle is, Let’s say a record of ABCD1 gets inserted in kafka_connect_booking collection and during this event processing itself let’s say another upsert for the same document ABCD1 happens on kafka_connect_booking collection which in-turn triggers another event but the first event would have completed and it would have deleted record of ABCD1 from kafka_connect_booking and now the second event for the same record processes it comes back and tries to delete the same data which could result with an exception as there is no record of ABCD1 in kafka_connect_booking collection to delete. So, I wanted to lock the ABCD1 document of kafka_connect_booking collection when there is a insert/update so that till the first event processing is completed second event will be in a queue.

I hope I was able to make you understand my issue :slight_smile: If we can’t use triggers is there anyway where I can lock the document in kafka_connect_booking till the event processing is completed for that particular document.

Thanks,
Vinay

4

Hi @Vinay_Gangaraj,

Perhaps you should update a status field for this record inside the triggers using transaction and then do the logic of your trigger.

For example the first transaction command is to update the status field to in-progress and just before committing change it to done.

This will make any other operations outside of the transaction to wait for its completion/abort:

If a transaction is in progress and has taken a lock to modify a document, when a write outside the transaction tries to modify the same document, the write waits until the transaction ends.

Will that work for you?

Best
Pavel