Re: set autocommit only for select statements

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(at)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