Hi Tom,

One question.

This is the process the application is currently doing.

During Order creation, Java code populates the user data into result bean and each result bean is mapped to the database tables. During Order check out, the individual stored procedure is called and the database insert/update is done and committed at each stored procedure level.

But logically the transaction should be committed only after all the stored procedure calls are successful with out any errors.

Also the stored procedure (back end) is returning the unique id (sequence no) back to the client at every stored procedure completion.


My questions are :
==================

Say i call 5 stored procedures from Java code. Each stored procedure is creating records in some tables. In case, if the 5th stored procedure call fails (due to some reason), i have to rollback all the records upto the point of time.

Can i do this ?

I will remove the commit at the stored procedure level. The individual stored procedure is returning success or failure back to the caller in JDBC.

I want to control the commit at the Java code level.
If all the stored procedure call is successful then i will do commit at the Java side else rollback all the records since begining of the transaction.

Do you see any drawback with this approach.

Please advice .

Thanks

In fact

Hi Tom,
Good day please suggest on this. In the project I am working on the procedures in database are called from servlets using jdbc , auto commit is off and the commit/rollback is done in procedures after successful/failure of transaction. What is your suggestion should the commit be always issued from servlets or front end technology used or if done in procedures for complete transaction it is ok and what is the best way of these.

Thanks as always.

Regards,
Vijay

Hi Tom,

We have a single transaction which will insert 40M+ rows into a table (6 indexes associated with it) and it needs 50GB+ rollback segment. Also, there may be more rows in the future.

Here is my question.

What's your suggestion about the maximum rollback segment usage per atomic transaction? It couldn't be unlimited, right?

Thanks

I have an issue. My script updates a table and commits every 15 minutes. However, after about 75% of the update is complete, the script failed. When I rerun the script, I don't want to do the updates that were already committed. How would I do it? I am sure you have some ideas in the way I have to code to avoid running the updates that were already committed. could you please help? thanks.

Tom,

Quick question on commits. I know that commits are synched to the LGWR process and hence are bad not only from an overall process flow but bad from a perfomance point of view too. Do savepoints also synch to the LGWR process and hence suffer performance hits?


Also in a web application like asktom (mod_plsql). Would you rely on mod_plsql doing the commit (it bieng the client) or would you do it in your top most procedure?

Regards.

If using jdbc and setting autocommit off. Then running a pl/sql procedure that passes success or failure back to the app so the app has transaction control over commit/rollback.

What happens though if the transaction fails and sends failure back to the app which is then supposed to send the rollback command to the db but what if the connection is severed before this rollback command can get to the db? Does the db do a commit? If so is this not a bad thing?

Tom,
A user went to asktom and found out how to delete the 68 duplicate rows out of the 47million row partitioned table. He managed to "lose" his connection to the database while the delete was running. Now he wants to know if he can issue a commit to that SID/SERIAL# from another session. Is that possible?

What do you think of this, Tom? </code> http://forums.oracle.com/forums/thread.jsp?forum=137&thread=288319&tstart=0&trange=15 <code>

Apparently we can't have full control over commits in HTML DB. Is there something I am missing - something fundamentally wrong with spanning a transaction over multiple pages? Is doing this outside the scope of HTML DB's intended purpose? Frankly, I was rather disappointed to read about this.

Interested in your opinion.

Thank you!
Mark

Thanks for the response. If I may play devil's advocate...

I feel it would be worth the overhead of having a persistent session. If I'm not mistaken, the web server would simply keep track of the user via a cookie, and map that cookie ID to the corresponding Oracle session ID. Once that's taken care of, each user would interact with the database just as if they each had SQL Plus open, only with a pretty GUI.

Having a session per page precludes:

- splitting up of really long forms into several pages without doing tricks - e.g. my form takes up 4 pages - I would like to have page 1 do an insert, then pages 2 and 3 do an update on the newly inserted row, and finally page 4 does an update and a commit (or a rollback if the user so chooses). Maybe they can even go back to page x and edit the previous info (another update). I just feel this would be more natural and elegant than something like asktom's transaction cache.

- use of neat Oracle features like on commit temporary tables, deferred constraints, MVs that refresh on commit - all are essentially confined to transactions on a single page

