What is isolation level of autocommit?

All we need is an easy explanation of the problem, so here it is.

I’m trying to make sense from "autocommit" concept.

Originally I’ve heart about it in the context of Java JDBC API Connection.setAutoCommit(boolean autoCommit)

Quick search revealed that the term is not specific to JDBC, PostgreSQL refer to "autocommit" too:

https://www.postgresql.org/docs/current/sql-start-transaction.html

In the standard, it is not necessary to issue START TRANSACTION to start a transaction block: any SQL command implicitly begins a block. PostgreSQL’s behavior can be seen as implicitly issuing a COMMIT after each command that does not follow START TRANSACTION (or BEGIN), and it is therefore often called “autocommit”. Other relational database systems might offer an autocommit feature as a convenience.

If it is widespread what is isolation level of autocommit mode?

Conceptually as we have only one SQL statement before implicit COMMIT isolation levels almost make no sense for autocommit. Only READ UNCOMMITTED might influence the result, other anomalies are defined in term of a sequence of SQL statements.

Can I alter autocommit isolation level to / away from READ UNCOMMITTED?

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

The default isolation level is controlled through default_transaction_isolation and is read committed in a standard installation.

You can check your current setting using:

show default_transaction_isolation;

As Postgres does not support (or implement) read uncommitted there is no need to change this "away from read uncommitted".

If you want to change the isolation level to "higher" one, even in autocommit mode, you can use:

set default_transaction_isolation = serializable;

The above will change the default for the current session (=connection).

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply