Select and Update Together.

[Jigar Naik]

Hi ,

I have got the following table structure.

SQL> DESC SMS_INBOUND;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 MSG_ID                                    NOT NULL NUMBER(20)
 DNIS                                               NUMBER(10)
 SHORT_MESSAGE                                      VARCHAR2(200)
 MOBILE_NUMBER                                      NUMBER(10)
 RECEIVE_TIME                                       TIMESTAMP(6)
 READ_FLAG                                          NUMBER(2)

Now i want to read 10 messages at particular interval, Lets say i am reading 10 messages using select statement and when to fire the select statement i am deciding through java scheduler.

but while i am reading a messages from the table using select statement i want to update the READ_FLAG value at the same time instance.

If it possible ?
PLUS some other application will be continuously performing insert in SMS_INBOUND table.

so use of the flag is to keep track on how many messages i have read using select statement for further processing.

How do i achieve that ?

Please suggest me some idea for the same.

Thanks & Regards,
Jigar Naik

[Jan Cumps]

This might work:

Open transaction

Get list of msg ids you want to handle
for each of these MSG_IDs:

select .... from SMS_INBOUND where MSG_ID = ... FOR UPDATE;
(if you have a unique index on MSG_ID, this allows Oracle to only lock that row. Else this can result in a page lock - or worse)
You now have unique update access to that row, as long as your transaction lasts.

update SMS_INBOUND set READ_FLAG = .... where MSG_ID = ... ;


Commit transaction

[Jigar Naik]

what id i don't use FOR UPDATE ?
Because i am getting the primary key while firing select statement.

and even if i use for update there's going to be two transaction on database one is SELECT and the other is UPDATE.

What if I use simple Select statement to get the record and simple update statement bases on the primary key to update the flag column ?

[Hemavathy Ramamoorthy Gopal]

Hi Jigar Naik,

Write a procedure consisting of the following steps.

Step 1 : First select the records according to the requirement.

Step 2 : Pass the unique value (Primary Key Column) of above selected records as an input to update statement to update the flag.


I think these steps will meet your requirment if there exists any discrepency let me know.

[Jan Cumps]

What if I use simple Select statement to get the record and simple update statement bases on the primary key to update the flag column ?

As long as you do this in one transaction, and use the for update clause, you will be the only one that can modify the record.

When you use the for update clause, Oracle will tak e are that you will get a write lock on the data. Other people are able to read the data. But they can't do a select for update, and they can't alter the record until you end the transaction.


You can Google for 'Pessimistic locking' to get more info on this (troublesome) topic.

[Avi Abrami]

Jigar,

there's going to be two transaction on database one is SELECT and the other is UPDATE

Are you sure about this?
I'm not, but I think you are wrong.
Have you read Thomas Kyte's books on Oracle?
It's been a while since I read them and also a while since I used Oracle, but he does describe transactions and use of FOR UPDATE.
I highly recommend reading his books.

Good Luck,
Avi.