Content-Type: multipart/alternative;
 boundary="------------DmX6Cz3hx3n3fa0gowcxHg0P"
Message-ID: <bc177652-b317-0f78-cc82-466f24315549@gmail.com>
Date: Mon, 11 Apr 2022 21:53:59 -0400
MIME-Version: 1.0
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:91.0) Gecko/20100101
 Thunderbird/91.7.0
Subject: Re: set autocommit only for select statements
Content-Language: en-US
To: pgsql-admin@lists.postgresql.org
References: <548f2ed6-3209-705d-09e2-a5afbc6fb71c@quadratum-braccas.com>
From: Mladen Gogala <gogala.mladen@gmail.com>
In-Reply-To: <548f2ed6-3209-705d-09e2-a5afbc6fb71c@quadratum-braccas.com>
Archived-At: 
 <https://www.postgresql.org/message-id/bc177652-b317-0f78-cc82-466f24315549%40gmail.com>
Precedence: bulk

This is a multi-part message in MIME format.
--------------DmX6Cz3hx3n3fa0gowcxHg0P
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit

On 4/11/22 12:14, Sbob wrote:
> Hi;
>
>
> Is there a way to set "autocommit = on" for all select statements and 
> have "autocommit = off" for all other statements?
>
>
> Thanks in advance
>
>
>
>
The "autocommit" is a tool option which tells the tool whether to add 
"COMMIT" statement after each and every SQL. The RDBMS server only knows 
about transactions, as mandated by the ACID compliance. What the 
"autocommit" option of tools like psql actually does is to turn each of 
your SQL statements into a separate transaction. That can have some 
drawbacks, but it also has some positive sides. Your lock duration is 
much shorter and you don't get lock waits. However, some things may 
surprise you:

[mgogala@umajor ~]$ psql
Password for user mgogala:
psql (13.6, server 14.2)
WARNING: psql major version 13, server major version 14.
          Some psql features might not work.
Type "help" for help.

mgogala=# select ename,sal from emp where deptno=20 for update;
  ename | sal
-------+------
  SMITH |  800
  JONES | 2975
  SCOTT | 3000
  ADAMS | 1100
  FORD  | 3000
(5 rows)

mgogala=# select l.locktype,d.datname,r.relname from pg_locks l join
mgogala-# pg_database d on (l.database=d.oid) join pg_class r on 
(l.relation=r.oid);
  locktype | datname |              relname
----------+---------+-----------------------------------
  relation | mgogala | pg_class_tblspc_relfilenode_index
  relation | mgogala | pg_class_relname_nsp_index
  relation | mgogala | pg_class_oid_index
  relation | mgogala | pg_class
  relation | mgogala | pg_locks
(5 rows)

mgogala=#

As you can see, I did "SELECT FOR UPDATE" from the table named "emp". 
When I check the locks from pg_locks, there are no locks on the "emp" 
table. That is because psql (and not the database) has executed "COMMIT" 
immediately after "SELECT FOR UPDATE", thereby ending the transaction 
and releasing the locks. However, if I open another session and do the 
following:

mgogala=# begin transaction;
BEGIN
mgogala=*# select ename,sal from emp where deptno=20 for update;
  ename | sal
-------+------
  SMITH |  800
  JONES | 2975
  SCOTT | 3000
  ADAMS | 1100
  FORD  | 3000
(5 rows)

The result of query to pg_locks is now very different:

mgogala=# select l.locktype,l.mode,d.datname,r.relname from pg_locks l join
pg_database d on (l.database=d.oid) join pg_class r on (l.relation=r.oid);
  locktype |      mode       | datname | relname
----------+-----------------+---------+-----------------------------------
_*relation | RowShareLock    | mgogala | emp_pkey*__*
*__* relation | RowShareLock    | mgogala | emp*_
  relation | AccessShareLock | mgogala | pg_class_tblspc_relfilenode_index
  relation | AccessShareLock | mgogala | pg_class_relname_nsp_index
  relation | AccessShareLock | mgogala | pg_class_oid_index
  relation | AccessShareLock | mgogala | pg_class
  relation | AccessShareLock | mgogala | pg_locks
(7 rows)

Now, there are two locks in RowShare mode on the  emp table and its 
primary key. That is because the transaction on the "emp" table has not 
finished and locks are still intact. BTW, you don't have to turn off the 
autocommit mode to use "BEGIN TRANSACTION". The morals of the story is 
that the "autocommit option" is something that regulates the behavior of 
the tool, not the database.

The point of "SELECT FOR UPDATE" is to lock certain rows in the database 
to modify them later. That will not work without "BEGIN TRANSACTION". In 
the autocommit mode, each SQL is a separate transaction, delineated by 
the transaction terminating statements by the tool executing the SQL.

Regards

-- 
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
https://dbwhisperer.wordpress.com

--------------DmX6Cz3hx3n3fa0gowcxHg0P
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 8bit

