Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Without autocommit SELECT returns old data. #390

artfulrobot opened this issue Nov 13, 2015 · 1 comment

Without autocommit SELECT returns old data. #390

artfulrobot opened this issue Nov 13, 2015 · 1 comment


Copy link

This might not be a bug, but as my question at stackoverflow had not received any answers, it appears I'm not the only one vexed by this, so I'm putting it here in case it is a bug. Apols if it's some weird desired behaviour.

Set up a database with one table, one row.

mysql> CREATE TABLE state (counter INT(10) UNSIGNED NOT NULL) ENGINE=InnoDB;
mysql> INSERT INTO state VALUES (1);

Then run this:

#!/usr/bin/env python3
import pymysql.cursors
import time

conn = pymysql.connect(host='localhost',
     # credentials etc.

while True:
    with conn.cursor() as cursor:
        cursor.execute("SELECT * FROM state limit 1;")
        vals = cursor.fetchone()
        print (vals)

This prints 1 every second. Now while that's running, fire up a MySQL client and update the state table

mysql> UPDATE state SET counter = counter + 1;

(On Debian/MariaDB 10 at least, this client autocommits, but you can issue a COMMIT from the client if you're unsure. And you can test by exiting the CLI client and starting a new one to inspect the table.)

I would now expect the running python script to read 2 but it keeps saying 1.

I have found that by passing autocommit=True, or issuing conn.commit() after the cursor.execute line, it works as expected.

Copy link

methane commented Nov 13, 2015

It's repeatable read.

Sign up for free to subscribe to this conversation on GitHub. Already have an account? Sign in.
None yet
None yet

No branches or pull requests

2 participants