pythondatabasepostgresqltransactionstable-locking

Python & Postgres: would psycopg2.connect lock the table?

I am running a python script to do ETL(Extract, transform, load) and I put all the psql queries in one transaction. Here's the transaction:

conn = psycopg2.connect(...)
try:
    cur = conn.cursor() #q1
    cur.execute("create temp table tt (like t INCLUDING DEFAULTS)") #q2
    cur.execute("copy tt from '/file.csv' DELIMITER ',' CSV HEADER ") #q3
    cur.execute("...") #q4,   update t based on data from tt
    conn.commit()
except:
    conn.rollback()

I know the table will be locked when running q4, but I'm not sure if the table would be locked during the whole transaction(from connect to commit)?

Is there a way to test if the table is locked? I don't have much of data right now (about 100 rows)..

Thanks very much!

Solution

  • I know the table will be locked when running q4, but I'm not sure if the table would be locked during the whole transaction(from connect to commit)?

    Locks are taken when first required, and released at transaction commit, not before.

    So in your case, you don't access t until q4, so that's when the lock is taken. UPDATE takes a ROW EXCLUSIVE lock on the table. Despite the name, it's a table level lock; additionally, row-level locks are taken on rows that're updated.

    Is there a way to test if the table is locked? I don't have much of data right now (about 100 rows)..

    Query pg_locks. Understand that there are both table- and row-level locks.