How often to commit?
sarvanOct 3 2011 — edited Oct 7 2011Experts,
I'm sorry for a big post like this..
We work on a large data migration project where in we need to move data from legacy system database to oracle 11g.
Here is how we do this
1) We have extracted the legacy data as flat files from the database and loaded to _SOURCE tables in oracle
2) Since it's not just table to table copy of _SOURCE data. we have created procedures to perform this data movement.
3) Most of our migration procedure will an explicit cursor to pick records from _SOURCE table
4) with cursor for loop we process one record at a time and insert into _TARGET table
5) We will directly insert the value obtained by the cursor into the target filed or get the value out of a function and insert it. NOTE : We have separate function created for fields where it need to get transformed to a new value
Additional information
a) since we planned to migrate one country and one year data at a time, it's not going to be more than 1,00,000 records copy in one time for any table.
b) No production downtime is allowed during this data migration as users will be using the OLTP application (DB) all 24 hours from different regions.
In the migration procdures, I have placed the COMMIT outside the "end loop" so that after migrating all records from SOURCE to TARGET, it will commit. If there are any problems during the insertion (even 1 record) I have decided to rollback. This I'm doing it via. WHEN OTHERS THEN ROLLBACK. This also will help re-running the migration procedure on failures.
My fellow developer says this "We need to commit frequently to avoid locking issues with the production database. A preferable solution would to pass in a “commit frequency” to the migration procedure".
Is it really required as I'm not going to migrate more than 0.1 million records one time?
He also adds "Having that many orders uncommitted holds locks in the database, which it held for more than a couple seconds has a very high risk of causing locks, and even deadlocks, in the production environment. Worse, there is the risk of lock escalation, which would place exclusive locks on the tables". Is it true?
Is it true?
Thanks for your answers in advance