AUTOCOMMIT – Oracle vs PostgreSQL

Oracle and PostgreSQL differ significantly in their transaction models, and AUTOCOMMIT is one of those differences. We see many of our customers successfully migrate code, but still encounter variations in behavior and even runtime errors related to transaction control. Many issues happen because AUTOCOMMIT settings differ between Oracle and PostgreSQL. It is definitely necessary to understand the distinctions in AUTOCOMMIT between Oracle and PostgreSQL to ensure a successful migration.

A simple demonstration in Oracle using SQL*Plus

SQL*Plus is an interactive tool that is available as an Instant Client and also with every Oracle database installation by default. I have taken 2 SQL*Plus sessions for demonstration.

SQL*Plus session 1:

I have taken an empty table AUTOC_TEST. In the first session I am inserting a row into the table.

SQL> INSERT INTO AUTOC_TEST VALUES(10);

1 row created.

SQL*Plus session 2:

Without closing the first session I am checking the count of rows for the table in the second session. We see that it shows "0 rows" in second session, even though we inserted a row in first session.

SQL> SELECT COUNT(*) FROM AUTOC_TEST;

  COUNT(*)
----------
         0

Same demonstration in PostgreSQL using psql

Let us now perform same test in PostgreSQL using 2 psql sessions. psql is the PostgreSQL client application and an interactive tool similar to SQL*Plus for Oracle.

PSQL session 1:

I have taken an empty table autoc_test and inserted a row in the 1st session.

postgres=# INSERT INTO autoc_test VALUES(10);
INSERT 0 1

PSQL session 2:

From the second session we are checking the count of rows in the table.

postgres=# SELECT COUNT(*) FROM autoc_test;
 count
-------
     1
(1 row)

But in PostgreSQL we see that it is showing a different behavior when compared to Oracle. We inserted a row in the 1st session and then checked the count of rows for the table in the second session, without closing the 1st session. Unlike Oracle, we are able to see the inserted row in the second session.

To understand this difference we need to understand AUTOCOMMIT in databases.

If you are looking for any assistance around Migrations to PostgreSQL, click here for a Free Migration Assessment and eliminate your database software license costs with our support during migrations from Oracle to PostgreSQL and SQL Server to PostgreSQL.

What is AUTOCOMMIT in databases ?

Autocommit in databases refers to a mode where each SQL statement is automatically committed upon execution, making individual statements as standalone transactions. In the autocommit mode, each user statement is treated as a separate transaction, and a commit is automatically performed at the end of the statement if the execution is successful. If there’s an error, any changes made during the statement’s execution are rolled back.

Do we have AUTOCOMMIT in Oracle?

Oracle does not have an autocommit mode. In Oracle, the AUTOCOMMIT setting is not a feature that you directly set within the database server itself. Instead, autocommit behavior is typically controlled at the client or driver level. For instance, many SQL clients, development tools, and programming language drivers that interact with Oracle databases can be configured to automatically commit transactions after each SQL statement is executed.

To manage autocommit behavior in applications that connect to Oracle, we would typically look into the documentation of the specific client or driver we are using to see how to enable or disable autocommit. For example, in Java’s JDBC (Java Database Connectivity), we can control this behavior by calling setAutoCommit(true) or setAutoCommit(false) on the Connection object.

By default, Oracle operates in a transactional mode where changes made by SQL statements (such as INSERT, UPDATE, DELETE) within a transaction are not made permanent in the database until an explicit COMMIT statement is issued.

That is the reason why in the above Oracle example, we are not able to see the row inserted by the first session visible for the second session. This is because, Oracle gives complete control to the user/client on when to commit and there is no AUTOCOMMIT concept in Oracle server. To be able to see the changes in second session, either we need to COMMIT the changes in the first session or turn on AUTOCOMMIT at the Client level.

DDLs implicitly COMMIT before and after they are completed in Oracle

