Forgive me, but sounds like a slightly suspect design. I am assuming your sms_inboud table is some sort of queue? I.e. some process inserts data into it, and you have a TimerTask that reads chunks of data, does something, then flags them as read? If you have a choice, the easiest way to do this would be to delete the read data and insert it into a sms_processed table. Or to delete them and have a trigger drop data into a history table.
If you can't do that, you could read an update in a transaction. This will have the effect of behaving somewhat like a single statatement.
If you have another process that can update your data you may want to consider pessimistic locking. This is not normally a good choice, because it creates a bottleneck in the database. You can use select...for update to lock the rows you are processing while you aere processing them. If you need to know for certain nothign else is changing the data you have selected while you are processing it, this would be a way.
That will work. What may give you problems is if more than one process updates this table. If it doesn't your solution is fine. If is does, you may have to cater for handling the selected data changing before you've updated it.
Joined: Dec 12, 2006
okkk... i got you, i need something which locks that particular record, so that no other process can update it,till the time i update that record.