How to specify "no update" in an oracle select statement?

In a simple oracle statement, I have to fetch rows with no locks 开发者_Go百科on it. I think this can be done through select with no update statement. Correct me if I'm wrong ? If not can anyone pls let me know the format for it ?

Yes, if you don't specify for update in a select then you haven't locked any rows.

If your code is only doing a select (and not a "select for update"), no locks would be acquired on the record. If you have DML (Update,select,delete) on the rows or a "Select for Update", then you have the possibility of locking.

**Old Answer

You are probably looking for the "FOR UPDATE NOWAIT" clause in Oracle, but it's behavior is not as you described.

It will try to acquire a lock on the row/rows in the select and return an error if the lock is already acquired by someone else.

Session1: (No Commit yet.. Row Locked by this statement)

SQL> update scott_emp 
  2    set sal = sal + 100
  3    where empno = 7839;

1 row updated.

Session2 :

SQL> select * from scott_emp
  2  for update nowait;
select * from scott_emp
              *
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified