-2

Below Snapshot is current application flow.enter image description here

Current Flow

When user Logged in at these multiple deployments, then respective SMSAgent(java class) insert user info in database, SMSHelper is a java Scheduler which reads data from database in its local queue,send SMS and then update user status in database.

Issue with this flow

Now,In above scenario, Multiple SMS is getting send to Single User because database is common and both the notification helper takes contact details from database(which may be common) and send SMS to that user.

Existing Solution

Currently, solution to this problem is only available in oracle 11g where select query has for update skip locked support.

Expectation

How to achieve the same with all databases at application level and not at query level ?

Prateek
  • 12,014
  • 12
  • 60
  • 81
  • you can add an additional column that indicates sms is sent so that if onc helper class send sms it will update that column then other helper class can check that column just before sending sms – coreJavare May 28 '14 at 13:21
  • @coreJavare i think u havent read that helper updates user status in database. – Prateek May 28 '14 at 13:24
  • SMSHelper should check status of sent flag "JUST BEFORE" sending sms so it wont send other sms – coreJavare May 28 '14 at 13:27
  • @Mat Thanks, Is it fine now. – Prateek May 28 '14 at 13:27
  • @coreJavare Suppose both the helper checked status simultaneously, then in that case too, multiple sms will be sent. – Prateek May 28 '14 at 13:29
  • Row level lock will solve that issue – coreJavare May 28 '14 at 13:38
  • @coreJavare Row level locking is not good for large number of rows http://stackoverflow.com/questions/3462643/why-is-table-level-locking-better-than-row-level-locking-for-large-tables – Prateek May 28 '14 at 13:42

1 Answers1

0

First,you have to RESERVE the row by update and then do select.

Suppose u have 200 row,

so first you should do is RESERVE by some value which are unique by instance, also you could limit on no of rows updated in your query and then select the row which are reserved by your query

UPDATE TABLE_NAME SET SERVER_INSTACE_ID=UNIQUE_VAL AND ROWNUM <= RECORD_RESERVATION_LIMIT

SELECT * FROM TABLE_NAME WHERE SERVER_INSTANCE_ID=UNIQUE_VAL

Through this approach, you don't need to obtain lock on row or table.

Community
  • 1
  • 1
harsh patel
  • 39
  • 9