0

Hi I have a stored procedure, which has a SELECT Query (on a table)
then it updates the same table's modified timestamp so that the same records
will not be picked up again.

When two application nodes try to execute this stored procedure simultaneously
,is it possible that both nodes will get the same records (due to the fact that 1st update was not executed before 2nd read ) ?

Any help would be much appreciated.

TheGameiswar
  • 27,855
  • 8
  • 56
  • 94
Pawandeep
  • 75
  • 9
  • Wrap the whole select+update into a transaction and this won't happen. On the other hand, if the update isn't *heavy* in terms of the rows updated and calculations, you shouldn't have any noticable waits between the "simultaneous executions" of the stored procedure. Take a look at this great answer: http://stackoverflow.com/a/1195923/6492765 – MK_ Mar 13 '17 at 08:56
  • @MK_ i think it can still happen. the transaction initiated by 1st node may yet not be commited, and 2nd node gets the same records. – Pawandeep Mar 13 '17 at 09:01
  • Should've mentioned the transaction isolation level in my comment, not just link to the answer about it. Anyways, read up the linked answer. You need to use `SET TRANSACTION ISOLATION LEVEL SERIALIZABLE` within the stored procedure. Beware though that when you set it within the stored procedure, it's scoped **only** to that procedure. – MK_ Mar 13 '17 at 09:06
  • @MK_ Thank you very much, for the help – Pawandeep Mar 13 '17 at 09:14
  • No problem. If this answers your whole question, want me to turn my comments into a more elaborate answer below? – MK_ Mar 13 '17 at 09:21

0 Answers0