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

Recent Posts

See All

MySQL Installation on Linux

This article demonstrates MySQL installation on Oracle Enterprise Linux 6.5 version. Installation of MySQL Make sure you are able to connect internet via virtual machine. Try to ping google.com and pr

Add ISO image storage repository on XenServer

If you have used VMware ESXI, its very easy to upload ISO files to the data store and create VMs. With XenServer, you must create your own ISO repository to upload ISO files. Create ISO directory Acce

Automate RMAN Backups using Shell Scripts

In a real environment, off course you will not manually trigger all the Oracle database backups. You need an automated mechanism to trigger RMAN backups. In this article we will look at RMAN backup au

  • Facebook
  • Twitter
  • YouTube
  • Instagram

© 2020 by DBA Genesis

Feedback