: Is global ordering by commit SCN possible in Debezium Oracle pipelines?
17 views
Skip to first unread message
Олександр Тупчієнко
Dec 21, 2025, 3:32:15 PM (2 days ago) Dec 21
to debezium
Hi,
In my pipeline (Debezium Oracle → Kafka with 3 partitions per topic → AWS Streaming Ingestion → Redshift), I’m capturing CDC from around 200 tables, and I sometimes see out-of-order CDC events, where events with a lower commit SCN arrive after higher SCNs. This occasionally causes incorrect sequences like UPDATE followed by CREATE for the same primary key when applying MERGE logic in Redshift.
Is such a situation possible in practice, and if so, how can one protect against it or mitigate its impact?
Chris Cranford
Dec 21, 2025, 10:59:58 PM (2 days ago) Dec 21
to debe...@googlegroups.com
Hi -
Events are emitted by the source connector is commit order, not in individual execution order. This is because Oracle stores transactions in an interweaved pattern. Let's take the following example:
Transaction 1 starts
Transaction 1 modifies TABA row with primary key 1
Transaction 2 starts
Transaction 2 modifies TABA row with primary key 2
Transaction 1 modifies TABA row with primary key 3
Transaction 2 commits
Transaction 1 commits
With the topic having a single partition, events would be consumed as: PK2, PK1, PK3. This is because of transaction commit order, not individual transaction order. Two concurrent transactions cannot modify the same row, so there is no concern here with the same primary key due to how row-level locking works.
If we shit to your multi-partition scenario, these events could be consumed by the sink in any order, and it all depends on which partitions the events are stored in. If all events were stored in the same partition, then you get the same exact behavior as I described above with a single partition; however, if they're distributed across partitions the the order is not guaranteed by Kafka and they can arrive at the consumer in any order, and even being out of order within the context of the same transaction. The only guarantee in this case is that events for the same primary key will always be consumed in dispatch order, and due to Oracle row-level locking, that is transaction commit order.
One added note, SCN is not unique, and within the context of two transactions, you can see changes for different rows of a table come in where the SCN moves forward and backward for individual inserts, updates, or deletes. However, for the same primary key, the SCN should always be the same or increasing by definition of the source table row-level locking.
Are you performing any streaming processing on the data set before writing to RedShift where you base any logic on the SCN?
-cc
Events are emitted by the source connector is commit order, not in individual execution order. This is because Oracle stores transactions in an interweaved pattern. Let's take the following example:
Transaction 1 starts
Transaction 1 modifies TABA row with primary key 1
Transaction 2 starts
Transaction 2 modifies TABA row with primary key 2
Transaction 1 modifies TABA row with primary key 3
Transaction 2 commits
Transaction 1 commits
With the topic having a single partition, events would be consumed as: PK2, PK1, PK3. This is because of transaction commit order, not individual transaction order. Two concurrent transactions cannot modify the same row, so there is no concern here with the same primary key due to how row-level locking works.
If we shit to your multi-partition scenario, these events could be consumed by the sink in any order, and it all depends on which partitions the events are stored in. If all events were stored in the same partition, then you get the same exact behavior as I described above with a single partition; however, if they're distributed across partitions the the order is not guaranteed by Kafka and they can arrive at the consumer in any order, and even being out of order within the context of the same transaction. The only guarantee in this case is that events for the same primary key will always be consumed in dispatch order, and due to Oracle row-level locking, that is transaction commit order.
One added note, SCN is not unique, and within the context of two transactions, you can see changes for different rows of a table come in where the SCN moves forward and backward for individual inserts, updates, or deletes. However, for the same primary key, the SCN should always be the same or increasing by definition of the source table row-level locking.
Are you performing any streaming processing on the data set before writing to RedShift where you base any logic on the SCN?
-cc
--
You received this message because you are subscribed to the Google Groups "debezium" group.
To unsubscribe from this group and stop receiving emails from it, send an email to debezium+u...@googlegroups.com.
To view this discussion visit https://groups.google.com/d/msgid/debezium/86829f31-3fc5-4821-96d9-4959716f29ban%40googlegroups.com.
Reply all
Reply to author
Forward
0 new messages