Does PyMySQL support SELECT...FOR UPDATE?

Question

PyMySQL, a python package to access MySQL database, seems not support SELECT ... FOR UPDATE.

In the code below, I used SELECT...FOR UPDATE to read some_table in function f(), used UPDATE to modify the table in g(), and spawned 50 threads for each function.

I expected deadlock to happen since SELECT...FOR UPDATE should block the threads spawned by g. But actually no deadlock happened. Can some one explain why?

from threading import Thread
import pymysql

def f():
    db = pymysql.connect("localhost", "tester","pwd", "testDB")
    cur = db.cursor()
    sql = "SELECT * FROM some_table FOR UPDATE"
    try:
        cur.execute(sql)
    except:
        print("Exception in select")

def g():
    db = pymysql.connect("localhost", "tester", "pwd","testDB")
    cur = db.cursor()
    sql = "UPDATE some_table SET val=20 WHERE id=2"
    try:
       cur.execute(sql)
       db.commit()
    except:
       print("Exception in update")
       db.rollback()

for _ in range(50):
    Thread(target=f, args=()).start()
    Thread(target=g, args=()).start()

I am using Python 3.4 and PyMySQL 0.6.6. Thank you in advance.

Answer 1

PyMySQL does support SELECT ... FOR UPDATE

But you need start the transaction using connection.begin()

Here's an example:

connection= pymysql.connect("localhost", "tester", "pwd","testDB")
connection.begin()
cursor = db.cursor()
cursor.execute("SELECT * FROM some_table FOR UPDATE")

Table/row (depending upon your select query) is now in locked state. Only the current connection can make changes.

To release the lock. You can,

1. commit the changes (if any)

connection.commit()

2. close the connection

connection.close()