Best Dad In The World

I have many things to be thankful for but two of the most important are the town I grew up in and the parents that raised me there. The town is the idyllic Nelson, BC – a great place to grow up. And my parents allowed me to take full advantage of it including the golf course and its much fabled 7th hole.

It was easily reachable for a potential birdie but the green was cruelly sloped outwards towards a deep sand trap. An approach shot with a high loft was required. My Dad favored more of a low line drive so this hole frustrated him. Off the tee we frequently watched his straight but low trajectory approach land in front of the green and then roll. And roll. And roll…into the sand trap. As a youngster tagging along I would ask “Why are you in the sand trap again?” “It just feels right” he’d reply sarcastically. After a swing or two in the trap I’d ask “Why are you still in the sand trap?” “Just enjoying the view” he’d say with a chuckle. One way or another the ball would find it’s way onto the green and a few putts later we’d be on our way to the next tee.

Once I started golfing I was very focused on learning to hit the ball really, really high so it would stop quickly when it landed near the green. Any green. The 7th green.

Over the years the scene would play out again and again – my ball would soar high, land near the putting green and stay away from the trap. My Dad’s would find the trap more often than not and the familiar conversation would follow. Never in anger. Always in fun.

My Dad passed away in September 2014. My Mom spent the last year and a half working slowly through the grieving process. She decided the holiday weekend this May was the time to say a final good-bye and spread his ashes in remembrance. We discussed many places around town that we associated with my Dad – the lake he enjoyed boating on, the church hall he was always fixing something at, the golf course, the park he enjoyed walking at and the power plant where he worked for 20 years. We settled on the dual theme of water and work and chose the river leading up to the power plant.

So on an overcast afternoon we gathered to say a few words, release his ashes into the river and take a shot of his favorite Rum. Afterwards my older brother mentioned his main memory of Dad was his focus on family and this focus had been passed onto us. This made me think of my daughter who has expressed some interest in trying golf. I can hear it now…

“Why are you in the sand trap again Dada?”

“Just enjoying the view…”

I miss you Dad.

 

Answer: Backout And Constraints

And here are the results. Two modes raised the error during backout and 2 succeeded.

NOCASCADE
Raised Error

NOCASCADE_FORCE
Raised Error

CASCADE
No Error
2 Records Afterwards
DEMO_ID DEMO_NAME
---------- ---------
 2 Demo 2
 1 Demo 1
SQL Executed
delete from DRH.DEMO where DEMO_ID = '1' and DEMO_NAME = 'Demo 1'
insert into DRH.DEMO(DEMO_ID,DEMO_NAME) values ('2','Demo 2')
insert into DRH.DEMO(DEMO_ID,DEMO_NAME) values ('1','Demo 1')
Explanation
Because this is cascade mode it starts with the latest transaction - the re-insert of record 1.
This is backed out with the delete SQL. Then the delete transaction is backed out with 2 inserts.

NONCONFLICT_ONLY
No Error
2 Records Afterwards
DEMO_ID DEMO_NAME
---------- ---------
1 Demo 1
2 Demo 2
SQL Executed
insert into DRH.DEMO(DEMO_ID,DEMO_NAME) values ('2','Demo 2')
Explanation
This much simpler - it just inserts the row with DEMO_ID=2.

How many did you get right?

Thanks for reading!

Quiz: Backout And Constraints

When backing out transactions from the past using undo SQL statement it is important to consider the current data – especially if the undo SQL violates database constraints.

Consider this example scenario that inserts 2 records in one transaction and then deletes them both in a second transaction. Then in a third transaction it re-inserts the first row.

What will happen when we try to backout the deletion (second transaction)?

-- insert 2 records
SET TRANSACTION NAME 'INSERT';
INSERT INTO demo
VALUES(1,'Demo 1');
INSERT INTO demo
VALUES(2,'Demo 2');
COMMIT;

-- delete both records
-- this is the transaction to be backed out later
SET TRANSACTION NAME 'DELETE';
DELETE demo;
COMMIT;

-- insert the first record again
SET TRANSACTION NAME 'INSERT2';
INSERT INTO demo
VALUES(1,'Demo 1');
COMMIT;

Will the backout fail when it tries to re-insert the row with DEMO_ID=1 because it violates the primary key constraint. Will it throw this error message?

ERROR at line 1:
ORA-55511: Flashback Transaction experienced error in executing undo SQL
ORA-00001: unique constraint (ORA-00001: unique constraint (DRH.SYS_C0010742) violated.) violated
ORA-06512: at "SYS.DBMS_FLASHBACK", line 51
ORA-06512: at "SYS.DBMS_FLASHBACK", line 86
ORA-06512: at line 4

It’s interesting to note the extra “violated” at the end of the message.

Is the error message thrown by all 4 modes of backout transaction? How many rows will be in the table afterwards? I’ll give you a hint – its not always 1 row.

DBMS_FLASHBACK.NOCASCADE        = error message? 1 row?
DBMS_FLASHBACK.NOCASCADE_FORCE  = error message? 1 row?
DBMS_FLASHBACK.NONCONFLICT_ONLY = error message? 1 row?
DBMS_FLASHBACK.CASCADE          = error message? 1 row?

In my next blog post I’ll explain the answer.

Thanks for reading.