As Pawel showed in his post some details about how the row scn technology works in specific situations isn’t as well documented as you’d wish.
I asked Oracle development to clarify the questions I had and the answer back was both interesting and useful. Every kind of update and lock on a row will make the rowscn for the row/block NULL until the updating/locking transaction completes. That is, any kind of lock and update can be identified when retrieving data.
More interesting is that this can be used to implement “skip locked” which is a feature oracle AQ uses, but it is not supported for use by end users. For a table where rowdependencies is defined, this can now be implemented with “where ora_rowscn is NOT NULL”. I don’t know exactly how and when I’ll use it, but it is good to have this tool when the situation arises. I’m sure it’ll come in handy soon enough as discussions about the skip locked feature seems to occur almost on a monthly basis.
I’m on a long vacation so I cannot test this until I come home again. I had some time over this mornings, so I figured it would be a good time to show that the blog isn’t completely dead, it’s just not too active when I have to choose between talking with friends and family I haven’t seen in a couple of years and researching Oracle technologies.
The problem is that the only rows you can see where ora_rowscn is NULL are the ones updated by your own session. Any updates by other sessions will appear with the ORA_ROWSCN of the last committed change because the record is picked up as a consistent read before being picked up in current mode for the update.
Thank you Gary. I was afraid there would be some such limitation to this. It sounded too good, so I shouldn’t be surprised that it was.
Oracle supports information from the development group didn’t really tell me what I wanted to know, though what they said is probably 100% correct.
I can’t wait to get a chance to play with this and see how and when it can be used.