Login In/out Triggers, implmenting NOW...

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. 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 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!

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) Received on Wed Nov 25 1998 - 00:00:00 CST