DDL statements in Oracle automatically commit any outstanding transactions in the session before they start and again after they complete. This means that if you have any uncommitted DML operations (like INSERT, UPDATE, DELETE) before a DDL statement is executed, Oracle will automatically commit those changes. Similarly, once the DDL operation is completed, it commits again, making the DDL changes permanent.

As an example, let us take 2 SQL*Plus sessions again. I have truncated the table AUTOC_TEST table before performing this test.

Session 1:

SQL> INSERT INTO AUTOC_TEST VALUES(10);

1 row created.

Session 2:

SQL> SELECT COUNT(*) FROM AUTOC_TEST;

  COUNT(*)
----------
         0

We see that we have inserted a row from session 1 and we are not able to see that change in session 2. Now let us run a DDL statement in session 1 and observe what we discussed above.

Session 1:

SQL> CREATE TABLE NEW_TABLE(I INT);

Table created.

Session 2:

SQL> SELECT COUNT(*) FROM AUTOC_TEST;

  COUNT(*)
----------
         1

We see that when we run a DDL statement from session 1, Oracle is automatically committing and we are able to see changes in session 2.

What about AUTOCOMMIT in PostgreSQL?

PostgreSQL operates in the autocommit-on mode, and there is no option to alter this behavior at the server level. This is commonly referred to as transactions executed in unchained mode. However, PostgreSQL allows clients to configure autocommit settings. These database clients typically allow us to turn off autocommit at client side, by automatically issuing a BEGIN statement before the first statement following the end of a transaction.

That is the reason why in the above example, we are able to get changes of first session in the second session. Because PostgreSQL server operates in autocommit on mode. The PSQL client defaults to AUTOCOMMIT ON.

It is always strongly recommended to avoid disabling autocommit. Most database clients and APIs, including psql, JDBC, psycopg2, pgAdmin, and DBeaver, provide an option to disable autocommit. Notably, in the mentioned list, all clients, except psycopg2, run in autocommit-on mode as the default setting. So when using psycopg2, we need to make sure to turn on AUTOCOMMIT mode using the below statement where conn is connection variable.

conn.autocommit = True 

Problems with “autocommit off” in PostgreSQL

There are several issues that can arise when autocommit is turned off in PostgreSQL. If you disable autocommit and forget to execute a commit or rollback, your transaction will be in an idle state for a long time. There are few more problems:

We cannot use COMMIT/ROLLBACK in procedures

If we disable autocommit, we will not be able to use transaction control statements like COMMIT or ROLLBACK in the procedures. If we use them we will get a runtime error. See below example –

CREATE OR REPLACE PROCEDURE add_numbers(
    in1 INTEGER,
    in2 INTEGER
)
AS $$
BEGIN
    INSERT INTO testidm VALUES(1);
    COMMIT;
END;
$$ LANGUAGE plpgsql;

CALL add_numbers(1,2);

--Output
ERROR:  invalid transaction termination
CONTEXT:  PL/pgSQL function add_numbers(integer,integer) line 4 at COMMIT

The same is also mentioned in the docs that:

 

If CALL is executed in a transaction block, then the called procedure cannot execute transaction control statements. Transaction control statements are only allowed if CALL is executed in its own transaction.

Locks held by long-running transactions

Disabling autocommit in PostgreSQL provides users with complete control over when to COMMIT or ROLLBACK. However, this can lead to long-running transactions, and the locks acquired by the SQL statements within the transaction may be held for an extended period, thereby potentially blocking other transactions.

Table bloat

In PostgreSQL, the autovacuum daemon is responsible for removing dead tuples, which are generated from deletes and updates. If the session has a long-running open transaction, autovacuum may be unable to delete these dead tuples, leading to table bloat.

Conclusion

To sum it up, when migrating from Oracle to PostgreSQL, the way we handle AUTOCOMMIT can cause hiccups. Oracle relies on manual commits, while PostgreSQL operates in autocommit mode by default. Understanding and adjusting to these differences is vital for a smooth transition, avoiding unexpected issues in how transactions are managed. It is always recommended not to turn off autocommit, as doing so results in unexpected behavior and runtime errors.