<html>
  <head>
    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
  </head>
  <body>
    <div class="moz-cite-prefix">On 4/11/22 12:14, Sbob wrote:<br>
    </div>
    <blockquote type="cite"
      cite="mid:548f2ed6-3209-705d-09e2-a5afbc6fb71c@quadratum-braccas.com">Hi;
      <br>
      <br>
      <br>
      Is there a way to set "autocommit = on" for all select statements
      and have "autocommit = off" for all other statements?
      <br>
      <br>
      <br>
      Thanks in advance
      <br>
      <br>
      <br>
      <br>
      <br>
    </blockquote>
    <p>The "autocommit" is a tool option which tells the tool whether to
      add "COMMIT" statement after each and every SQL. The RDBMS server
      only knows about transactions, as mandated by the ACID compliance.
      What the "autocommit" option of tools like psql actually does is
      to turn each of your SQL statements into a separate transaction. 
      That can have some drawbacks, but it also has some positive sides.
      Your lock duration is much shorter and you don't get lock waits.
      However, some things may surprise you:</p>
    <p><font face="monospace" color="blue">[mgogala@umajor ~]$ psql<br>
        Password for user mgogala: <br>
        psql (13.6, server 14.2)<br>
        WARNING: psql major version 13, server major version 14.<br>
                 Some psql features might not work.<br>
        Type "help" for help.<br>
        <br>
        mgogala=# select ename,sal from emp where deptno=20 for update;<br>
         ename | sal  <br>
        -------+------<br>
         SMITH |  800<br>
         JONES | 2975<br>
         SCOTT | 3000<br>
         ADAMS | 1100<br>
         FORD  | 3000<br>
        (5 rows)<br>
        <br>
        mgogala=# select l.locktype,d.datname,r.relname from pg_locks l
        join <br>
        mgogala-# pg_database d on (l.database=d.oid) join pg_class r on
        (l.relation=r.oid);<br>
         locktype | datname |              relname              <br>
        ----------+---------+-----------------------------------<br>
         relation | mgogala | pg_class_tblspc_relfilenode_index<br>
         relation | mgogala | pg_class_relname_nsp_index<br>
         relation | mgogala | pg_class_oid_index<br>
         relation | mgogala | pg_class<br>
         relation | mgogala | pg_locks<br>
        (5 rows)<br>
        <br>
        mgogala=# </font><br>
    </p>
    <p>As you can see, I did "SELECT FOR UPDATE" from the table named
      "emp". When I check the locks from pg_locks, there are no locks on
      the "emp" table. That is because psql (and not the database) has
      executed "COMMIT" immediately after "SELECT FOR UPDATE", thereby
      ending the transaction and releasing the locks. However, if I open
      another session and do the following:</p>
    <p><font face="monospace" color="blue">mgogala=# begin transaction;<br>
        BEGIN<br>
        mgogala=*# select ename,sal from emp where deptno=20 for update;<br>
         ename | sal  <br>
        -------+------<br>
         SMITH |  800<br>
         JONES | 2975<br>
         SCOTT | 3000<br>
         ADAMS | 1100<br>
         FORD  | 3000<br>
        (5 rows)</font><br>
      <br>
    </p>
    <p>The result of query to pg_locks is now very different:</p>
    <p><font face="monospace" color="blue">mgogala=# select
        l.locktype,l.mode,d.datname,r.relname from pg_locks l join <br>
        pg_database d on (l.database=d.oid) join pg_class r on
        (l.relation=r.oid);<br>
         locktype |      mode       | datname |             
        relname              <br>
----------+-----------------+---------+-----------------------------------<br>
         <u><b>relation | RowShareLock    | mgogala | emp_pkey</b></u><u><b><br>
          </b></u><u><b> relation | RowShareLock    | mgogala | emp</b></u><br>
         relation | AccessShareLock | mgogala |
        pg_class_tblspc_relfilenode_index<br>
         relation | AccessShareLock | mgogala |
        pg_class_relname_nsp_index<br>
         relation | AccessShareLock | mgogala | pg_class_oid_index<br>
         relation | AccessShareLock | mgogala | pg_class<br>
         relation | AccessShareLock | mgogala | pg_locks<br>
        (7 rows)</font><br>
    </p>
    <p>Now, there are two locks in RowShare mode on the  emp table and
      its primary key. That is because the transaction on the "emp"
      table has not finished and locks are still intact. BTW, you don't
      have to turn off the autocommit mode to use "BEGIN TRANSACTION".
      The morals of the story is that the "autocommit option" is
      something that regulates the behavior of the tool, not the
      database.</p>
    <p>The point of "SELECT FOR UPDATE" is to lock certain rows in the
      database to modify them later. That will not work without "BEGIN
      TRANSACTION". In the autocommit mode, each SQL is a separate
      transaction, delineated by the transaction terminating statements
      by the tool executing the SQL.<br>
    </p>
    <p>Regards<br>
    </p>
    <pre class="moz-signature" cols="72">-- 
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
<a class="moz-txt-link-freetext" href="https://dbwhisperer.wordpress.com">https://dbwhisperer.wordpress.com</a>
</pre>
  </body>
</html>

--------------DmX6Cz3hx3n3fa0gowcxHg0P--