CODEWITHSUNDEEP
javasqloracle-database
yovan786
yovan786

Reputation: 633

How to lock records in Oracle while doing some validation

I have a bash script running a Java application every 5 min. The Java application gets records from a table meeting certain conditions, from the Oracle database. I have to do several validations on each individual record returned in the Resultset and if the current record passes the validations,I add the particular record in an ArrayList. And finally when all records are validated, do a batch update on these records.

The problem is that the validations take time and new records are added by that time. So there are times when a previous script has not yet finished its execution, when a second script is running. What i want is to lock the current non-updated records so that another script does not fetch them from the database.

The best I can think of is having a status column in the table.

The Java application would fetch records where the status is 0, set them to 1 and perform the validations consequently. Finally after validations (success or not), the status is set to 2.

Can you think of a better alternative as it seems a bit "clunky".

Upvotes: 1

Views: 288

Answers (1)

eaolson
eaolson

Reputation: 15094

You can select the batch of records FOR UPDATE, which will lock only those rows and prevent another session from gaining a lock on them.

SELECT mycol
  FROM mytable
 WHERE ...
   FOR UPDATE

Upvotes: 3

Related Questions