FOR UPDATE means that records selected in the SELECT statement will be locked until transaction (which contains SELECT ... FOR UPDATE statement) issues a COMMIT or a ROLLBACK.
If you do not specify NOWAIT, SELECT will have to wait until the lock (made by another transaction) is released (which can be indefinitely). If you do specify NOWAIT, you'll immediately get ORA-00054 error (resource is busy).
• Yes, you got the right point.
'SELECT * FROM emp FOR UPDATE OF sal' will lock the whole table but at the same time 'SELECT * FROM emp FOR UPDATE' also does the same function. Then what is the use of adding column 'sal' with the FOR UPDATE clause?
jayanta_deb, the purporse of using a column name in the FOR UPDATE OF .. clause is to lock only the rows that belong to the tables that you especify their columns. Think about when you use joins, views, etc.. that you only want to lock rows of a especific table in the query. Otherwise, Oracle will hold locks on all the tables involved in the query (not desirable sometimes).
It is not clear where are you using NOWAIT, an example would be helpfull.
Using FOR UPDATE
When you declare a cursor that will be referenced in the CURRENT OF clause of an UPDATE or DELETE statement, you must use the FOR UPDATE clause to acquire exclusive row locks. An example follows:
CURSOR c1 IS SELECT empno, sal FROM emp
WHERE job = 'SALESMAN' AND comm > sal
FOR UPDATE NOWAIT;
The SELECT ... FOR UPDATE statement identifies the rows that will be updated or deleted, then locks each row in the result set. This is useful when you want to base an update on the existing values in a row. In that case, you must make sure the row is not changed by another user before the update.
The optional keyword NOWAIT tells Oracle not to wait if requested rows have been locked by another user. Control is immediately returned to your program so that it can do other work before trying again to acquire the lock. If you omit the keyword NOWAIT, Oracle waits until the rows are available.
All rows are locked when you open the cursor, not as they are fetched. The rows are unlocked when you commit or roll back the transaction. So, you cannot fetch from a FOR UPDATE cursor after a commit. (For a workaround, see "Fetching Across Commits".)
When querying multiple tables, you can use the FOR UPDATE clause to confine row locking to particular tables. Rows in a table are locked only if the FOR UPDATE OF clause refers to a column in that table. For example, the following query locks rows in the emp table but not in the dept table:
CURSOR c1 IS SELECT ename, dname FROM emp, dept
WHERE emp.deptno = dept.deptno AND job = 'MANAGER'
FOR UPDATE OF sal;