Lock Management Services

I want to have the capability of managing multiple table/row locks within one connection to the database. It's my understanding that the purpose of the Oracle Lock Management Services (utilizing the DBMS_LOCK package) is to do just that. What I am attempting to do is:

1 - make one connection to the database
2 - request an exclusive row lock via the Oracle Lock Manager
(sys.dbms_lock.request)

3 - select TABLE1, ROW53 (for example)
4 - request a second exclusive row lock via the Oracle Lock Manager
(sys.dbms_lock.request)

5 - select TABLE2, ROW99 (for example)
6 - release second lock via the Oracle Lock Manager (sys.dbms_lock.release)
7 - commit the update to TABLE1, ROW53 (first transaction)
8 - release the first lock via the Oracle Lock Manager

note: If I do a 'select for update' from the two tables, for which Oracle automatically handles the lock, that when I do a rollback to release the lock on TABLE2, TABLE1 is also released. I want to retain the lock on TABLE1 to possibly commit any changes to this table. I can't use savepoints either.

I have been successful in requesting a lock, which SQL*DBA shows. I have also released the locks. However, I have not been successful in applying the lock.  What I've done is requested a lock and did a 'select' from a table. My assumption was, with the lock in place, the row associated with my select would be locked. However, through a second connection, I was able to perform an update on that row.

Any success in using the Lock Management Services?

Thanks,
Tim Received on Wed Apr 26 1995 - 00:00:00 CEST