- It seems I have to use owa_opt_lock to control concurrency (thanks for pointing that out - I like to have most functionality in packages, so I'm not using HTML DB update methods)

So basically I feel like the user interface is imposing limitations on what I can and cannot do in the database, and that avoiding these limitations would be worth the session state overhead. Obviously others - who are probably more knowledgeable - disagree, but maybe it would be worth having the option, at least.

Thanks for hearing me out.
Mark

Well I just had a chat with a friend that made me change my mind. He isn't that experienced with Oracle, but he has a lot of web development experience, and just understands the concepts well. I'll post our log below, in case anyone is interested:

Friend: hmm..I like both options :-) but I think you are delving into features of HTMLDB that I don't know enough about to give a proper answer

Friend: though, I think I prefer his method..storing the data into a session state table, and when the user commits it takes the data out and moves it to the final destination

Friend: hmm..does the method he mention not work with packages?

Mark: the reason I like my method is you don't even need a session state table..you just update and insert into the "live" table, and until you commit it's like you have your own different version of the table - nobody else can see your changes until you commit

Mark: which is actually the standard way to do this stuff, but they imposed these limitations simply because they wanted to not keep track of a state, since they're using html.

Friend: true, but say the user jumps to a totally different area of the site, would that affect the transaction for that page? and how long would it take for the transaction to be cleaned up if the user never goes back to the page?

Mark: you could have it automatically rollback if you jump out of that section..like in my example of the four page form, you'd just do a rollback if you exited those four pages...rollback time depends on how many changes you make, but if you're just adding a few rows here and there it'll be close to instantaneous

Friend: but how would you signal the web server that you jumped out of that section if the user say hit the back button

Mark: hmm yeah that's true..would kind of depend on people using the links provided and not using the back button, or typing in random urls on their own..hmmmm

Friend: yeah

Mark: well then I'd say that anytime you start a new transaction, you do a rollback first...so if you hit the back button, you'd still have uncommitted changes, but if you ever go into another area where you are capable of committing (a separate 4-page form let's say) then your changes get rolled back...if you jump to page 3 in that second form, since you didn't insert the row in the first place (in form 2, page 1), you'd get an error, which could be handled gracefully - it would jump you to form 2 page 1, which would first do a rollback then begin that next transaction

Friend: then say you have two browser windows open, by say clicking a link while holding the shift down, at that point both windows would be using the same session..the second window could end up canceling the transaction of the first window

Mark: hmmm then maybe autonomous transactions would be a good solution, cause using those you can kinda define "sub" transactions which only get committed or rolled back from within that transaction...so each time the form was opened i'd start a separate autonomous transaction...only problem then is your uncommitted changes would hang around till you logged off or expired, which isn't a big deal as long they don't get huge

Friend: but how would you determine which form is actually being sent from the user? window 1 or window 2?

Friend: and if that is being used on the internet, you basically just gave people an easy way to take down your server :-)

Mark: i guess the name for the transaction would have to be generated and somehow associated with the page...maybe a hidden field...i see what you're sayin about people taking down the server...hmm

Friend: but even in a hidden field, you open a new window to the same page in the same session, that hidden field is the same too :-)

Friend: now granted that scenario might not be a big deal, and if you can recover easily from that no loss..but something to remember

Friend: which is kinda why I'm leaning towards the method asktom mentioned..as it will handle all these scenarios

Friend: including expiring the old data without you having to worry about it

Mark: well the hidden field would be the same in the new window if you already started the transaction, but if you opened page 1 in a new window - and so you had a new transaction begin there - you could have the two separate forms going without interfering with each other

Friend: true, that would at least take care of one possibility

Mark: but i was just thinking in the bathroom, I don't think you can have multiple autonomous transactions, nor can you give them names.

Friend: well thats a different problem :-)

Mark: if you can't do that then you can't do what I was saying, pretty much :-)

Friend: the other advantage to the method asktom mentioned is say you have 2 web servers, both hitting the same database, with no transactions spanning pages a load balancer could easily just direct the user to either server without worries

Friend: that probably doesn't affect you, but could later

Mark: ah well guess that's probably the way to go..not that I have a choice anyway. just didn't really understand why we were forced to do it that way :-)

Let's say one day, your you and 9 other coworkers decided to order out for lunch. You're nominated to get the food. There are 3 restaurants they want you to go to. Would you try and remember what each one wanted or would you write it down and, when you got to each restaurant, recite what was wanted from that restaurant?

You and your coworkers are the people logged into the system doing things. The restaurants are the different actions each user wants to do and the orders are the information that each action needs to complete. The paper represents a database table and your head represents sessions/open transactions (i.e. RAM/CPU).

