Know thy database fundamentals

It’s so important to know your database features and the idiosyncracies of the particular database that you are using.

On a recent project, this point was highlighted by some code designed to feed updated information from database A to database B and back again.

Both these feeds – feed 1 from database A to database B and feed 2 from database B to database A – relied on selecting records from a certain range.

Feed 1 is selecting by last update date. Everytime it runs, it stores the maximum last update date from the records selected and uses that to drive the minimum date when running the next time. So it selects the records with a last update date between that maximum date selected last time and the current time.

 In a similar vein, Feed 2 works off a sequence number. The table primary key of the table that drives this query is a sequence generated number. Each time Feed 2 runs, it stores the maximum sequence number from the range of data selected and uses that the next time it runs. So it selects the records with a sequence number between the maximum one selected last time and the sequence nextval.

Can you see what the problem is?

This affects both these feeds and relates to knowing how Oracle deals with concurrency and read consistency.

With three sqlplus windows each connected as the same user:


FEED@DOM@XE>
create table tab1 (col1 date);


FEED@DOM@XE>select to_char(col1,'DD-MM-YYYY HH24:MI:SS') from tab1;


no rows selected
.....

SESS2@DOM@XE>insert into tab1 values (sysdate);

1 row created.

SESS2@DOM@XE>exec dbms_lock.sleep(50)

.....

SESS1@DOM@XE>insert into tab1 values (sysdate);

1 row created.

SESS1@DOM@XE>commit;

Commit complete.

.....

FEED@DOM@XE>l
1 select to_char(col1,'DD-MM-YYYY HH24:MI:SS')
2* from tab1
FEED@DOM@XE>/

TO_CHAR(COL1,'DD-MM
-------------------
15-08-2007 09:12:26

.....

SESS2@DOM@XE>exec dbms_lock.sleep(50)

PL/SQL procedure successfully completed.

SESS2@DOM@XE>commit;

Commit complete.

.....

FEED@DOM@XE>l
1 select to_char(col1,'DD-MM-YYYY HH24:MI:SS')
2* from tab1
FEED@DOM@XE>/

TO_CHAR(COL1,'DD-MM
-------------------
15-08-2007 09:11:49
15-08-2007 09:12:26

So, in the feed situation I described above, the feed runs, it picks up the row inserted by SESS1, and it stores that date as the maximum date fed. Our slightly slower transaction in SESS2 was doing some more work or waiting on something (simulated by a call to dbms_lock) and will never be picked up. It’s last update date is before that of SESS1. Writers don’t block readers and it obviously wasn’t visible at the time that the feed ran as it was uncommitted. So the feed destination never gets notified.

It’s basic fundamentals at the end of the day.

The good news is that both these feeds are candidates to be replaced by an AQ queue.

About these ads

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

Follow

Get every new post delivered to your Inbox.

Join 68 other followers

%d bloggers like this: