ADO: do I need to explicitly run a "commit" statement during or after a transaction?

Hi Experts:

    In a piece of Python code using ADO, I execute a sql statement execution command inside a transaction for 500000 times like the following code. Theoretically I guess the changes will  be saved after the transaction without explicitly running con.Execute("commit") during or after the transaction.

1. Is my guess correct true?
2. Will there be any problem caused by this "LARGE" transaction if I do it this way?

Thank you!


# Python code 1:
# con is an ADO connection object, i is an integer
con.BeginTrans()
i = 0
while (i<500000):
    con.Execute("insert into table_name (field_a, field_b) values (1,2)")
    i = i + 1
con.CommitTrans()

Open in new window

huangs3Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mrjoltcolaCommented:
I see you have con.CommitTrans() already. I do not know ADO / Python so well, but if that is what I think it is, there is no need for an explicit "commit". Actually all APIs I know have a Commit() function or overall transaction API, you never have to say "commit"

One large transaction is fine, especially if the operation is supposed to be atomic. It depends on what you want to happen if you get an error (roll back?).

Too many commits will degrade performance, but too large / long running transactions can also use UNDO space if there are other queries running against the dataset that is being changed. I could commit in batches of say 50000, but I would just leave it alone and commit at the end unless you otherwise see some sort of problem.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.