| Implicit and Explicit Locks [message #441940] |
Thu, 04 February 2010 00:02  |
tejasvi_ss
Messages: 22
Registered: February 2010
Location: Bangalore, India
|
Junior Member |
|
|
Hi,
I am a fresher for Oracle DB. i need the exact difference between Implicit and Explicit locks. Because,As i tried below scenario it works even in implicit then why we go for Explicit locks.(These is excluding TABLE LOCK and NOWAIT concept)
Eg for Implicit
session 1:
select * from emp where empno=7369;
update emp set ename='A' where empno=7369;
session 2:
select * from emp where empno=7369;
update emp set ename='B' where empno=7369;
Result: it will in waiting state as there is commit or rollback done in session 1.
When we consider same using Explicit locks ie by replacing select statement with 'SELECT * FROM emp where empno=7369 FOR UPDATE;' in both sessions, it will result the same output as implicit.
Then why these concept exist in explicit when it can be done with implicit.
|
|
|
|
|
|
| Re: Implicit and Explicit Locks [message #441945 is a reply to message #441943] |
Thu, 04 February 2010 00:12  |
tejasvi_ss
Messages: 22
Registered: February 2010
Location: Bangalore, India
|
Junior Member |
|
|
Hi,
ya I got that concept. But in my case it will lock only that specific row. Then both works and result in same output. i dint find any difference. is implicit lock equivalent to explicit lock with out NOWAIT and LOCK TABLE concept?
|
|
|
|
| Re: Implicit and Explicit Locks [message #441946 is a reply to message #441940] |
Thu, 04 February 2010 00:25 |
Yasir Hashmi
Messages: 304
Registered: April 2006
|
Senior Member |
|
|
You are mixing two things:
CASE 1:IMPLICIT lOCK
session 1:
select * from emp where empno=7369;
update emp set ename='A' where empno=7369;
session 2:
select * from emp where empno=7369;
update emp set ename='B' where empno=7369;
session 2 will hang
But If i do
update emp set ename='B' where empno=7370;
it will work
Implicit locking locks a specific row of the table.
WIth explicit lock with select for update statement,it will lock the entire table,hence no dml is allowed.
session 1
SQL>select * from emp for update;
session 2
|
|
|
|
| Re: Implicit and Explicit Locks [message #441947 is a reply to message #441946] |
Thu, 04 February 2010 00:33 |
tejasvi_ss
Messages: 22
Registered: February 2010
Location: Bangalore, India
|
Junior Member |
|
|
I agree with your answer in your case. But in my case it will lock only single row in both implicit and explicit lock. why explicit have these option when it can be done through implicit itself.
|
|
|
|
|
|
|
|
| Re: Implicit and Explicit Locks [message #441961 is a reply to message #441940] |
Thu, 04 February 2010 02:13 |
Yasir Hashmi
Messages: 304
Registered: April 2006
|
Senior Member |
|
|
SESSION 1
SQL> select * from emp for update;
SESSION 2
Any DML to emp to any of its row results in a lock.
From the docs
Quote:
A transaction acquires a table lock when a table is modified in the following DML statements: INSERT, UPDATE, DELETE, SELECT with the FOR UPDATE clause, and LOCK TABLE
Then how can SELECT FOR UPDATE takes EXACTLY the same locks than UPDATE?
|
|
|
|
| Re: Implicit and Explicit Locks [message #441965 is a reply to message #441961] |
Thu, 04 February 2010 02:47 |
 |
Michel Cadot
Messages: 68665
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
Quote:Then how can SELECT FOR UPDATE takes EXACTLY the same locks than UPDATE?
I don't understand the question. It is how Oracle works.
Precise your question, in your example you lock ALL rows as there is no where clause. Note this is not the same example as the post I answered.
Regards
Michel
[Updated on: Thu, 04 February 2010 02:48] Report message to a moderator |
|
|
|
| Re: Implicit and Explicit Locks [message #441967 is a reply to message #441965] |
Thu, 04 February 2010 02:59 |
tejasvi_ss
Messages: 22
Registered: February 2010
Location: Bangalore, India
|
Junior Member |
|
|
Hi yasir,
Actually my question is when a WHERE clause is present in a query. But you are trying to say for whole table. I agree the answer is right for your case. In Explicit,
SELECT * FROM emp for UPDATE;
-- Will lock whole table [b]Explicitly[/b]
SELECT * FROM emp where empno=1234 for UPDATE;
-- Will lock only that specific row [b]Explicitly[/b] but not whole table
In implicit,
update emp set ename='A';
-- Will lock whole table [b]Implicitly[/b]
update emp set ename='JOHN' where empno=7369;
-- Will lock only that specific row [b]Implicitly[/b] but not whole table
My question was why Explicit lock exists where oracle can do it implicitly in these scenario
[Updated on: Thu, 04 February 2010 08:23] by Moderator Report message to a moderator |
|
|
|
|
|
| Re: Implicit and Explicit Locks [message #441974 is a reply to message #441967] |
Thu, 04 February 2010 03:25 |
flyboy
Messages: 1903
Registered: November 2006
|
Senior Member |
|
|
tejasvi_ss wrote on Thu, 04 February 2010 09:59My question was why Explicit lock exists where oracle can do it implicitly in these scenario
In explicit locking scenario, you use SELECT statement for retrieving row value(s). If you would not lock them explicitly, some other user(s) could change them during that time.
If you do not need to SELECT value(s) from updated rows, you just issue UPDATE directly (implicit locking).
So, the answer to you question would be: in your scenario, you may use both types of locking. Anyway, there are other scenarios, which are not applicable by issuing only UPDATE - previous SELECT of updated rows is needed. In that cases, those queried rows have to be locked explicitly to avoid data inconsistency.
|
|
|
|
|
|
|
|
| Re: Implicit and Explicit Locks [message #442000 is a reply to message #441986] |
Thu, 04 February 2010 05:44 |
tejasvi_ss
Messages: 22
Registered: February 2010
Location: Bangalore, India
|
Junior Member |
|
|
Hi,
In Simple words, In case of Implicit locks, while u try to UPDATE a row Where u have no information that its already in use or locked by some other user and you will confirm that its locked only after execuion of UPDATE which may exit with SUCCESS or HANG status.
But in case of Explicit before you go for UPDATE ie with use of FOR UPDATE you will check and confirm that whether that row is not locked and can be UPDATED or not. So that you can decide before executing UPDATE statement that is accessible to UPDATE or not.
Hope its clear if i am not wrong
|
|
|
|
|
|
| Re: Implicit and Explicit Locks [message #442011 is a reply to message #442008] |
Thu, 04 February 2010 06:18 |
tejasvi_ss
Messages: 22
Registered: February 2010
Location: Bangalore, India
|
Junior Member |
|
|
hi,
ya..I agree with you. That what i was trying to say you. in Explicit you can lock before updating anything which helps that user to confirm (guarantee) that no one can update the selected rows until he completes his UPDATION with commit or rollback.
But in implicit there is no guarantee that rows which you are trying to UPDATE is free or will be till you execute UPDATE statement.
[Updated on: Thu, 04 February 2010 06:19] Report message to a moderator |
|
|
|
|
|
|
|
| Re: Implicit and Explicit Locks [message #442035 is a reply to message #442017] |
Thu, 04 February 2010 08:21 |
cookiemonster
Messages: 13925
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
Michel Cadot wrote on Thu, 04 February 2010 12:59Actually it is the same thing with SELECT FOR UPDATE, Oracle does not lock ALL rows (of the result set) when you open the cursor but only when you fetch them which is at the same time with SQL*Plus but not in PL/SQL when you loop into the SELECT FOR UPDATE to know if you want to update and how each row.
Maybe I'm misunderstanding, but I don't believe that's right. Oracle locks all the rows referenced by the where clause before any fetches, which makes sense from my understanding of the concurrency rules.
2 examples:
Example 1:
session 1:
SQL> create table test_locks as (select rownum a, rownum b from dual connect by level < 100);
Table created.
SQL> begin
2
3 for rec in (select a, b from test_locks where a < 50 order by a for update) loop
4
5 dbms_lock.sleep(1);
6 update test_locks set b = b where a = rec.a;
7
8 end loop;
9
10 commit;
11
12 end
13 ;
14 /
PL/SQL procedure successfully completed.
Session 2, immediately after hitting return on the / in session 1:
SQL> update test_locks set b=b where a = 49;
Result - session 2 hangs for 50 seconds then completes.
Example 2:
Session 1:
SQL> declare
2
3 cursor c1 is
4 select a,b
5 from test_locks
6 where a < 50
7 for update;
8
9 begin
10
11 open c1;
12
13 end;
14 /
PL/SQL procedure successfully completed.
SQL>
Session 2:
SQL> update test_locks set b=b where a = 49;
Result - session 2 hangs until you issue a commit or rollback in session 1.
Example 1 may not prove as much as I think it does due to implicit array fetching, but example 2 is pretty concrete.
|
|
|
|
|
|
| Re: Implicit and Explicit Locks [message #442044 is a reply to message #441940] |
Thu, 04 February 2010 09:39 |
cookiemonster
Messages: 13925
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
Your example doesn't actually prove me wrong:
SQL> create table test_locks as select * from all_tables;
Table created.
SQL> insert into test_locks select * from test_locks;
1178 rows created.
SQL> /
2356 rows created.
SQL> /
4712 rows created.
SQL> /
9424 rows created.
SQL> /
18848 rows created.
SQL> /
37696 rows created.
SQL> /
75392 rows created.
SQL> commit;
Commit complete.
SQL> select count(*) from test_locks;
COUNT(*)
----------
150784
SQL> set serveroutput on
SQL> declare
2 cursor c1 is select * from test_locks for update;
3 r1 c1%rowtype;
4 begin
5 dbms_output.put_line('start '||to_char(sysdate, 'HH24:MI:SS'));
6 open c1;
7 dbms_output.put_line('opened '||to_char(sysdate, 'HH24:MI:SS'));
8 FETCH c1 into r1;
9 dbms_output.put_line('fetched '||to_char(sysdate, 'HH24:MI:SS'));
10 close c1;
11 rollback;
12 end;
13 /
start 15:00:45
opened 15:01:41
fetched 15:01:41
PL/SQL procedure successfully completed.
SQL> /
start 15:10:23
opened 15:11:12
fetched 15:11:12
PL/SQL procedure successfully completed.
SQL> declare
2 cursor c1 is select * from test_locks;
3 r1 c1%rowtype;
4 begin
5 dbms_output.put_line('start '||to_char(sysdate, 'HH24:MI:SS'));
6 open c1;
7 dbms_output.put_line('opened '||to_char(sysdate, 'HH24:MI:SS'));
8 FETCH c1 into r1;
9 dbms_output.put_line('fetched '||to_char(sysdate, 'HH24:MI:SS'));
10 close c1;
11 rollback;
12 end;
13 /
start 15:16:32
opened 15:16:33
fetched 15:16:33
PL/SQL procedure successfully completed.
SQL> /
start 15:16:34
opened 15:16:34
fetched 15:16:34
PL/SQL procedure successfully completed.
SQL>
As you can see in the version with for update there is a minute gap between the open and the fetch. Without the for update the gap is basically nonexistent.
All your example proves is that since oracle can't physically lock all the rows in a large table instaneously it's possible for another session to sneak in and update something before you've acquired all the locks.
And yes if you lock the entire table then just modify a couple of rows it will take ages.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
