|
|
 Blogs
Toad World blogs are a mix of insightful how-tos from Quest experts as well as their commentary on experiences with new database technologies. Have some views of your own to share? Post your comments! Note: Comments are restricted to registered Toad World users.
 Do you have a topic that you'd like discussed? We'd love to hear from you. Send us your idea for a blog topic.
Jun
5
Written by:
StevenFeuersteinTW
Tuesday, June 05, 2007 6:16 AM
Qusefuls #1 and #2 were published on Steven Feuerstein's personal blog at feuerthoughts.blogspot.com.
About Qusefuls
A Quseful is a Quick and Useful (as opposed to Quick and Dirty) tip on now to write programs in the Oracle PL/SQL language more effectively. I originally published Qusefuls on my blog (feuerthoughts.blogspot.com), but am now switching the "home" for Qusefuls to my ToadWorld blog. Each Quseful contains a description of the tip ("What's the point?"), some PL/SQL code you can install in your environment to implement the tip ("Show me the code!"), an example of how to use the code to help you get your job done ("How do I use it?").
What's the point?
First, here is my recommendation to you:
Never call COMMIT or ROLLBACK; directly in your code. Instead, call a program that will do the commit for you, and design that program so you can dynamically turn commits/rollbacks on and off, without changing the application code.
Why would I say that? Because a COMMIT; in your code is an example of hard-coding, and as we all know, hard-coding is bad. "Hard-coding?" you ask, "What is Steven talking about?"
Everyone knows what hard-coding is: you put a literal value directly in your code instead of "hiding it" behind a variable, constant or function name. As in:
IF l_employee.salary > 10000000 THEN
must_be_ceo ();
END IF;
And we all know that this is a bad thing to do; even if it doesn't seem as though that literal value could ever change, we know that it will, and then we have to track down every occurrence of the number and change that. So that's the easy part of hard-coding. The hard part is recognizing all the different kinds of hard-coding that can appear in your code. For example, I suggest to you that every time you write COMMIT; or BOLLBACK; in your code, you have hard-coded the transaction boundary. That is, once you commit, you cannot undo your changes. And once you rollback, those changes are gone. Lost forever.
"Well, duh!" you are likely thinking. "That's the whole point of those statements. Now you are just being silly."
Not at all. This is one of those situations that seem so clear at first glance, but upon closer inspection, one realizes that it is a bit more complicated. Suppose I have created a program to adjust the popularity ratings of my company's products, partitioned by gender. The specifications for this program call for a commit, so I write the following:
PROCEDURE adjust_ratings (gender_in IN VARCHAR2)
IS
BEGIN
.... execute many queries and DML statements ....
COMMIT;
END adjust_ratings;
It is then time to test my program (which I will do with Quest® Code Tester for Oracle). I must write the code to set up the various tables on which the program depends (and to which it writes). Some of these tables have hundreds of thousands of rows of data, so it is not at all practical to load it from scratch each time. In fact, what really makes the most sense is to be able to run her program, look at the changes to the tables, and then (assuming something is still wrong) issue a rollback after running adjust_ratings to return the state of the data back to its starting point.
No problem! I just go into my code and make this change:
PROCEDURE adjust_ratings (gender_in IN VARCHAR2)
IS
BEGIN
.... execute many queries and DML statements ....
-- Don't commit while testing COMMIT;
END adjust_ratings;
Now I can run my tests, rollback, run some more tests, without having to go through an elaborate, time-consuming setup process. And when I have fully tested the program and is sure it works? I change the program back to its original state:
PROCEDURE adjust_ratings (gender_in IN VARCHAR2)
IS
BEGIN
.... execute many queries and DML statements ....
COMMIT;
END adjust_ratings;
So let's recap those steps:
1. Write program.
2. Modify program for testing.
3. Test program until you are sure it works.
4. Then change the program.
What's wrong with this picture? You are not supposed to change your code after you finish testing! Sure, it's not a big deal to comment out and in the COMMIT; statement, but what if there are dozens of such statements in your code? How will you make sure that have changed them all?
Oh and as for "commit as hard-coding," do you see now what I mean? It seems so unambiguous at first, but once we look at the requirements for testing one's code, that inflexible transaction boundary becomes an obstacle. Sometimes we want the commit to take place, but at other times, we'd really rather it didn't do the commit.
So what should you do, instead? Call a program to do the committing for you. I have written such a program, in the my_commit package.
Show me the code!
Sorry, rather than show you all the code here (very clumsy), I offer the source code and any supporting files in this zip file.
You can also download my entire "demo zip", containing all the scripts and reusable code that are part of my regular trainings. The zip for this Quseful is inside that zip as well.
Here are the files in the Quseful3.zip:
my_commit.pks - the my_commit package specification
my_commit.pkb - the my_commit package body
Q##MY_COMMIT.qut - a Quest Code Tester test definition export that you can import this into an installation of Code Tester, in order to confirm for yourself that my_commit works as advertised.
How do I use it?
To take advantage of my_commit, I would change my procedure as follows:
PROCEDURE adjust_ratings (gender_in IN VARCHAR2)
IS
BEGIN
.... execute many queries and DML statements ....
my_commit.perform_commit ();
END adjust_ratings;
By default, committing is enabled, and perform_commit will do the commit; here is the implementation of this utility:
PROCEDUREperform_commit(context_inINVARCHAR2:=NULL)
IS
BEGIN
trace_action('perform_commit', context_in);
IFcommitting()
THEN
COMMIT;
ENDIF;
END;
It contains a built-in tracing facility that you can turn on to "watch" commits. But the main thing is the conditional statement that only commits when the package setting is enabled.
So when I test my code, I can disable saving and then run the program. Shown below are the steps inside SQL*Plus. Check out the test definition export in the download zip to see how this is done in Quest Code Tester setup logic.
SQL> exec my_commit.turn_off
SQL> exec adjust_ratings ('MALE')
And after I am done analyzing the results, I can simply rollback and test again.
Do you like it? Do you use it?
I'd love to hear what you think of this utility and, in particular, if you found it useful. So please don't hesitate to post a comment on this blog or send me a note at steven.feuerstein@quest.com.
6 comment(s) so far...
Re: Quseful #3: Don't put COMMIT; in your code!
Hi Steven,
I would even go that far to prohibit the use of COMMIT or also a call to a commit procedure in regular PL/SQL business functions.
Because COMMITs in normal methods always "fire back", eg. if you call them from Oracle Forms/Oracle APEX you don't expect that they destroy your transaction. In that tools you have different transaction bounders.
Only "main" programs, like a PL/SQL batch job or Oracle Forms/APEX/Java Service Layer which calls the different business functions are allowed to issue a commit or rollback, because they define the transaction bounders.
Just my thoughts.
Patrick
By PatrickWolf on
Friday, June 08, 2007 4:51 PM
|
Re: Quseful #3: Don't put COMMIT; in your code!
Great theory.
I'd question the effect on performance, though that would certainly depend on how often it was being called, eh?
By stews on
Tuesday, June 12, 2007 9:21 AM
|
Re: Quseful #3: Don't put COMMIT; in your code!
Patrick:
Fine thoughts. I agree that it should be very unusual indeed to actually want or need to do a commit inside business logic in the server.
Stews:
You are concerned about the overhead of calling a procedure to do a COMMIT? I really doubt that would be much of an issue. The time it takes to do a commit would I imagine generally overwhelm the tiny amount of cycles need to call the procedure.
By StevenFeuersteinTW on
Tuesday, June 12, 2007 1:01 PM
|
Re: Quseful #3: Don't put COMMIT; in your code!
This is great! Thanks for sharing this.
By bobh on
Monday, June 18, 2007 8:33 AM
|
Re: Quseful #3: Don't put COMMIT; in your code!
For the minimal number of simple repeatable one person-one session tests for which this approach is valid for the risks are unacceptably high.
Espousing this technique as "a quick and useful best practise" is dangeous and irresponsible.
Fundamentally it violates the golden rule of testing:
"What you test should be the exact code that you will execute in production."
The location and timing of commit is fundamental to the transactional design of a system. By subverting that transactional design by simply not committing you risk a catalog of consequences: reporting phantom bugs, triggering false exceptions, masking real exceptions, altering results, phantom locking and deadlocking problems:
(1) Imagine there is a commit located in the code which is actually a bug.
This could be either:
(a) because it will cause an actual failure by exception in a trigger body, or in a procedure or function called from a trigger. By setting my_commit.turn_off() in this session you will fail to spot this obvious bug.
(b) because it breaks up a logical transaction.
Error conditions that need to be tested, spotted and resolved are missed because the badly positioned commit is suppressed.
(2) By masking commits in autonomous transactions it is possible to create either phantom bugs (that exist only because the commit is missing) or fail to spot real bugs. For instance a triggered autonomous transaction (maybe to avoid mutating tables)
(3) Background job scheduling
Imagine a process that schedules background jobs on the fly, maybe for some manual concurrency.
Obviously by not committing, the jobs will not be scheduled.
This process is firstly untestable, and secondly likely to cause functional knock-on effects to dependent processes. For instance there may be housekeeping jobs monitoring data changes based on expected background job activity. The behaviour of these jobs is obviously going to vary wildly from how they would actually behave were these background jobs becoming scheduled appropriately.
(4) Locking
The locks not released because you didn't commit are going to mean that many test scenarios just can't actually be tested.
It's also possible to produce deadlock situations that don't exist in reality.
(5) Accidental production disasters.
Imagine the scenario where my_commit.turn_off() call finds its way into a production session because some dodgy development left over that was poked into some unusual branch that is very infrequently invoked.
Now imagine how difficult to spot that could be if for instance one session in a connection pool has this function set.
(6) Malicious attacks on production.
Imagine a massively concurrent huge throughput database storing millions of vital, expensive, tightly regulated financial transactions.
Just 10 or 20 seconds with my_commit.turn_off() could take down an organisation.
(7) You end up with the unecessary package "my_commit" being released into the production environment so that your code compiles. That's just nasty.
(8) Finally the "iterate_and_commit" procedure that appears in the example code frightens me.
Surely the last thing we need is for respected practicioners to be endorsing this kind of newby cruft:
IF g_commit_after <= g_counter AND g_commit_after > 0
THEN
perform_commit (context_in);
init_counter;
ELSE
g_counter := g_counter + 1;
END IF;
By HansAnderson on
Thursday, June 28, 2007 3:56 AM
|
Re: Quseful #3: Don't put COMMIT; in your code!
Thanks, HansAnderson, for your lengthy comment. By the way, in your number 8, I am sure we would all appreciate hearing what you think makes the code shown to be "newby cruft."
Oh, and what's "cruft"?
As for all or you critiques, I believe pretty much all of it can be applied to the situation that I was working to help people avoid: going in and commenting out commits.
Having said that, yes, there certainly is a danger to making the commit something that can be turned off with an API call - but if your API is not secure, you have bigger (or at least as big) problems in your environment. No end user is ever going to be able (you hope) run my_commit.turn_off. how would someone ever be able to do that in a secure environment - and KNOW that it was possible to do.
Ah, there's a thought: if you want to use my_commit in your environment, CHANGE THE NAME OF THE PACKAGE. Name it something like
wrrt7uu8.blipblap
and then a hacker could never guess what it is going to do.
SF
By StevenFeuersteinTW on
Tuesday, September 02, 2008 12:05 PM
|
|
|