SQLAlchemy not returning selected data

Question

I'm using SQLAlchemy as the ORM within an application i've been building for some time.

So far, it's been quite a painless ORM to implement and use, however, a recent feature I'm working on requires a persistent & distributed queue (list & worker) style implementation, which I've built in MySQL and Python.

It's all worked quite well until I tested it in a scaled environment. I've used InnoDB row level locking to ensure each row is only read once, while the row is locked, I update an 'in_use' value, to make sure that others don't grab at the entry.

Since MySQL doesn't offer a "NOWAIT" method like Postgre or Oracle does, I've run into locking issues where worker threads hang and wait for the locked row to become available.

In an attempt to overcome this limitation, I've tried to put all the required processing into a single statement, and run it through the ORM's execute() method, although, SQLAlchemy is refusing to return the query result.

Here's an example.

My SQL statement is:

SELECT id INTO @update_id FROM myTable WHERE in_use=0 ORDER BY id LIMIT 1 FOR UPDATE;
UPDATE myTable SET in_use=1 WHERE id=@update_id;
SELECT * FROM myTable WHERE id=@update_id;

And I run this code in the console:

engine = create_engine('mysql://<user details>@<server details>/myDatabase', pool_recycle=90, echo=True)
result = engine.execute(sqlStatement)
result.fetchall()

Only to get this result

[]

I'm certain the statement is running since I can see the update take effect in the database, and if I execute through the mysql terminal or other tools, I get the modified row returned. It just seems to be SQLAlchemy that doesn't want to acknowledge the returned row.

Is there anything specific that needs to be done to ensure that the ORM picks up the response?

Cheers

Answer 1

You have executed 3 queries and MySQLdb creates a result set for each. You have to fetch first result, then call cursor.nextset(), fetch second and so on.

This answers your question, but won't be useful for you, because it won't solve locking issue. You have to understand how FOR UPDATE works first: it locks returned rows till the end of transaction. To avoid long lock wait you have to make it as short as possible: SELECT ... FOR UPDATE, UPDATE SET in_use=1 ..., COMMIT. You actually don't need to put them into single SQL statement, 3 execute() calls will be OK too. But you have have to commit before long computation, otherwise lock will be held too long and updating in_use (offline lock) is meaningless. And sure you can do the same thing using ORM too.