Optimistic ORA_ROWSCN 2: the SCN

In my previous post, I covered lost updates. Before saying how ORA_ROWSCN can help avoid them, I need to talk about the SCN. It's hard to be simple and correct about such a fundamental element of the Oracle database, but I'll try.

Optimistic ORA_ROWSCN 1: Lost Updates

I've gotten lots of feedback about my proposal to use ORA_ROWSCN for optimistic locking. I'm going to start over in more detail and try to leave less scope for misinterpretation. Thanks to all for your comments, especially Jonathan Lewis who saved me from some technical doubts. "Optimistic locking" is a type of solution to the problem of "lost updates". So what do we mean by "lost update", anyway?

Optimistic Locking with one Timestamp?

A reader called chris asked me a good question about optimistic locking: why not use a “user-defined” scn (e.g. timestamp) as an extra column to achieve the same goal as ORA_ROWSCN? Well, chris, you'd have to use one per row, not one per transaction.

More on Optimistic Locking with ORA_ROWSCN

Thanks to comments by Tony Hasler and pingbacks from Jeff Kemp, here's more detail on how optimistic locking works with SCNs, especially with respect to "restarts" during update.

Optimistic Locking: One SCN to rule them all

Previously I showed how to avoid lost updates with ORA_ROWSCN. Now let’s create an API that avoids lost updates with just one SCN. What kind of API? A transaction consists of one or more changes to data that should happen together: either all should happen or none. When the transaction commits, all the changes happen; […]

Avoiding Lost Updates with ORA_ROWSCN

Applications today use "optimistic locking" to avoid lost updates. Tom Kyte thought of using ORA_ROWSCN for optimistic locking, then decided it didn't work (see ORA_ROWSCN for optimistic locking). I now think it does!

Comparing Expression Lists (not tuples)

This post is a long-winded answer to a question Bryn Llewellyn asked me on Twitter about comparing expression lists.

#OUGN17 Row Pattern Matching with MATCH_RECOGNIZE

OUGN17 (the boat conference) rocked - and the boat didn't, thank goodness. I gave two talks about row pattern matching that were well received: two or three brave souls even went to both talks. I put my slides on the OUGN Google Drive page and on Slideshare.

Multi-Table Inserts with Sequences

Multi-table inserts are a drag when sequences are involved, because we don't have enough control over when the sequence is incremented. I've seen some dangerous workarounds. Here is a workaround that I think is safe.