Locking of tables

dillipsatpathy's Avatar
Light Poster
Can any body help me out with describing the Difference between implicit and explicit locking?I have very much doubt in this topic.so kindly elaborate it with example(if possible) please.

Thanking you
Dillip
0
shabbir's Avatar, Join Date: Jul 2004
Go4Expert Founder
Both implied and explicit read locks are still a read lock, however a read lock that had been obtained will prevent any writes to the table.

Difference between implicit and explicit locks.
A select statement will cause an implicit read lock within the execution of the select, release after the select completes. If there are multiple selects, the read lock is obtained for each select separately, so writes is possible in between the multiple selects.

An explicit read lock will keep the lock until it is released. You can do multiple select between the LOCK TABLES and UNLOCK TABLES and you are sure that no data in the tables you have read locked is changed in between the multiple selects.

Take an example:
LOCK TABLE t1 READ, t2 READ;
SELECT * FROM t1;
SELECT * FROM t2;
UNLOCK TABLES;

With the explicit LOCK TABLES, you can be sure that there is no changes to the tables in between your selects. So, the data you read is consistent.

Assuming you do not have the Lock Tables, what happened is that another connection is able to update the 2 tables in between your select, example, after you complete the 1st select, another connection is able to update the table t1 and t2 before you execute the 2nd select, in this case the data you have obtained from the 2 selects may no longer be consistent since the data had been changed between your 1st and 2nd select.
Therefore, you need the explicit Read Lock to get a consistant view of the data for the 2 selects.
0
dillipsatpathy's Avatar
Light Poster
Hi Shabbir, I have again a little prob in implicit cursor.Lets take a example...
If 'A' and 'B' are two user requesting a same Database table
User 'A' is first Transmitting a command : Select emp_no from emp where emp_no=211 for update;
Update sal
set sal=sal+500
from emp where empno=211;
and not commited
Then User 'B' is transmitting a command :Select emp_no from emp where emp_no=211 for update;
Update sal
set sal=sal+1000
from emp where empno=211;

Then User 'A' is transmitting a command :Select emp_no from emp where emp_no=212 for update;
Update sal
set sal=sal+500
from emp where empno=212;
commit;

Now can u tell me what will be the output??
0
shabbir's Avatar, Join Date: Jul 2004
Go4Expert Founder
Are all the transaction done using the same connection. I assume if thats the case then

A will have Salary + 1000 and B Salary + 500
0
SIKO's Avatar, Join Date: Oct 2007
Light Poster
i think user B will get an error while trying to update coz the table emp will be locked after user A made an update and didnt use commit the table will stay locked until user A commits or rollback
0
johnbangla's Avatar, Join Date: May 2008
Newbie Member
Where I Can Find Oracle Dba Exam Dump?
0
xpi0t0s's Avatar, Join Date: Aug 2004
Mentor
> If 'A' and 'B' are two user requesting a same Database table...

OK let's break this down into a step by step sequence of events.
I should probably check this with two SQL*Plus sessions but I'm pretty sure of myself here. If you get different behaviour let me know and I'll run an explicit test.


1. User 'A': Select emp_no from emp where emp_no=211 for update;
User A gets a lock on emp_no=211

2. User 'A': Update sal set sal=sal+500 from emp where empno=211;
Empno 211's salary is increased by 500

3. User 'B': Select emp_no from emp where emp_no=211 for update;
Since you haven't specified NOWAIT, this will wait, because empno 211's row is locked. User B cannot continue until the lock is released

4. User 'A': Select emp_no from emp where emp_no=212 for update;
User A gets a lock on emp_no=212
User B is still waiting for the lock on 211

5. User 'A': Update sal set sal=sal+500 from emp where empno=212;
Empno 212's salary is increased by 500
User B is still waiting for the lock on 211

6. User 'A': commit;
So empno 211's sal+=500 and empno 212's sal+=500 are committed
Locks on empno=211 and 212 rows are released
B is now able to proceed and the SELECT FOR UPDATE statement on empno 211's row locks the row and returns the data, which is now 500 higher than it was when B issued the command

7. User 'B': Update sal set sal=sal+1000 from emp where empno=211;
This increases empno 211's sal by another 1000. So it's now 1500 higher than it was before step 1.

This may not have been User A and User B's joint intention when they both attempted to update 211's salary so it's important that the application should allow for this scenario and make sure the users are notified when data they are trying to change has been updated by someone else.
0
xpi0t0s's Avatar, Join Date: Aug 2004
Mentor
Explicit locking is where you lock the data explicitly with SELECT FOR UPDATE or LOCK TABLE. Implicit locking is where Oracle acquires the locks automatically.