Synchronization problems with between WRITER and READER RDS servers

Viewed 90

I have a basic structure on AWS that has an Aurora MySQL cluster and two servers, a WRITER (db.t2.medium) and a READER (db.r5.large).

I have an application in NodeJS that runs the following routine:

  • Insert a row in the database using WRITER server
  • Search this line ID in the table using READER server
  • Insert relational information into other tables using the generated ID using WRITER server

In terms of code its structured like this (considering that db.writer is an instance of knex that executes queries on the WRITER server and db.reader is the instance that executes on the READER server):

let theNewId = await db.writer.raw("INSERT INTO users (`name`,`email`) VALUES ('John Doe','mail@contoso.org')").catch(err => { console.log(err)}).then(async R=>{
      return await db.reader.raw("SELECT * FROM users WHERE `email`='mail@contoso.org'").catch(err => { console.log(err)}).then( async result=>{
         let theId = result[0].id;
         await db.writer.raw(""INSERT INTO users_emails (`user_id`,`email`) VALUES ('"+theId+"','mail@contoso.org')"");
         return theId;
      });
}); 

Note: I'm not executing RAW queryes, I'm just using it as example.

The problem I am having in all the similar functionalities of the code: the reader, even though it is running inside the result of the insert function, doesn't find the line that, when I will check, was inserted by WRITER correctly.

Is there any type of configuration or best programming practice that can be done so that this asynchronism does not occur in this type of situation?

1 Answers

Just to finish this question: after a lot of research I discovered that there is an asynchrony between the reading and writing instances that forces me to use, in these specific cases, the same write server to perform the searches.

Related