0

Need help in understanding inner query select reads between two transaction

UPDATE SER_NUMBERS SET SER_NUM = SER_NUM + 1 WHERE YEAR = 
(SELECT YEAR FROM (SELECT YEAR FROM SER_NUMBERS WHERE YEAR >= 1950
AND SER_NUM < 999 ORDER BY YEAR) WHERE ROWNUM =1)

I am updating SER_NUM = SER_NUM + 1 every time the query executes, the request are so intense that there could be thousands of request per second.

Question is that can two query transaction have a chance of reading same value from the inner select and two transaction updates the same value?

Is there a simple way to make Transaction-2 wait for the read until Transaction-1 finishes its update?

As I see from the oracle documentation the default isolation is Read committed

(Oracle Database does not prevent other transactions from modifying the data read by a query, that data can be changed by other transactions between two executions of the query. Thus, a transaction that runs a given query twice can experience both nonrepeatable read and phantoms.)

Any suggestion on best approach to achieve this?

mnm
  • 798
  • 1
  • 6
  • 11

1 Answers1

1

Is there a simple way to make Transaction-2 wait for the read until Transaction-1 finishes its update?

In general, Readers don't block writers, and writers don't block the readers.

From the documentation,

Transaction-Level Read Consistency

Oracle Database also offers the option of enforcing transaction-level read consistency. When a transaction runs in serializable mode, all data accesses reflect the state of the database as of the time the transaction began. Thus, the data seen by all queries within the same transaction is consistent with respect to a single point in time, except that queries made by a serializable transaction do see changes made by the transaction itself. Transaction-level read consistency produces repeatable reads and does not expose a query to phantoms.

Read more about Data Concurrency and Consistency

Lalit Kumar B
  • 47,486
  • 13
  • 97
  • 124