If I query a range of rows ordered by an identity primary key in oracle, can the result of the query contain a gap where a later query will return the missing row?
According to Oracle 12c 12.1 Online Documentation → 9 Data Concurrency and Consistency, “The database uses an internal ordering mechanism called an SCN to guarantee the order of transactions. As the SELECT statement enters the execution phase, the database determines the SCN recorded at the time the query began executing. In Figure 9-1, this SCN is 10023. The query only sees committed data with respect to SCN 10023.” Does SCN have the same order as an IDENTITY column? If not, is it possible to select ordered by SCN?
Would SET TRANSACTION ISOLATION LEVEL SERIALIZABLE help? This sounds similar to the “phantom reads” problem that the serializable isolation level avoids, except that I care about missing rows in a single select statement rather than missing rows between select statements.
My use case is this: for each change to one table, a trigger inserts events into a second events table which has a column id NUMBER(30) GENERATED ON NULL AS IDENTITY and also has a PRIMARY KEY ("id") constraint. My program fetches the following events after the most recently read event, like this: SELECT * FROM events ORDER BY id WHERE id > :lastId FETCH NEXT 10 ROWS ONLY. I want to make sure that events are not accidentally skipped or reordered.
Yes it can.
First, if your database is using RAC and the sequence used to generate the keys is defined as NOORDER, the default, each node of the cluster has a separate sequence cache that will be used to return values. Assuming the default sequence cache of 20, that means that node 1 would be producing sequence values 1, 2, 3, ... at the same time that node 2 would be producing sequence values 21, 22, 23, ... .
More generally, if you aren't using RAC or you've ensured that the sequence generates values in strictly increasing order, there is no guarantee that your transactions commit in the order in which they generate the sequence values. For example, at time t0, transaction 1 might get the sequence value of 1. At time t1, transaction 2 might get the sequence value of 2. If transaction 2 then commits at time t2 while transaction 1 is still open, the value of 2 would be committed and potentially readable by your query while transaction 1 with the value of 1 would be committed at some later point in time.
Oracle has asynchronous queues to send messages from one component to another so one option would be to simply use that functionality in your application rather than rolling your own. Your trigger would enqueue a message and then the consumer would dequeue those messages. If you want to roll your own queue, you'd probably need to add an additional processed column that you flip when you have processed the event. You could then run a query that looks for rows in the event table where processed is 0/ N.