CODEWITHSUNDEEP
python-3.xmultithreadingcursorpsycopg2
Sanjay
Sanjay

Reputation: 75

For Update - for psycopg2 cursor for postgres

We are using psycopg2 jsonb cursor to fetch the data and processing but when ever new thread or processing coming it should not fetch and process the same records which first process or thread.

For that we have try to use the FOR UPDATE but we just want to know whether we are using correct syntax or not.

    con = self.dbPool.getconn()
    cur = conn.cursor()             
    sql="""SELECT jsondoc FROM %s WHERE jsondoc @> %s"”"             
    if 'sql' in queryFilter:                 
   sql += queryFilter 'sql’]   
   When we print this query, it will be shown as below:             
         Query: "SELECT jsondoc FROM %s WHERE jsondoc @> %s AND (jsondoc ->> ‘claimDate')::float <= 1536613219.0 AND ( jsondoc ->> ‘claimstatus' = ‘done' OR jsondoc ->> 'claimstatus' =                    'failed' ) limit 2 FOR UPDATE"         
 cur.execute(sql, (AsIs(self.tablename), Json(queryFilter),)) 
    cur.execute()
    dbResult = cur.fetchall()

Please help us to clarify the syntax and explain if that syntax is correct then how this query lock the fetched records of first thread.

Thanks, Sanjay.

Upvotes: 0

Views: 774

Answers (1)

klin
klin

Reputation: 121584

If this exemplary query is executed

select * 
from my_table
order by id
limit 2
for update; -- wrong

then two resulting rows are locked until the end of the transaction (i.e. next connection.rollback() or connection.commit() or the connection is closed). If another transaction tries to run the same query during this time, it will be stopped until the two rows are unlocked. So it is not the behaviour you are expected. You should add skip locked clause:

select * 
from my_table
order by id
limit 2
for update skip locked; -- correct

With this clause the second transaction will skip the locked rows and return next two onces without waiting.

Read about it in the documentation.

Upvotes: 1

Related Questions