0

I am accessing a table in the Oracle 12C to generate unique reference number from the table and update the next number to the same row. At the same time, more than 3 application is accessing the same table for the reference number. Is there any way to lock the Reading of the table while I am accessing the connection using query. I have tried the following query.

LOCK TABLE UPS_HDR_SEQ IN SHARE ROW EXCLUSIVE MODE;
select max(SEQN) From UPS_HDR_SEQ where SRV_ID=?;

But it is only allowing the User Lock the connection during the committing of a transaction. During that time any other application can read the data gets the reference number. In this case, the other application can also get reference number same as my reference number. Is it Possible to implement Read lock of the table Using Query?

Akshay Prabhu
  • 175
  • 1
  • 4
  • 18
  • There is no way how you can block reading from Oracle database. – ibre5041 Sep 17 '18 at 13:46
  • It is not very clear why do you need the read lock. – Alfabravo Sep 17 '18 at 13:51
  • Is there a reason you aren't using an actual oracle sequence to generate the reference number? [Possibly related.](https://stackoverflow.com/q/17210835/266304) – Alex Poole Sep 17 '18 at 13:56
  • I can't use Oracle sequence lot required a change in other applications which I don't have any control. Otherwise, Sequence is the best option – Akshay Prabhu Sep 17 '18 at 14:34
  • @Alfabravo Read lock is required because at the time of me accessing the table values no one should get the value I am accessing. After updating from my side only other session must get the value. – Akshay Prabhu Sep 17 '18 at 14:42

1 Answers1

0

Look at the documentation for select for update of. This locks the row when you select. Of course, you need to follow it immediately with an update.

Steve11235
  • 2,849
  • 1
  • 17
  • 18
  • Thank you, Steve, but select for update is also having the similar issue at a time if two connection is calling select for update both are giving same values as a result which will result in the duplicate reference number. – Akshay Prabhu Sep 17 '18 at 14:37
  • No, select for update of using row locking so that one transaction must complete before the other starts. That's the point. You do the select to get the current value, modify it, and then do update to save the new value and unlock the row. – Steve11235 Sep 18 '18 at 19:03