Solved

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

Posted on 2009-07-10
1
319 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
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Migrate database to ASM disks. 1 40
ORA-01008: not all variables bound. 6 38
Python variable _ manually assigned 9 65
object oriented programming comparison 5 55
A set of related code is known to be a Module, it helps us to organize our code logically which is much easier for us to understand and use it. Module is an object with arbitrarily named attributes which can be used in binding and referencing. …
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…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

895 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now