2

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?

Improve this question
0

1 Answer 1

Reset to default
2

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).

Improve this answer
8
  • Tnx. From your answer I infer that SQL standard doesn't define syntax for default isolation level. We have to use proprietary extensions via connection settings or set operator. This answer (for SQL Server) dba.stackexchange.com/questions/221548/… also supports idea that syntax for default isolation level is vendor specific.
    – gavenkoa
    Apr 7, 2021 at 9:01
  • 1
    start transaction and set transaction are part of the SQL standard as far as I know. As the SQL standard doesn't have a notion of "autocommit" to begin with (as the Postgres manual states) those are the "standard ways" to deal with isolation levels.
    – user1822
    Apr 7, 2021 at 9:07
  • 1
    @gavenkoa: as you seem to be using Java/JDBC: you can use Connection.setTransactionIsolation() to change the isolation level for the current connection - that would be database independent if you really need that
    – user1822
    Apr 7, 2021 at 9:31
  • 3
    The SQL standard decrees that the default transaction isolation level be SERIALIZABLE, but no database that I have heard of implements that.
    – Laurenz Albe
    Apr 7, 2021 at 9:38
  • 2
    @JohnK.N. SQL 2012, Chapter 4.36.4 "Isolation levels of SQL-transactions": The isolation level of an SQL-transaction when any cursor is held open from the previous SQL-transaction within an SQL-session is the isolation level of the previous SQL-transaction by default. If no cursor is held open, or this is the first SQL-transaction within an SQL-session, then the isolation level is SERIALIZABLE by default.
    – Laurenz Albe
    Apr 7, 2021 at 11:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.