Results 1 to 8 of 8
  1. #1
    Join Date
    Jan 2004
    Posts
    153

    Unanswered: For Update OF <Col1>

    Hi All,

    I have given the following SQL statement :

    Sql> Select * from Emp FOR UPDATE OF SAL;

    Now another user has given the following SQL statement :

    Sql> Update Emp set Job = 'CLERK';

    and user gets locked.

    As per my understating FOR UPDATE clause ensure the lock at LOW level, then what is the use of FOR UPDATE OF <COL_NM>?

    And also I would like to inform you that inspite of using 'NOWAIT' clause the user is locked forever. Than what is the purpose of using the 'NOWAIT' clause?


    Tx,
    JD

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    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).

  3. #3
    Join Date
    Jan 2004
    Posts
    153
    Thanks for your reply.

    My queries are :
    1.What is the purpose of using Column name with FOR UPDATE clause when it is ensuring the ROW level lock.

    2. Inspite of using ‘NOWAIT’ clause, the transaction is gets locked until issuing a COMMIT or a ROLLBACK.

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    • How do you mean at ROW level? If you issue

      SELECT * FROM emp FOR UPDATE OF sal;

      it will lock the WHOLE table. What makes you think that it will lock it at the row level? Which row?

      But, using the WHERE clause makes things different:

      SELECT * FROM emp WHERE deptno = 10 FOR UPDATE OF sal;

      will lock ONLY employees who work in department no. 10. This WILL lock the table at ROW level.

    • Which transaction gets locked? The one that issues SELECT ... FOR UPDATE NOWAIT, or another one? I can't reproduce what you are saying.

  5. #5
    Join Date
    Jan 2004
    Posts
    153
    • 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?

    • The transaction gets locked for another one.

  6. #6
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    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.

  7. #7
    Join Date
    Jan 2003
    Location
    China
    Posts
    38

    Wink

    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:

    DECLARE
    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:

    DECLARE
    CURSOR c1 IS SELECT ename, dname FROM emp, dept
    WHERE emp.deptno = dept.deptno AND job = 'MANAGER'
    FOR UPDATE OF sal;
    Last edited by 884813; 11-29-06 at 10:11.
    Fan Yi

  8. #8
    Join Date
    Jan 2004
    Posts
    153
    Thank you very much.
    Now the concept of using FOR UPDATE OF <COL_NAME> is clear to me along with ‘NOWAIT’ also.


    Tx,
    JD

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •