Record "Blocking" ?

[ter055]

Hello,

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.

Any ideas?

I´m using 9i and programming in Java.

Thank you

[ales]

Hello,
"select for update" IS useful, IMHO.
If a session locks a record with "select for update", another session using "select for update nowait" on the same record, gets the ORA-00054 error.

[slimdave]

Are you talking about the problem of updating a record here, or were you wanting to use the status of that record itself as the locking mechanism for another process?

[ter055]

ok
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...???

Thanks
Ter

[slimdave]

Or is the DB 'thinking': ok is the same user, I allow it...???

no, it wouldn't.

[ter055]

ok

good to know,
so I can use it...

You know why it is so?

Thanks, regards
Ter

[ter055]

ok

it´s working with
"select ... for update nowait"

my problem now is that the lock to the record stays there even after the resulset is being closed!!

is there a configuration issue to check???

Thanks, regards,
Tero