In both situations, its easier to record the information and retrieve it when its needed. Takes up less resources and can be even more flexible than storing all the information in memory. A person could log in, do pages 1 and 2, logs off, the database could crash (and be recovered) and the user could come back and do pages 3 and 4 and then commit the transaction. With sessions, if the database crashed between pages 2 and 3, the user has to start all over

Thanks Tom, I am convinced this is the way to go now. About the first point, I had misunderstood what you were saying about the session state -- I thought we had to do some kind of trick using a table that is not a temporary table, but is essentially used as one (mapping session IDs to temporary data, deleting the right data at the end of transaction.) I get it now. Should have showed your post to the HTML DB guy here.

I was still confused about the second point for a moment, but then the light bulb went on... In pages 1 through n-1 you're just collecting info and putting in HTML DB's session state. Then the transaction simply begins and ends after the page n is submitted.

Thanks again for the awesome assistance.

Hi Tom,
This may sound like a stupid question, but I just want to know if it can be done. Can we have users who are allowed to change data in their particular session but not allowed to commit the changes?

Just Curious.

Tom,
I have 2 questions when I read your article, "Locking and Concurrency - Part1" on dbazine.com.

1. "You should defer committing as long as you have to. You should not do it quickly to avoid stressing the system, as it does not stress the system to have long or large transactions......"

I don't quite understand why long & large transactions
does not stress the system, but frequently committing will stress the system. what statistics tell me that?

the long & large transactions require huge rollback segments which will stress the system. In my batch system, if a long & large transaction
failed(rollback seg is too small), I lost a coup of hours, say 3 hrs, if I split the long&large transaction into two(if I can), I would save 1.5 hr if sencond transaction failed. In a time contraint batch system, should I avoid long and large transactions?


2. "If all of your applications use optimistic locking then using a straight UPDATE is generally OK, rows are locked for a very short duration as updates are applied and committed..... "

If doing so, I still have chance to lose the update, right?
Why suggest so?


Thank you for your advice!

Steve

"RESTARTABLE (another concept I cover in detail in the book.."

which chapter in which book? I have your "expert one-to-one" and "effect oracle design"

Thanks!

hi
This is sri. I have a query related stored procedure call in JDBC.
My application needs that i have to retrive the data for every 5 minutes from the database.How it is possible?

In your first book, you have explained that "commit" statement takes the least time to get executed.
It does the least work; all our work is done prior to the commit.
Yes, I did see it for a long time.
But, recently, I saw commit taking few (5-10) seconds.

The client is Toad on win-2000 professional.
Database & instance ( Oracle 8.1.7 ) on win-NT-4.

There were 12 Legacy instances of various offices on different Win-NT machines each using oracle 8.0.5.

We are now putting all the Legacy data at one place.
I have decided to use FGAC-policies to replace those instances.
I have imported all of them in 12 dummy users of a single instace in Oracle 8.1.7.
I am copying data from those users to a real user with (RO_Name) (Reg-office-name) as an added column.

All the target tables are having NO-Logging enabled.
All the tables have only BitMap idexes (1 each) on the column RO_Name; i.e., no other index except these BitMap-ones.

For each dummy user, I do this in a batch, before giving commit; like

INSERT /*+ APPEND */ select t1.*, 'RO1' from RO1.Table1 t1;
INSERT /*+ APPEND */ select t1.*, 'RO1' from RO1.Table2 t1;
...
INSERT /*+ APPEND */ select t1.*, 'RO1' from RO1.Table249 t1;
INSERT /*+ APPEND */ select t1.*, 'RO1' from RO1.Table250 t1;

COMMIT; -- This takes 5-10 seconds.

In each batch inserts (each transaction),
total no. of rows inserted are nearly 100000 to 500000.

(1) I am using Toad since last 3 years.
This happened only these times.
In all other cases, commit did not take
more than few ms (say 10 to 100 ms).
There are no other users using this instance;
it is still being build up by only one person
(i.e., me of course).

(2) While refreshing "this" page from the history (IE-6),
I got the following error; just curious to know why :

ORA-01400: cannot insert NULL into ("ASK_TOM"."WWC_ASK_QUESTION_ACCESS_LOG$"."DISPLAYID")

(2) While refreshing "this" page from the history (IE-6),
I got the following error; just curious to know why :

Sorry, not IE-6; it is Mozilla FireFox 1.5.0.1

