CODEWITHSUNDEEP
pythonpostgresqltransactionspsycopg2
quapka
quapka

Reputation: 2929

SQL command SELECT fetches uncommitted data from Postgresql database

In short: I have Postgresql database and I connect to that DB through Python's psycopg2 module. Such script might look like this:

import psycopg2

# connect to my database
conn = psycopg2.connect(dbname="<my-dbname>",
                        user="postgres",
                        password="<password>",
                        host="localhost",
                        port="5432")

cur = conn.cursor()

ins  = "insert into testtable (age, name) values (%s,%s);"
data = ("90", "George")

sel = "select * from testtable;"

cur.execute(sel)
print(cur.fetchall())
# prints out
# [(100, 'Paul')]
# 
# db looks like this
# age | name
# ----+-----
# 100 | Paul

# insert new data - no commit!
cur.execute(ins, data)
# perform the same select again
cur.execute(sel)
print(cur.fetchall())
# prints out
# [(100, 'Paul'),(90, 'George')]
#
#  db still looks the same
# age | name
# ----+-----
# 100 | Paul
cur.close()
conn.close()

That is, I connect to that database which at the start of the script looks like this:

age | name
----+-----
100 | Paul

I perform SQL select and retrieve only Paul data. Then I do SQL insert, however without any commit, but the second SQL select still fetches both Paul and George - and I don't want that. I've looked both into psycopg and Postgresql docs and found out about ISOLATION LEVEL (see Postgresql and see psycopg2). In Postgresql docs (under 13.2.1. Read Committed Isolation Level) it explicitly says:

However, SELECT does see the effects of previous updates executed within its own transaction, even though they are not yet committed.

I've tried different isolation levels, I understand, that Read Committed and Repeatable Read don't wokr, I thought, that Serializable might work, but it does not -- meaning that I still can fetch uncommitted data with select.

I could do conn.set_isolation_level(0), where 0 represents psycopg2.extensions.ISOLATION_LEVEL_AUTOCOMMIT, or I could probably wrap the execute commands inside with statements (see).

After all, I am bit confused, whether I understand transactions and isolations (and the behavior of select without commit is completely normal) or not. Can somebody enlighten this topic to me?

Upvotes: 1

Views: 1478

Answers (2)

e4c5
e4c5

Reputation: 53774

Using autocommit will not solve your problem. When autocommit is one every insert and update is automatically committed to the database and all subsequent reads will see that data.

It's most unusual to not want to see data that has been written to the database by you. But if that's what you want, you need two separate connections and you must make sure that your select is executed prior to the commit.

Upvotes: 1

IMSoP
IMSoP

Reputation: 97898

Your two SELECT statements are using the same connection, and therefore the same transaction. From the psycopg manual you linked:

By default, the first time a command is sent to the database ... a new transaction is created. The following database commands will be executed in the context of the same transaction.

Your code is therefore equivalent to the following:

BEGIN TRANSACTION;
select * from testtable;
insert into testtable (age, name) values (90, 'George');
select * from testtable;
ROLLBACK TRANSACTION;

Isolation levels control how a transaction interacts with other transactions. Within a transaction, you can always see the effects of commands within that transaction.

If you want to isolate two different parts of your code, you will need to open two connections to the database, each of which will (unless you enable autocommit) create a separate transaction.

Note that according to the document already linked, creating a new cursor will not be enough:

...not only the commands issued by the first cursor, but the ones issued by all the cursors created by the same connection

Upvotes: 2

Related Questions