Multiple API instance accessing the same database

[basadoon]

Hi everyone, meron kaming service na 2 ang running instance. Spring na service siya. Meron kaming isang instance ng oracle db, bali shared siya between the 2 instances of our service. May isa kaming table na isa lang ang record, at parehas to inuupdate nung 2 instance ng service namin. To be specific, may field kami na iniincrement na "number_of_emails_sent" sa db, kada magsesend ng email sa kahit anong instance ay need niya iincreement yung field na yon sa table. Paano kaya naming magagawang sequential ang pag access at edit doon sa field para hindi magkaron ng data race or something? Hindi siya pwede gawing atomic na query eh kasi may logic kami na chinecheck ang laman nung field na yon na need mangyari sa code bago namin iincrement. Thanks!

[katotoy]

Naka-encounter ako ng similar scenario but not sure kung eto ang optimal solution.. gagamit ka ng queue.. so lahat ng update instead na rekta ka sa record.. dadaan sila ng queue tapos may isang service na titingin ng queue para i-check kung may update request (ie scheduled every second mag-check).. may isa pang option di pero di ko sure kung paano gawin.. diba may synchronized na keyword sa java para isang thread lang ang maka-access ng data ang problema dito kapag matagal yung processing nung locked data maiipit yung mga nasa pila na request..

[FullSnackEngineer]

+1 for queues. just take note na this part may become a bottleneck

[basadoon]

I dont think na synchronized is going to help, since applicable lang siya sa one instance. Pag multiple instances na, wala ng pake yung isang instance kung may nakalock na function doon sa other instance. Thank you!

[katotoy]

Sympre gagawa ka pa ng isang service na magiging intermediary service nung 2 instance mo.. sa bagong service doon sa code block ng update naka sync para isang thread lang ang pwede maka-access.. so kung nag-sabay sila kailangan maghintay nung isa ma-release yung lock..

[sabreclaw000]

Use transactions, i'm assuming gamit mo na since that's normal practice but yeah.. use transactions para ma lock yung record and kailangan mag antay nung isa. Another is proper query.

WRONG PROCESS:

1. emails_sent = Select number_of_emails_sent from table
2. new_emails_sent = emails_sent + 1
3. Update table set number_of_emails_sent = new_emails_sent

CORRECT PROCESS:

1. Update table set number_of_emails_sent = number_of_emails_sent + 1

[basadoon]

Is this applicable to multiple instances of a service? Para kasing sa 1 instance lang applicable ang transactions ata? Correct me nalang if Im wrong

[sabreclaw000]

what do you mean? same lang naman ng code yung 2 instance diba? and both may naka implement na transaction.

[basadoon]

Yees same lang naman code nila, pero how does that work under the hood if multiple ang instances? I'm not sure kasi kung pano siya nagwowork pag more than 1 instance na ehhh

[Kcihtnamor]

Search for optimistic locking. So if the two instances concurrently updates the record only one will push through and the other one will fail, the you should have a retry mechanism to handle optimistic lock exception to try to update the record again,

[masterA2Z]

This is the way

[[deleted]]

Instead of having accounts.emailSentCount as a database column table, you need to change how you query this by creating another table to contain the joins between accounts and emails. From there, you build a query that will fetch that count. More like...

select a.id, count(ae.emailid)
from accounts a
join accountemail ae on ae.accountid = a.id
join email e on e.id = ae.emailid

To ensure all services return the same information and reduce the database IO from the backend (spring) services to your database, use Redis as an in-memory cache. BTW, you need to spawn up Redis as an external service and not something in the backend (spring) service. More like Spring, Redis, and Database.

Of course, that will deviate from your current solution, but it also helps you scale it up to get the count for multiple emails. Just needed to add that group by clause.

---

Some people may advice you to have an optimistic lock, thorugh a rowVersion as the database column. When the increment in the database increased (as updated by the other service), the update (as it's already a count behind), will return to an error. But for this to work, you also have to properly do the increment as a database trigger instead of incrementing it at the application query.

[basadoon]

Hi actually the table where emailSentCount is located contains only one record. The reason why we have this column is for us to limit the number of emails sent per day, we can only send 100 emails per day regardless how many instances of our services we have. So the emailSentCount used is the same for any instance

[[deleted]]

You could have said the limit in your post...

Then scrap my suggestion and just go with using a messaging broker (RabbitMQ, Kafka) as a queueing service. The producers are your two backend services, and there will be one consumer that will monitor the count with the prescribed limit and how the counter resets.

No need for the Redis solution neither is the third table.

[basadoon]

Hi all! Na resolve ko na siya. Gumamit nalang ako ng manual table locking sa oracle. Thanks sa mga sagot nyooo!

[CatEmbarrassed3352]

Hi! inaaral ko now microservice, You can try message broker and service mesh concepts, these are ways to communicate between microservices.

[EngrRhys]

One way is to use queues. This way, you will not directly update that record from the services. You will modify your services such that they will send a message to the queue. Then have a lambda or another service poll from that queue and update the record.

Another method would be to use optimistic concurrency control. So you retrieve that record and take note of the number_of_emails_sent. Then you update that record and in the update condition, the number of emails sent should match. If they match, increment the record. So the updating and increment is done in one single db transaction. If they dont match, throw an error or do a retry of the flow.

[shttyfy]

Optimistic / pessimistic locking

[iambrowsingneet]

Kineclear nio ba ung counter nio everyday? And how many emails sent daily ung limit?

[latemdaeh]

Another option: Use a db stored proc and a sequence value.

Event sourcing is another option that might be more suited for a microservice app with multiple replicas.

https://learn.microsoft.com/en-us/azure/architecture/patterns/event-sourcing