Problem with concurrent reading and writing (distinct processes) using MySQL and Python

Question

I have two concurrent processes:

1.) Writer - inserts new rows into a MySQL database on a regular basis (10-20 rows/sec)

2.) Reader - reads from the same table being inserted into

I notice that the Reader process only seems to see a snapshot of the database at about the time of its startup. Inserts occuring before this startup are found, but inserts occuring after are not. If I shut the Reader process down and restart it (but leave the Writer running), it will sometimes (but not always) see more data, but again seems to get a point-in-time view of the database.

I'm running a commit after each insert (code snippet below). I investigated whether this was a function of change buffering/pooling, but doing a "set @@global.innodb_change_buffering=none;" had no effect. Also, if I go in through MySQL workbench, I can query the most current data being inserted by the Writer. So this seems to be a function of how the Python/MySQL connection is getting set up.

My environment is:

Windows 7

MySQL 5.5.9

Python 2.6.6 -- EPD 6.3-1 (32-bit)

MySQL python connector

The insert code is:

    def insert(dbConnection, statement):
    cursor = dbConnection.cursor()
    cursor.execute(statement)   
    warnings = cursor.fetchwarnings()
    if warnings:
        print warnings
        rowid = []
    else:
        rowid = cursor.lastrowid
    cursor.close()
    dbConnection.commit()
    return rowid

The reader code is:

def select(dbConnection, statement):
cursor = dbConnection.cursor()
cursor.execute(statement)   
warnings = cursor.fetchwarnings()
if warnings:
    print warnings
    values = []
else:
    values = np.asarray(cursor.fetchall())
cursor.close()
return values

stderr · Accepted Answer · 2011-03-03 02:33:45Z

1

What's the read side look like?

I bet this is a problem with the isolation level on the read side. Most likely your read connection is getting an implicit transaction and the default InnoDB isolation level is:

Repeatable Read

Try issuing:

cursor.execute("SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED")

on the read side.

answered Mar 3, 2011 at 2:33

stderr

8,7521 gold badge38 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

cyj Over a year ago

Mike, you rock! This did the trick and is definitely not a tweak I would have found easily. I suppose I understand that there may be situations in which this snapshot-in-time is desired behavior, but I don't understand why it would be the default. I would think that for most applications with a long-standing/persistent database connection, this would not be the preferred behavior. Is there something about the way I'm setting up the connection that could be inadvertently triggering this behavior? Also, performance-wise, I'm assuming that the Repeatable Read would be a drag on performance.

stderr Over a year ago

I think the assumption is that whenever you read you would begin a transaction and commit or roll it back at the end and that those transactions will be relatively short lived. I think that in most common mysql workflows that's exactly what happens, a short job (like a CGI / PHP script) starts, connects to the db and reads some data and then disconnects. An application like that would most likely be okay with not reading data that was committed after it started. I imagine other applications that have pools of DB connections manage their isolation level explicitly to fit their usage.

stderr Over a year ago

It's certainly possible that MySQLConnector opens connections and puts them in this isolation level. However, I bet this is just the default mysql configuration. It's certainly reasonable to go into your db config and change it to READ COMMITTED. It's interesting to note that other databases have different default isolation levels. For instance, PostgreSQL uses READ COMMITTED by default.

cyj Over a year ago

I put this problem aside for a month, and now that I'm back into it, I'm finding that setting this "read committed" flag is only a partial solution... What I'm seeing now is that the Reader process sees data being concurrently inserted by the Writer for the first 15-30 seconds, but after, fails to see any new data. What's odd about this is that I can see all of the Writer's inserts through a MySQL Workbench connection, so it seems that there's something about the Python connection that is failing to access the most recent data. Are there other settings that might affect data visibility?

Collectives™ on Stack Overflow

Problem with concurrent reading and writing (distinct processes) using MySQL and Python

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related