setAutoCommit(boolean autoCommit)

[Raje Kumar]

What does setAutoCommit(false) signify? Does it mean that it is not automatically committed? I need to give another commit command.
or does it mean that commit is done when connection is closed.

[Stefan Wagner]

I'm not sure whether 'close ()' will do the commit.

When inserting about 1 million rows into a postgres-database, I got an error, because the number of uncommited inserts was limited to something between 10,000 and 200,000.

Perhaps this value is somehow customizable, but uncommited inserts are mostly done for performance-reasons on large inserts, so having a commit every 10,000 rows isn't that bad, if we don't want to get an error after 8 h of reading.

[Pedro Gongora]

it means that all the commands you send to the database will not be commited until you call commit(). In other words it begins a new transaction.

see the example:

Connection con = getConnection(); // init your connection
con.setAutoCommit(false); // it sends a "BEGIN TRANSACTION" to DB
... do your stuff here
if (everythigIsOk) {
con.commit(); // save your work
} else {
con.rollback(); // undo all changes
}

[Adeel Ansari]

Drawbacks of long commits:

- long commits results in lack of performance, i.e. locks contention.
- say you inserted 1,000 rows in AutoCommitFalse mode and then you need to rollback just last 200 then what wil you do?
- some other user will get previous data for a long time. may be he/she need to prepare a report for some manager.


Drawback of short commits:

- you can not rollback your previous insert/update/delete, because you have committed it.

So, it depends on the scenerio, what you would like to do. hence its better to go for something in between. if long commits are required then go for something Stefan Wagner said.

[Shailesh Chandra]

hi Adeel

my view little bit differs

Long commit:

If I have inserted 1000 rows and I want to rollback last 200 rows , means I want to commit 800 rows .
Then I can still use connection.setSavepoint(String savepoint_x)

this will rolback to my transaction to my mentioned save point and I think we can have muliple setSavepoint.


Short Commit :

after commit we can not rollback that true but it is for everywhere even in EJB managed transactions.
This is something related to code flow
[ October 07, 2004: Message edited by: Shailesh Chandra ]

[Adeel Ansari]

ofcourse shailesh, its always depend on the code flow. but when you commit it means commit it.

and about savePoint(). yeah right this is the way we can handle that scenrio. i haven't got any thought of it at that time. thanks for reminding anyways.
[ October 07, 2004: Message edited by: adeel ansari ]

[Surya Ram]

Here is an example
-----
con.setAutoCommit(false);
SQL updates
con.commit();
-----
You have to do it as some applications will have the autocommit set to false at Database level and leave the programmers to handle it in their Java code.