I am looking for a kind of Record Blocking,
where a query block a record in the way that a following query cannot see (select) this record.
the "select for update" is therefore not usefull then it locks for updates but allows to read (select).
To give an idea, a approach could be to have a column (status) where there is a flag (beingUsed) and in the selects you query with "where status not like 'beingUsed'".
As you probably know this is not "thread safe". If the select happends right when the flag is about to be set by another thread you can select the record in the actual thread and the flag is set right after it by the other thread.
So the idea is wrong but it explain a bit more the problem.
the status column would be only be used for the thread-problem...
thanks for the "for update" option
I also was thinking in using it...
The question is now
if I use "select ... for update nowait"
but the web application uses only one connection-configuration
(one userName, one password) for all threads.
Is the DB stopping a second "select ... for update nowait"
even if it is being made using the same connection-configuration (but from a different thread).
Or is the DB 'thinking': ok is the same user, I allow it...???