• Arun Kumar

SELECT FOR UPDATE

The correct way to update records in Oracle is by first locking the rows for update and then issuing the update command.


SELECT FOR UPDATE command will select the specific records from the table and lock those records. This prevents other users from updating the records as the lock on those records is held by the current session.


SELECT FOR UPDATE command allows you to get records locked first. In the same session you can proceed with updating the records and proceed further by issuing commit.


Syntax


SELECT * FROM EMP WHERE DEPTNO=10 FOR UPDATE;

Let us assume that some other session is holding lock on the records returned by – SELECT * FROM EMP WHERE DEPTNO=10. The above query will wait until the lock is released.


If you do not want to wait (due to lock by some other session on the records you want to select for update) then use NOWAIT clause


SELECT * FROM EMP WHERE DEPTNO=10 FOR UPDATE NOWAIT;

The above command will check if there is any existing lock on the records. If records are not locked, you will hold the lock. If records are locked, the above command will not wait until the lock is released. It will return an error.


The correct way to update records is by always using SELECT FOR UPDATE.

70 views

Recent Posts

See All

Oracle 12c to 19c Database Upgrade

In this article we will be looking at two most commonly used methods of upgrading Oracle database from 12c to 19c Pre-upgrade tasks Upgrade using DBUA Upgrade using Manual Method You can perform a dir