Home » RDBMS Server » Server Administration » Implicit and Explicit Locks (Oracle 10g)
Implicit and Explicit Locks [message #441940] Thu, 04 February 2010 00:02 Go to next message
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 #441943 is a reply to message #441940] Thu, 04 February 2010 00:07 Go to previous messageGo to next message
Yasir Hashmi
Messages: 304
Registered: April 2006
Senior Member
Hi,
Select for update would lock the entire table.
You cannot update any other row.
While in implicit locking,a table is allowed for other column updation.
Re: Implicit and Explicit Locks [message #441945 is a reply to message #441943] Thu, 04 February 2010 00:12 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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
Any DML would hang

Re: Implicit and Explicit Locks [message #441947 is a reply to message #441946] Thu, 04 February 2010 00:33 Go to previous messageGo to next message
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 #441948 is a reply to message #441943] Thu, 04 February 2010 00:36 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yasir Hashmi wrote on Thu, 04 February 2010 07:07
Hi,
Select for update would lock the entire table.
You cannot update any other row.
While in implicit locking,a table is allowed for other column updation.

This is wrong.
SELECT FOR UPDATE takes EXACTLY the same locks than UPDATE.

Regards
Michel

Re: Implicit and Explicit Locks [message #441949 is a reply to message #441940] Thu, 04 February 2010 00:39 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Database Concepts
Chapter 1 Introduction to the Oracle Database
Section Locking Mechanisms

Chapter 13 Data Concurrency and Consistency
Section How Oracle Locks Data

Regards
Michel
Re: Implicit and Explicit Locks [message #441961 is a reply to message #441940] Thu, 04 February 2010 02:13 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
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 Go to previous messageGo to next message
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 #441969 is a reply to message #441967] Thu, 04 February 2010 03:04 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I don't know why you focus on explicit/implicit, it is a very very very tiny point.
You lock rows, full stop.

Regards
Michel
Re: Implicit and Explicit Locks [message #441974 is a reply to message #441967] Thu, 04 February 2010 03:25 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
tejasvi_ss wrote on Thu, 04 February 2010 09:59
My 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 #441975 is a reply to message #441974] Thu, 04 February 2010 03:31 Go to previous messageGo to next message
tejasvi_ss
Messages: 22
Registered: February 2010
Location: Bangalore, India
Junior Member
Hi,

Now i got cleared my doubt. Thanks a lot.. Smile
Re: Implicit and Explicit Locks [message #441986 is a reply to message #441975] Thu, 04 February 2010 04:14 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
tejasvi_ss wrote on Thu, 04 February 2010 10:31
Hi,

Now i got cleared my doubt. Thanks a lot.. Smile

So can you explain us just to check we understand the same thing?

Regards
Michel

Re: Implicit and Explicit Locks [message #442000 is a reply to message #441986] Thu, 04 February 2010 05:44 Go to previous messageGo to next message
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 #442008 is a reply to message #442000] Thu, 04 February 2010 06:09 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SESSION 1
SQL> create table t (val integer);

Table created.

SQL> insert into t values (0);

1 row created.

SQL> commit;

Commit complete.

SQL> update t set val=1 where val=0;

1 row updated.

SESSION 2
SQL> select * from t where val=0 for update;
-- BLOCKED

You change the place where you take the lock but it is the same for UPDATE or SELECT FOR UPDATE.
See SELECT FOR UPDATE as just an UPDATE in 2 parts: first select and some time later update.
Note that update internally does the same thing but in one shot.

Regards
Michel
Re: Implicit and Explicit Locks [message #442011 is a reply to message #442008] Thu, 04 February 2010 06:18 Go to previous messageGo to next message
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 #442017 is a reply to message #442011] Thu, 04 February 2010 06:59 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Actually 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. So you can encounter the same problem: someone has updated the row you want to update after a SELECT FOR UPDATE.

Regards
Michel
Re: Implicit and Explicit Locks [message #442020 is a reply to message #442017] Thu, 04 February 2010 07:10 Go to previous messageGo to next message
tejasvi_ss
Messages: 22
Registered: February 2010
Location: Bangalore, India
Junior Member
ok..Thanks a lot for your answers. Hope i got lot of information which is too much for a very very very tiny thing..Smile thanks once again

Regards,
Tejasvi
Re: Implicit and Explicit Locks [message #442035 is a reply to message #442017] Thu, 04 February 2010 08:21 Go to previous messageGo to next message
cookiemonster
Messages: 12404
Registered: September 2008
Location: Rainy Manchester
Senior Member
Michel Cadot wrote on Thu, 04 February 2010 12:59
Actually 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 #442042 is a reply to message #442035] Thu, 04 February 2010 08:55 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Oracle locks all the rows referenced by the where clause before any fetches, which makes sense from my understanding of the concurrency rules.

No, it does not, imagine you have million rows to select but actually exit after modifying the first rows, you have to wait hours before starting.
You have to check it with a table that is greater than a couple of rows/blocks.

-- SESSION 1 - setup

SQL> create table t as (select * from dba_objects);

Table created.

SQL> insert into t select * from t;

49492 rows created.

SQL> /

98984 rows created.

SQL> /

197968 rows created.

SQL> /

395936 rows created.

SQL> /

791872 rows created.

SQL> commit;

Commit complete.

SQL> alter table t add (rn integer);

Table altered.

SQL> update t set rn=rownum;

1583744 rows updated.

SQL> commit;

Commit complete.


-- SESSION 1
15:42:20 SQL> declare cursor c is select * from t for update;
15:42:22   2  begin
15:42:22   3    open c;
15:42:22   4    dbms_lock.sleep(300);
15:42:22   5  end;
15:42:22   6  /
15:52:25 SQL> 


-- SESSION 2
15:42:28 SQL> update t set rn=2000000 where rn=1500000;

1 row updated.

15:43:03 SQL> 

Check the times, you can see I updated a row BEFORE the PL/SQL block exited.

Regards
Michel
Re: Implicit and Explicit Locks [message #442044 is a reply to message #441940] Thu, 04 February 2010 09:39 Go to previous message
cookiemonster
Messages: 12404
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.
Previous Topic: server dows automatically after every half an hour or 15 min.
Next Topic: Error:No data to read from socket
Goto Forum:
  


Current Time: Mon Dec 05 08:47:14 CST 2016

Total time taken to generate the page: 0.09654 seconds