Re: Login In/out Triggers, implmenting NOW...

A copy of this was sent to Nnoor_at_cris.com (NNOOR) (if that email address didn't require changing) On 25 Nov 1998 10:27:14 PST, you wrote:

>
>Following is a brief description of just one of the scenerios why
>I need the login in/out triggers.
>
>Oracle has excellent locking mechanisms built in but sometimes you
>want the additional functionality which can only be achieved thru
>semaphore like locks. So, for example, a user is (say) updating
>records of a department one by one and untill she is busy, you don't
>want that block of records to be accessible. My solution is to put
>the key range of the records to be used by her in a table and then
>every access it channeled thru that table.

why not SELECT .... FOR UPDATE that range instead of putting them in another table.

SELECT ... FOR UPDATE will allow others to read the underlying data with a select (like you would).

SELECT ... FOR UPDATE would prevent others from locking the same or overlapping range (you can use SELECT ... FOR UPDATE NOWAIT to allow them to know immediately they cannot procedure).

It achieves what you describe and you don't have any artificial lock tables to worry about.

>Problem that I face is
>that what happens if the user who has the lock (i.e. the entry in
>the "lock" table) experiences a system crash (of whatever nature)
>and has her session terminated before the application had the chance
>to clean up the entry in the "lock" table. Now we have a situation

if the client app crashes -- typically the back end (in a networked environment) will hang out anyway and the on-logout trigger wouldn't have fired -- they are still logged in.

>where a user is not even (probably) logged in but has a hold on a
>block of records. To clean up this entry in the lock table is little
>tircky and, I think, best handelled by a trigger which Oracle will
>automatically execute when a session terminates. I think of the
>following solution in the meantime:
>When ever a query is made on the lock table to see if someone has
>a lock on the records that you are interested in, at the same time
>do a query on the V$SESSION view to see if the lock-holder's session
>is still alive and kicking, otherwise delete the "locking" entry!

Just add a column to your locking table (if you continue to use it, i would suggest not using it but use select for update). This column will default to "userenv('sessionid')". Then, create a view on this table such as:

alter table my_lock_table
add that_new_column number default userenv('sessionid');

create view my_lock_table_view
as select <the relevant columns>
from my_lock_table
where that_new_column in ( select audsid from v$session );

Then, use that view instead of the real table for inserting and selecting from. It will only return rows of people logged in (but still suffers from dead client connections but sql*net with dead client detection can fix that for you)

>
>What do you think about this method? Which field in the V$SESSION
>a. uniquely identifies a session, and b. gives the true status?
>
>Thanks and Regards,
>Nasir (nnoor_at_cris.com)
 

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA

--
http://govt.us.oracle.com/    -- downloadable utilities
 
----------------------------------------------------------------------------
Opinions are mine and do not necessarily reflect those of Oracle Corporation
 
Anti-Anti Spam Msg: if you want an answer emailed to you, 
you have to make it easy to get email to you.  Any bounced
email will be treated the same way i treat SPAM-- I delete it.