START TRANSACTION with Python 3.6 mysql-connector-python returns no results

Question

I'm using Python 3.6, mysql-connector-python 8.0.11 and 8.0.11 MySQL Community Server - GPL. The table in question is using the innoDB engine.

When using the MySQL Workbench I can enter:

USE test; START TRANSACTION; SELECT * FROM tasks WHERE task_status != 1 LIMIT 1 FOR UPDATE;

And it provides 1 record as expected:

When I use a script using python3 (from the same machine - same access, etc):

  * SQL QRY: START TRANSACTION; SELECT * FROM test WHERE task_status != 1 LIMIT 1 FOR UPDATE;
  * SQL RES: No result set to fetch from.

This is debug output from my script. If I change the Query to normal SELECT, I do get output.

  * SQL QRY: SELECT * FROM test WHERE task_status != 1 LIMIT 1;
  * SQL RES: [(1, 0, 'TASK0001')]

I know SELECT * isn't the way to go but just trying to get some response for now.

I'm trying to allow multiple worker scripts to pick up a task without the workers taking the same task:

Do a select and row lock the task so other workers 'SELECT' query doesn't show them,
Set the task status to 'being processed' and unlock the record.

This is my first venture into locking so this is new ground. I'm able to do normal queries and populate tables etc so have some experience but not with locking.

TABLE creation:

create table test
(
  id          int auto_increment
   primary key,
  task_status int         not null,
  task_ref    varchar(16) not null
);

Questions:

Is this the correct mindset? I.e. is there a more pythonic/mysql way to do this?
Is there a specific way I need to initiate the mysql connection? Why would it work using the MySQL workbench but not via script? I've tried using direct mysql and this works too - so I think it is the python connector that may need setting up correctly as it is the only component not working.
Currently I'm using 'autocommit=1' on the connector and 'buffered=True' on the cursor. I know you can set 'autocommit=0' in the SQL before the 'START TRANSACTION' so understand for the locking I may need to do this, but for all other transactions I would prefer to keep autocommit on. Is this OK and/or doable?

CODE:

#!/usr/bin/env python

import mysql.connector
import pprint

conn = mysql.connector.connect(user='testuser',
                               password='testpass',
                               host='127.0.0.1',
                               database='test_db',
                               autocommit=True)

dbc = conn.cursor(buffered=True)

qry = "START TRANSACTION; SELECT * FROM 'test' WHERE task_status != 1 LIMIT 1 ON UPDATE;"
sql_select = dbc.execute(qry)
try:
    output = dbc.fetchall()
except mysql.connector.Error as e:
    print("  * SQL QRY: {0}".format(qry))
    print("  * SQL RES: {0}".format(e))
    exit()
else:
    print("  * SQL QRY: {0}".format(qry))
    print("  * SQL RES: {0}".format(output))

Many Thanks,

Frank

frank_t frank_t · Accepted Answer · 2018-07-06T20:05:28

So after playing around a bit, I worked out (by trial and error) that the proper way to do this is to just put 'FOR UPDATE' at the end of the normal query:

Full code is below (including option to add dummy records for testing):

#!/usr/bin/env python

import mysql.connector
import pprint
import os

conn = mysql.connector.connect(user='testuser',
                               password='testpass',
                               host='127.0.0.1',
                               database='test_db',
                               autocommit=True)

dbc = conn.cursor(buffered=True)

worker_pid = os.getpid()
all_done = False

create = False

if create:

    items = []
    for i in range(10000):
        items.append([0, 'TASK%04d' % i])

    dbc.executemany('INSERT INTO test (task_status, task_ref) VALUES (%s, %s)', tuple(items))
    conn.commit()
    conn.close
    exit()


while all_done is False:

    print(all_done)
    qry = (
        "SELECT id FROM test WHERE task_status != 1 LIMIT 1 FOR UPDATE;"
        )
    sql_select = dbc.execute(qry)

    try:
        output = dbc.fetchall()
    except mysql.connector.Error as e:
        print("      * SQL QRY: {0}".format(qry))
        print("      * SQL RES: {0}".format(e))
        exit()
    else:
        print("      * SQL QRY: {0}".format(qry))
        print("      * SQL RES: {0}".format(output))

    if len(output) == 0:
        print("All Done = Yes")
        all_done = True
        continue
    else:
        print("Not Done yet!")

    if len(output) > 0:
        test_id = output[0][0]
        print("WORKER {0} FOUND: '{1}'".format(worker_pid, test_id))
        qry = "UPDATE test SET task_status = %s, task_ref = %s WHERE id = %s;"

    sql_select = dbc.execute(qry, tuple([1, worker_pid, test_id]))
    conn.commit()

    try:
        output = dbc.fetchall()
    except mysql.connector.Error as e:
        print("      * SQL QRY: {0}".format(qry))
        print("      * SQL RES: {0}".format(e))

    else:
        print("      * SQL QRY: {0}".format(qry))
        print("      * SQL RES: {0}".format(output))

    print(all_done)

Hope this helps someone else save some time as there are a lot of places with different info but searches for python3, mysql-connector and transactions didn't get me anything.

Good Luck,

Frank

START TRANSACTION with Python 3.6 mysql-connector-python returns no results

1 Answers