CODEWITHSUNDEEP
databasehibernate
Farhan stands with Palestine
Farhan stands with Palestine

Reputation: 14126

What are the advantages & disadvantages of Auto-commit mode of operation of a database connection?

From the Hibernate Documentation.

Many DBMSs enable the so called autocommit mode on every new database connection by default. The autocommit mode is useful for ad hoc execution of SQL.
An application, by definition, always executes a planned sequence of statements. It seems reasonable that you therefore always create transaction boundaries to group your statements into units that are atomic. Therefore, the autocommit mode has no place in an application.

Why is there a default auto-commit mode if no application would be using it?

Upvotes: 1

Views: 2599

Answers (2)

Bohemian
Bohemian

Reputation: 425083

For atomic business processes that can be competed with one query it’s more efficient to use auto-commit because you avoid the extra begin and commit queries that would otherwise be required.

However, for other processes (the bulk), auto-commit is not a realistic option, because a failure part way through the process, ie when only some of the update queries have completed, will almost certainly leave the database in an inconsistent state, which is very bad.

In the general case, it’s better to leave transaction handling to the transaction context (database library and container etc).

Upvotes: 1

william_grisaitis
william_grisaitis

Reputation: 5911

I believe you answered your own question, at least partly:

The autocommit mode is useful for ad hoc execution of SQL.

Yes, it might be undesirable for applications with high transaction volume, but it can still be useful in other contexts, e.g. ad hoc or low-volume inserting.

Upvotes: 1

Related Questions