Solved

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

Posted on 2009-07-10
1
337 Views
Last Modified: 2013-12-19
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

0
Comment
Question by:huangs3
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
1 Comment
 
LVL 40

Accepted Solution

by:
mrjoltcola earned 500 total points
ID: 24827537
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

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Strings in Python are the set of characters that, once defined, cannot be changed by any other method like replace. Even if we use the replace method it still does not modify the original string that we use, but just copies the string and then modif…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to recover a database from a user managed backup
Suggested Courses

635 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question