Here's my problem. I've several identical programs running individually. Each read a row in the database (according to some criteria), perform a number of procedures and lastly upgrading that row. Therefore I wish to make certain that the row began to become processed by a credit card applicatoin, won't be processed by another. Quite simply, i would like that the application to choose the following available row. How do i implement this?

I try'it different methods using "choose ... for update", MVCC, different kind of transaction isolation, but no luck for moment. Maybe I am wrong somewhere.

A typical solution is by using a 'state' column:

  • not processed
  • 1 processing
  • 2 processed

When processing the row, set the condition to at least one. Once it's processed, place it to two. This works in most databases.

If you wish to safeguard against rows being stuck within the 'processing' condition (for instance since the session/connection was closed), then you may give a column 'processing_session' and grow it using the current session (function SESSION_ID()) when processing. To discover if your session continues to be alive, use table INFORMATION_SCHEMA.SESSIONS.