Re: set autocommit only for select statements - Mailing list pgsql-admin

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