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
