Oracle – for when it was like that when you got there

Main menu

Post navigation

ORACLE Transactions and Fishing on the Underground

It’s that time of year again. Yes, it is the season to be snotty.
“Man-flu”, was Nurse Debbie’s considered medical opinion. Admittedly, she’s feeling a bit under the weather herself and, as we all know, “Bird-flu” is a far more serious condition.

I think I must have picked up this particular bug during my daily commute, which currently involves quite a lot of time on the Tube.

In order to pass the time in the morning crush that is the Northern Line, I’ve taken on a challenge from Simon.

He claims that, apart from St. John’s Wood, there is no other tube station that does not contain at least one letter from the word “Mackrel”.

Whilst this may seem a somewhat esoteric fact, it’s probably quite appropriate to look for bits of fish whilst wedged into a Tube train like a sardine.

The tube map itself includes station on the Overground Network as well as the DLR so, ironically, this does provide a bit of “wiggle-room” for my Mackrel search.

All of which serves to act as an example in the following exploration of how Oracle transactions work…

RDBMS Transaction control

I’ve found that developers coming from other RDBMS to Oracle often have a bit of a problem getting their heads around the concept of a transaction.
There is a fairly simple explanation for this.
Many Databases, such as MySQL and Teradata, default to treating single DML statements as a complete transaction – i.e. you issue the statement and the change is implicitly commited.
Of course, if the RDBMS is ANSI compliant, the facility to switch to ANSI standard transactions is available. In Teradata for example, you need to switch from TERADATA Transactions to ANSI transactions. With MySQL, you need to use INNODB.
In Oracle however, the reverse is true. ANSI style transactions are the default.
Time to create a table for use in our examples…

So, we now have a table to hold the station name and a flag value (fishy) to indicate whether or not the station name contains any letters from the work “Mackrel”.
Yes, we could write some code to populate the second column automatically, but where’s the fun in that ?

Ooops, I didn’t mean to do that

Suppose one morning, the combination of excess caffeine and asphyxiation from a particularly crowded train get the better of me and I suddenly believe that BANK does not infact contain any trace of “Mackrel”…

It doesn’t matter what database object you run the DDL for, ANY DDL statement will implicitly commit any pending transactions in the session.

Nothing up my sleeve…

That explains the way that Oracle handles transactions…but how does it maintain two different views of the same data simultaneously – one with the uncommited changes and one with the data as it exists prior to those changes ?

When you issue a DML statement, the changed data block is written to an internal memory structure called the Redo Log Buffer (unless you’re playing around with DIRECT PATH INSERTS that is).
When you query the changed table in your session, you are actually reading data from the Redo Log Buffer.
At the same time, a copy of the original data block ( i.e. before the change) is written to a Rollback Segment in the UNDO tablespace. A query issued from any other session will look at the Rollback Segment. This is how you get two different versions of the same table at the same time.
Once the change is commited, the Rollback Segment is cleared down and the Redo Log Buffer is flushed to the Redo Log file ( by the LGWR background process). At this point, the change is made permanent.

It is possible to generate separate concurrent transactions in a single session using AUTONOMOUS TRANSACTIONs, but in the main, you tend to find that most Oracle applications work in the way I’ve described.

That’s quite enough of my fishy journey to work. Off to try fishing for a bit of sympathy instead.
Now, where did I leave the paracetamol ?

3 thoughts on “ORACLE Transactions and Fishing on the Underground”

Having passed the tube station mackrel conundrum onto my work colleagues it seems that Hoxton station is the only other solution to the problem we could find but on further checking that does seem to be an overground station on the tube map.