(3) One more point :

Do you accept (and reply) queries via e-Mail ?
In the office, we do not have internet access nearby.

Yes, you are right.
SQL*Plus took quite a small time.
I wonder what toad might be doing for such a simple thing like "commit".
I know, toad does take lot of time while SELECTing - it must be formatting the output. (Whenever I check with Windows' Task-Manager, it shows 99 % CPU usage for SELECTing)

But, what about commit ? So, I thought there might be something in my tables, indexes, session-environment that was wrong. Since I did not see such thing for 3 years, I doubted other things. Like, recently I changed from oralce 8.0.0 to 8.1.7, etc.

Thanks for the reply.

We have mail.
We have network.
But, the internet is allowed from only few PC's; and they are not close.

I think I know the answer to this but want to confirm. Our application defaults to autocommit mode (some methods turn it off when there are multiple updates). My question is whether autocommit will result in a performance hit when only selects are being performed.

My guess is that it does not result in a performance hit since there are no updates to be written out to the redo logs.

Is my assumption here correct? What I am trying to determine is if we have a potential problem with performance due to log file syncs when only selects are being performed and autocommit is enabled.

Respected Mr. Tom,

Right now I am quite in a deep trouble. I need your help. We are using Oracle 9i and Sun Solaris – 5.8 and Pro*C program. Through this program, we Update and Insert more than 2000 records. And at the same time, so many other Pro*C programs are also running to update and insert to Database concurrently.

So we implemented incremental commit. Now the current logic in the program regarding the incremental commit strategy does not provide a clean rollback solution in the event of an error. The logic needs to be modified to have either a single commit, or some other check pointing strategy, such that in the event that an error occurs, the loader can issue a rollback command and be assured that all new and/or updated records have been rolled back.

1> What type of problem do you envisage if I use single “Commit”? I am afraid, if rollback space runs out of space in the light of my pro*c program and other programs executed concurrently in the production server.

2> Can we use “Savepoint” in Pro*C but I don’t believe in it because it can’t surmount the rollback space problem. Anyway, if it is the only viable solution in this case, please suggest me and please write me two lines pro*c code using “Savepoint”. Otherwise you please suggest me the best method.


Regards,
Arindam Mukherjee

We've used commits and rollbacks in our stored procedures from the start, the reasoning being that they have been created as self-contained transactions.

Our applications have access to the database only via PL/SQL procedures, and the procedures/applications have been designed in such a way that the apps don't have 'transactions' that span multiple procedures.

Example procedures:

checkout_publication: Checks out (as in version control) a publication to a given user. (The CHECKEDOUT_BY column on the PUBLICATION table is set to the user name)

modify_publication: Modifies some columns of a given publication.

The worry has also been that if commits/rollbacks are left to the calling application (VB and other windows apps), what happens if an application 'forgets' and open transactions and locks are left lingering.

For example, in the checkout_publication procedure, there is a SELECT FOR UPDATE. What happens if that transaction is not appropriately ended by the calling program?

So I am not clear on why the calling applications should do the commits/rollbacks if the stored procedures are built in the way I've described above.

Can you perhaps expand on the costs/benefits of letting the application do the commits/rollbacks so that I can make a case to my colleagues?

Thanks.

Hi tom,

I am working on the DW environment. We have a set of jobs which runs every day and load the data. The job calls the SQL files. We are using Oracle 9i.

I have added the “WHENEVER SQLERROR EXIT FAILURE; “ in SQL file to stop the execution if any one of the SQL script fails. So I can fix the error and rerun the same SQL file once again. (We provide commit in the end)

But I am facing one issue here. The script1 and script2 is loading the same t_hist table. I need commit after the completion of the both the statement. If the script2 fails I fix the issue and I will rerun the same file. 

The below scenario is not doing the rollback.  I need your guidance here. 

SQL> WHENEVER SQLERROR EXIT FAILURE;
SQL> create table t_hist ( x number(5), y number(5));

Table created.

SQL> create table t ( x number(5), y number(5));

Table created.

SQL> insert into t values ( 1,2);

1 row created.

SQL> insert into t values ( 1,4);

1 row created.

SQL> insert into t values ( 1,6);

1 row created.

SQL> insert into t values ( 1,8);

1 row created.

SQL> create table t1 ( x number(5), y number(5));

Table created.

SQL> insert into t1 values ( 2,2);

1 row created.

SQL> insert into t1 values ( 2,4);

1 row created.

SQL> insert into t1 values ( 2,6);

1 row created.

SQL> insert into t1 values ( 2,8);

1 row created.

SQL> commit;

Commit complete.

/* ---------------- Script1 --------------------- */

SQL> insert into t_hist
  2  select * from t;

4 rows created.

/* ---------------- Script2 --------------------- */

SQL> insert into t_hist
  2  select x from t1;
insert into t_hist
            *
ERROR at line 1:
ORA-00947: not enough values 


The script1 has loaded the 4 records and script2 has failed. So the session will close because of the “WHENEVER SQLERROR EXIT FAILURE;” command.

I have opened the another session and executed the “select * from t_hist”. The first execution has loaded the data and committed the data. 

New session result:

SQL> select * from t_hist;

         X          Y
---------- ----------
         1          2
         1          4
         1          6
         1          8

I did not give any commit statement after the execution of the script1. Still the records got committed. 

My questions:

1.Is it because of the “WHENEVER SQLERROR EXIT FAILURE;” command? Is it auto committing the session after script2 fails?

2.if answer is “yes” for the question #1 . Then how can I over come this one?  My scenario is “I need to commit the data after the completion of the both stmts. Because I need to avoid the duplication while rerunning sql file during the failure”.

Regards,
Karthi

Thanks a lot tom for your reply.

You have suggested writing stored procedures instead of the SQL files. We are dealing with many jobs and each job is having some set files.

Do we need to convert all the SQL files to stored procedure? Do you have any other suggestion for me?

Could you tell me what are all the draw backs if we change the default value (sqlplus by default COMMITS on exit) and Could you tell us how to change the default vale. This is for my learning purpose.

Regards,
KArthi

Hi Tom,

I have created the stored procedure to implement that one. Thanks a lot for your guidance. I have learned one good practice from you and I will not change the default settings.

I found another two ways also. Those methods are working fine. But I did not implement it.

Method 1:

Given the SQL scripts with in BEGIN and END. It is working fine because it commits at the end. It rollbacks if the script2 fails.

BEGIN
/* ---------------- Script1 --------------------- */

insert into t_hist
select * from t;


/* ---------------- Script2 --------------------- */

insert into t_hist
select x from t1;

END;
/

Method 2:

Found the FAILURE ROLLBACK option in the WHENEVER SQLERROR EXIT command.

Tried with below command before executing the scripts. This is also not committing the records if the script2 fails.

<b>“WHENEVER SQLERROR EXIT FAILURE ROLLBACK “</b>

Please provide you comments on above methods (Interested to understand very well about all the concepts)

Once again Thanks a lot for your guidance.

Regards,
Karthi

BEGIN
/* ---------------- Script1 --------------------- */

insert into t_hist
select * from t;


/* ---------------- Script2 --------------------- */

insert into t_hist
select x from t1;

END;
/

Thanks a lot tom.

I have written separate SP. I did not post that. I have asked the suggestion about the other two methods except stored procedure method.

I will follow stored procedure best practice in future also.

Regards,
Karthi

Did you realize that in your script:

create table t_hist ( x number(5), y number(5));
create table t ( x number(5), y number(5));
insert into t values ( 1,2);
insert into t values ( 1,4);
insert into t values ( 1,6);
insert into t values ( 1,8);
create table t1 ( x number(5), y number(5));
insert into t1 values ( 2,2);
insert into t1 values ( 2,4);
insert into t1 values ( 2,6);
insert into t1 values ( 2,8);
commit;

the CREATE TABLE T1 is DDL; DDL implicitly commits. That is why your first 4 inserts were not rolled back.

It is certainly a good thing that you are considering restart and recoverability; unfortunately, many developers do not consider this until an incident actually occurs. But you need to re-think your process, and make sure you have a robust design.

If your process actually includes DDL such as you show here (even at the beginning of the script), you have probably gone down the wrong road.

Hi Michael Tefft,

Thanks for you comments.

I have created tables – t,t1 and t_hist for replicating the scenario. I do not have the DDLs in my real time scenario.

Now I have a robust design with the help of Tom.

Regards,
Karthi

i Just wonder if we inserting 40 M Recordes in table its took around 10 mins to insert, just wann to know when we fire commit it its writing into data block. and immediately finish its executiong. does it mean that it finish writing into data blocks or what internally happend when we fire commit stmt. can you pls brief about it. i am very much curious about to know it.