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
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.
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??
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
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
> 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.
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.
