-1

I am working with microservices architecture using consul for service discovery and ECS for maintaining docker containers

Now I am Developing a micro service which will read record from AWS SQS and after some validation it will be inserted into SQL.

Problem For scalability suppose we launch two containers A and B say and if both A and B tries to update same record on SQL then how can I manage isolation and avoid race condition

One solution not sure I think can be to have some tokens on Elasticache aws service

However would appreciate if somebody can share his proven solution to work on this kind of use cases

OneCricketeer
  • 179,855
  • 19
  • 132
  • 245
Karn_way
  • 1,005
  • 3
  • 19
  • 42
  • How about optimistic locking? – Gerry Mantha Dec 29 '17 at 15:46
  • Any ACID compliant SQL RDBMS should be able to handle the current updates with no issues. (A in ACID is for "atomicity"). each of the updates will succeed or fail completely; the entire row will updated or not and the DBMS schema constraints will be enforced throughout each transaction. The question then becomes whether your updates are serializable or atomic. In other words you can update a number by adding something to the column in the UPDATE statement (that's atomic/safe) but doing a SELECT, calculating a new value and setting in a sep. statement would be bad. – Jim Dennis Jan 02 '18 at 06:12

1 Answers1

1

I assume you mean SQLServer managed on AWS RDS by saying SQL, and it is not different from any other SQLServer instance. And your problem is not related to microservices or AWS in any way. If you access a same data source from multiple different instances a race condition will occur. There are 2 different ways to deal with this problem in SQL Server:

  • Pessimistic Locking
  • Optimistic Locking

You can choose one of the two options depending on your scenario. More information here

In a pessimistic lock you lock the row when you read it via SELECT command. You should use READ COMMITTED isolation level or explicitly specify a LOCK hint in your query.

For optimistic locking, you can define a column in the table, which will be changed every time you update a column:

UPDATE RaceTable SET UpdatedOn = @currentDate WHERE Id = @id AND UpdatedOn = @lastUpdateDate

More info about concurrency control in SQLServer:

Despite all, since you referred microservices architecture, I'd suggest you create separate DB's for each one of your services and communicate your services through API calls (preferably REST). More reading on this: https://plainoldobjects.com/2015/09/02/does-each-microservice-really-need-its-own-database-2/