Tejas, January 08, 2002 - 1:08 pm UTC
I think, dbms_lock is not actually locking any row(s).
If one user holds the lock in exclusive mode then other user
can not hold the "same" lock. But it does not prevent the direct update of the row (by using update table set ...).
Please let me know, What I understood is true or not ?
Could I prevent these row(s) from updating by other users, while associated lock (using dbms_lock) is held by an user ?
January 08, 2002 - 2:15 pm UTC
You can, if you write you OWN data access layer (eg: an API).
Instead of allowing:
update t set x = ... where pk = ....
you write a procedure
procedure p( p_pk in number, p_x in varchar2 )
is
begin
dbms_lock -- get a lock named after the primary key
update the row
end;
don't let people do updates, let them use an API.
Unlock Only a Subset of Locked Rows
Ed, February 10, 2007 - 2:14 pm UTC
Bringing this back after 5 years...
Tom, you said
>> Commit/rollback = locks on rows are gone.
SQL> select * from emp where empno in (7369, 7521, 7698) for update ;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
7369 SMITH CLERK 7902 17-Dec-1980 800.00 20
7521 WARD SALESMAN 7698 22-Feb-1981 1250.00 500.00 30
7698 BLAKE MANAGER 7839 01-May-1981 2850.00 30
Is there a way to release lock on empno = 7521 only ?
still locking the other 2
thanks
10gR2
February 12, 2007 - 10:17 am UTC
nope.