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…

CREATE TABLE stations(
    station_name VARCHAR2(50),
    fishy VARCHAR2(1))
/

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 ?

A simple transaction

Now lets add some data to our table …

SQL> INSERT INTO stations(
  2  	station_name, fishy)
  3  VALUES(
  4  	'EUSTON', 'Y')
  5  /

1 row created.

SQL> 

At this point, the record has not yet been written to the table. Don’t believe me ? OK, well try this…

First, query the table in the session in which you’ve just issued the insert statement :

SQL> SELECT * FROM stations;

STATION_NAME					   F
-------------------------------------------------- -
EUSTON						   Y

SQL> 

Well, it’s there, isn’t it ?
Yes it is…in this session.
Before we go any further, check the id of this session…

SQL> SELECT sys_context('userenv', 'sessionid') FROM dual;

SYS_CONTEXT('USERENV','SESSIONID')
--------------------------------------------------------------------------------
110257

SQL> 

Now open a completely new session ( if you’re not sure, you can check the session id again to make sure it’s unique) – whilst keeping the original session open …

SQL> SELECT sys_context('userenv', 'sessionid') FROM dual;      

SYS_CONTEXT('USERENV','SESSIONID')
--------------------------------------------------------------------------------
110259

SQL> 

Now do the same query on the table …

SQL>  SELECT * FROM stations;

no rows selected

SQL>  

In order to make our INSERT permanent, we need to return to the original session and …

SQL> COMMIT;

Commit complete.

SQL> 

If we now re-query the table in the second session…

SQL> SELECT * FROM stations;

STATION_NAME					   F
-------------------------------------------------- -
EUSTON						   Y

SQL> 

Just what is going on ? I’ll go into the details in a bit. In the meantime, the point is that DML statements in uncommited transactions are visible ONLY to the session in which they are issued.

It’s worth noting that, if you terminate the session without terminating the transaction, the default behaviour is that the changes will be discarded.

Let’s add another row. This time we’ll issue the commit at the end to make sure that the change is saved…

SQL> INSERT INTO stations(
  2  	station_name, fishy)
  3  VALUES(
  4  	'BANK', 'Y') 
  5  /

1 row created.

SQL> COMMIT;

Commit complete.

SQL> 

Any sessionin the database can now see the new record…

SQL> SELECT *                
  2  FROM stations
  3  WHERE station_name = 'BANK';       

STATION_NAME					   F
-------------------------------------------------- -
BANK						   Y

SQL> 

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”…

SQL> UPDATE stations 
  2  SET fishy = 'N'
  3  WHERE station_name = 'BANK';

1 row updated.

SQL> 

…before realising my error. Fortunately, I haven’t commited the change yet so, rather than having to issue another UPDATE statement to correct my error, I simply need to ROLLBACK the transaction…

SQL> ROLLBACK;

Rollback complete.

SQL> SELECT *          
  2  FROM stations
  3  WHERE station_name = 'BANK';

STATION_NAME					   F
-------------------------------------------------- -
BANK						   Y

SQL> 

The SAVEPOINT

It’s also possible to rollback part of a transaction in Oracle, by means of the seldom used SAVEPOINT feature….

SQL> INSERT INTO stations(
  2  	station_name, fishy)
  3  VALUES(
  4  	'ST JOHNS WOOD', NULL);

1 row created.

SQL> SAVEPOINT sure_about_this;

Savepoint created.

SQL> UPDATE stations
  2  SET fishy = 'Y'
  3  WHERE station_name = 'ST JOHNS WOOD';

1 row updated.

SQL> SELECT *
  2  FROM stations
  3  WHERE station_name = 'ST JOHNS WOOD';

STATION_NAME					   F
-------------------------------------------------- -
ST JOHNS WOOD					   Y

SQL> 

At this point we realise that we want to discard the UPDATE statement but to keep the original INSERT…

SQL> ROLLBACK TO SAVEPOINT sure_about_this;

Rollback complete.

SQL> COMMIT;

Commit complete.

SQL> SELECT *
  2  FROM stations
  3  WHERE station_name = 'ST JOHNS WOOD';

STATION_NAME					   F
-------------------------------------------------- -
ST JOHNS WOOD

SQL> 

A note on DDL statements

There is more than one way to commit a pending transaction. Issuing a DDL command will cause an implicit commit of any pending transactions in your session.
For example, in session 1…

SQL> UPDATE stations
  2  SET fishy = 'N'
  3  WHERE station_name = 'ST JOHNS WOOD';

1 row updated.

SQL> ALTER TABLE stations MODIFY station_name NOT NULL;

Table altered.

SQL> 

If we now look in session 2…

SQL> SELECT *
  2  FROM stations
  3  WHERE station_name = 'ST JOHNS WOOD';

STATION_NAME					   F
-------------------------------------------------- -
ST JOHNS WOOD					   N

SQL> 

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 ?

About these ads

3 thoughts on “ORACLE Transactions and Fishing on the Underground

  1. 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.

  2. Good work. Though I have been in the IT feild for 20 years didn’t know the reason for commit after inserting a record. I did it as a habit.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s