Hi, I have a requirement to lock rows after they have been selected until they have been processed. When they have been processed they will either be updated or deleted and this marks the end of the transaction. So:

select x from y - This marks the start of the transaction
Perform some business logic
update / delete y where x ... - This marks the end of the transaction

Each row in the table basically represents a message that needs to be processed and there are multiple threads that read from the table in order to process these messages. I need to make sure a row (message) is not processed by 2 threads concurrently. Rows are processed in the order that they were inserted into the table.

I can lock the row using LockMode.UPGRADE or LockMode.UPGRADE_NOWAIT but this will either cause any other thread that wants to process the row to wait indefinitely or error. What I want is for the other thread to ignore this row since it is already being processed.

Do I need some flag in the table to mark rows that are being processed? I am wary of this approach because if the application goes down then I may leave rows locked permanently.

What I want is basically LockMode.UPGRADE/LockMode.UPGRADE_NOWAIT but in such a way that these rows will be ignored by other threads (if I do "select * from y where rownum <= 1 order by id " to select the oldest row for example).

I am using Oracle if that makes any difference.

Thanks

Unless you set your isolation level to read-uncommited, having a flag won't help.

How about using a single thread to read the messages that need processing then handing them off to your pool of worked threads that do the actual updating. The dispatcher thread could keep track of the last message it read and use this to select the new messages.

Unfortunately having a single thread is not possible. I have multiple instances of this application and each instance is very high traffic, the various threads must be able to concurrently process the messages.

The flag solution is more of a manual process:

- read row from table and set 'locked by' and 'locked time' columns
- do some work
- either update row and null out the 'locked by' and 'locked time' columns so the row can be processed again or delete the row (depending on result of work above)

Using a 'locked time' I can put something in the query that ignores locks that have 'expired' which will be defined as having been locked for over x minutes. This is to prevent application crashes locking a row permanently.

I think this will work but ideally I would like the database to handle this. I would just like to use hibernate and tell oracle 'this row is locked so ignore it for other requests'. But I'm beginning to think this is not possible. It seems you can only get other requests to wait indefinitely or error since they will not ignore the locked row in their queries.

In a separate transaction from the actual work?

Is 'locked by' a user id or an id for the application instance that is processing the row?

You could add a version column to the message table (optimistic locking) so that only 1 of 2 simultaneous transactions on the same message will succeed. However, if both apps process messages from the head of the table you're likely to get many of these exceptions. If messages can be processed in any order you could randomise the selected message to avoid the majority of these. Also, this assumes all your "work" is transactional i.e. can be rolled back when optimistic locking failure is detected.