Geek Answers Handbook

Can I replace savepoints to start new transactions in Oracle?

Currently, the process we use to insert recordsets looks something like this:

(and note that “record set” means something like a person’s record along with their addresses, phone numbers, or any other related tables).

  • Run the transaction.
  • Insert a set of related records.
  • Lock, if everything was successful, lean back.
  • Return to step 1 for the next set of entries.

Should we do something like this?

  • Run the transaction at the beginning of the script
  • Run a savepoint for each record set.
  • Insert a set of related records.
  • Return to savepoint, if there is an error, continue if everything is successful.
  • Complete the transaction at the beginning of the script.

After some problems with ORA-01555 and reading several Ask Tom articles (like this one ), I think of trying from the second process. Of course, as Tom points out, starting a new transaction is something that needs to be driven by business needs. Is the second process worth a try, or is this a bad idea?

+6
oracle oracle10g commit savepoints ora-01555
source share
2 answers

Transaction should be a significant Unit of Work. But what makes up the Unit of Work depends on the context. In the OLTP system, the Work unit will be the only character along with their address information, etc. But it sounds like you are doing some form of batch processing that loads a lot of people.

If you are having problems with ORA-1555, this is almost certainly because you have a long query providing data that is updated by other transactions. Locking within your loop contributes to the cyclical use of UNDO segments, and therefore will tend to increase the likelihood that the segments you rely on to ensure read consistency will be reused. So, not doing this is probably a good idea.

Using SAVEPOINT is a different matter. I do not know what advantage this will give you in your situation. As you work with Oracle10g, you might want to use the voluminous DML error log instead.

Alternatively, you can rewrite the driving request so that it works with smaller pieces of data. Without knowing more about the specifics of your process, I cannot give specific advice. But in general, instead of opening one cursor for 10,000 records, it would be better to open it twenty times in 500 lines of pop music. Another thing to consider is whether the insertion process can be made more efficient, say, using the mass collection and FORALL.

+5
source share

Some thoughts ...

  • It seems to me that one of the asktom link points is the size of your rollback / cancel to avoid the 1555th. Is there a reason this is not possible? As he notes, buying a disk is much cheaper than writing / maintaining code to cope with rollback restrictions (although I had to do a double trick after reading the $ 250 pricetag for a 36 GB drive - this stream started in 2002. A good illustration of Moore's Law!)
  • This link (Burleson) shows one possible problem with savepoints.
  • Are your transactions valid actions 2,3 and 5 in the second scenario? If so, what would I do - make every transaction. Sounds a little to me, since scenario 1 is a collection of transactions collapsed into one?
+1
source share

More articles:


All Articles