1

I have several identical applications running independently. Each read a row from the database (based on some criteria), perform a series of operations and finally updating that row. Therefore I want to make sure that a row started to be processed by an application, will not be processed by another. In other words, i want that an application to select the next available row. How can I implement this?

I try'it different strategies using "select ... for update", MVCC, different type of transaction isolation, but no luck for moment.

Cœur
  • 37,241
  • 25
  • 195
  • 267
fv2005
  • 29
  • 2

1 Answers1

1

A common solution is to use a 'state' column:

  • 0 not processed
  • 1 processing
  • 2 processed

When processing the row, set the state to 1. Once it's processed, set it to 2. This will work in all databases.

If you want to protect against rows being stuck in the 'processing' state (for example because the session/connection was closed), then you could add a column 'processing_session' and fill it with the current session (function SESSION_ID()) when processing. To find out if a session is still alive, use table INFORMATION_SCHEMA.SESSIONS.

Thomas Mueller
  • 48,905
  • 14
  • 116
  • 132