Обсуждение: set autocommit only for select statements
Hi; Is there a way to set "autocommit = on" for all select statements and have "autocommit = off" for all other statements? Thanks in advance
Is there a way to set "autocommit = on" for all select statements and
have "autocommit = off" for all other statements?
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
Am 12.04.22 um 03:53 schrieb Mladen Gogala: > > 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. > What you write about psql sending a COMMIT statement after each statement is wrong. It may be true for other database systems. PostgreSQL as a server commits each statement automatically unless the client has started a transaction with BEGIN or START TRANSACTION statements. This can be proved easily by not using psql as a client, but some programming language. Regards, Holger -- Holger Jakobs, Bergisch Gladbach, Tel. +49-178-9759012
Вложения
-----Message d'origine----- De : Holger Jakobs <holger@jakobs.com> Envoyé : mardi 12 avril 2022 08:31 À : pgsql-admin@lists.postgresql.org Objet : Re: set autocommit only for select statements Am 12.04.22 um 03:53 schrieb Mladen Gogala: > > 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. > What you write about psql sending a COMMIT statement after each statement is wrong. It may be true for other database systems. PostgreSQL as a server commits each statement automatically unless the client has started a transaction with BEGIN or STARTTRANSACTION statements. This can be proved easily by not using psql as a client, but some programming language. Regards, Holger -- Holger Jakobs, Bergisch Gladbach, Tel. +49-178-9759012 ----------------------------------------------------------------------------------- Hi, It is a little more "complex" :-) A session can be put in AUTOCOMMIT mode or transaction mode. Tool "psql" is by default in AUTOCOMMIT mode and can be putin transaction mode using \set AUTOCOMMIT off Pay attention to the variable case. It should be in capital letters. When this is done, You don't need to start transactionsexplicitly and the server behaves like Oracle but it is not an identical behavior. Other points matter like thereaction to errors when in a transaction... Other programming environments could choose another default operating mode and however can offer to choose the operatingmode at connection. Michel SALAIS
What you write about psql sending a COMMIT statement after each statement is wrong. It may be true for other database systems. PostgreSQL as a server commits each statement automatically unless the client has started a transaction with BEGIN or START TRANSACTION statements. This can be proved easily by not using psql as a client, but some programming language. Regards, Holger
RDBMS, in order to be ACID compliant, deals with transactions, not with single statements. It is the client who starts transaction, not the database. It is also the client who sends commit. And yes, I am also using Python which allows me to set autocommit property:
ttps://www.psycopg.org/docs/connection.html#connection.autocommit
If I set autocommit connection property to True, each cursor.execute will send a commit. Autocommit cannot be set or disabled on the database level because the database deals with transactions. The point where the transaction is started and finished is the client. That is so for MySQL, that is so for Oracle and that is so for Postgres.BTW, speaking of Python, has anybody here tried Psycopg3? I know it was released but I am not sure whether it's stable enough to use in production?
-- Mladen Gogala Database Consultant Tel: (347) 321-1217 https://dbwhisperer.wordpress.com
What you write about psql sending a COMMIT statement after each statement is wrong. It may be true for other database systems.
PostgreSQL as a server commits each statement automatically unless the client has started a transaction with BEGIN or START TRANSACTION statements.
This can be proved easily by not using psql as a client, but some programming language.
Regards,
If what you are saying was true, then autocommit would be a database mode, not a tool mode. It isn't a database mode. Q.E.D.
-- Mladen Gogala Database Consultant Tel: (347) 321-1217 https://dbwhisperer.wordpress.com
On Mon, 11 Apr 2022 at 17:15, Sbob <sbob@quadratum-braccas.com> wrote: > Is there a way to set "autocommit = on" for all select statements and > have "autocommit = off" for all other statements? Please explain what it is you are hoping to achieve? -- Simon Riggs http://www.EnterpriseDB.com/
On Tue, Apr 12, 2022 at 08:00:35AM -0400, Mladen Gogala wrote: > On 4/12/22 02:31, Holger Jakobs wrote: > > What you write about psql sending a COMMIT statement after each > > statement is wrong. It may be true for other database systems. > > > > PostgreSQL as a server commits each statement automatically unless the > > client has started a transaction with BEGIN or START TRANSACTION > > statements. > > > > This can be proved easily by not using psql as a client, but some > > programming language. > > > > Regards, > > If what you are saying was true, then autocommit would be a database mode, > not a tool mode. It isn't a database mode. Q.E.D. That's actually mostly true. Postgres will start an implicit transaction for every query (or multiple queries) outside a transaction and will implicitly commit it after a successful execution. The only difference is there you won't see a BEGIN / COMMIT anywhere, but the same underlying code will be executed.