python - 为什么需要重新连接数据库才能看到表数据的变化？

Question

我定期查询 MySQL 表并检查同一行中的数据。

我使用 MySQLdb 来完成这项工作，每 15 秒查询一次相同的表和行。

实际上，行数据每 3 秒更改一次，但游标始终返回相同的值。

奇怪的是:在我关闭MySQL连接并重新连接后，使用新游标执行相同的select命令，返回的是新值。

我怀疑错误的代码是在注释之后开始的:

config = SafeConfigParser()
config.read("../test/settings_test.conf")

settings = {}
settings["mysql_host"] = config.get("mysql","mysql_host")
settings["mysql_port"] = int(config.get("mysql","mysql_port"))
settings["mysql_user"] = config.get("mysql","mysql_user")
settings["mysql_password"] = config.get("mysql","mysql_password")
settings["mysql_charset"] = config.get("mysql","mysql_charset")

#suspected wrong code
conn = mysql_from_settings(settings)
cur = conn.cursor()
cur.execute('use database_a;')
cur.execute('select pages from database_a_monitor where id=1;')
result = cur.fetchone()[0]
print result
#during 15 second, I manually update the row and commit from mysql workbench
time.sleep(15)    

cur.execute('select pages from database_a_monitor where id=1;')
result = cur.fetchone()
print result
conn.close()

输出是:

94
94

如果我更改代码以关闭连接并重新连接，它会返回最新值而不是重复相同的值:

conn = mysql_from_settings(settings)
cur = conn.cursor()
cur.execute('use database_a;')
cur.execute('select pages from database_a_monitor where id=1;')
result = cur.fetchone()[0]
print result
conn.close()

time.sleep(15)
#during that period, I manually update the row and commit from mysql workbench

conn = mysql_from_settings(settings)
cur = conn.cursor()
cur.execute('use database_a;')
cur.execute('select pages from database_a_monitor where id=1;')
result = cur.fetchone()[0]
print result
conn.close() 

输出是:

94
104

为什么会出现这种行为差异？

这里是 mysql_from_settings 的定义:

def mysql_from_settings(settings):
    try:
        host = settings.get('mysql_host')
        port = settings.get('mysql_port')
        user = settings.get('mysql_user')
        password = settings.get('mysql_password')
        charset = settings.get('mysql_charset')
        conn=MySQLdb.connect(host=host,user=user,passwd=password,port=port,\
               charset=charset)

        return conn
    except MySQLdb.Error,e:
        print "Mysql Error %d: %s" % (e.args[0], e.args[1])

Answer 1

这几乎可以肯定是事务隔离的结果。由于您没有另外说明，我将假设您使用的是默认存储引擎 ( InnoDB ) 和隔离级别 ( REPEATABLE READ ):

REPEATABLE READ

The default isolation level for InnoDB. It prevents any rows that are queried from being changed by other transactions, thus blocking non-repeatable reads but not phantom reads. It uses a moderately strict locking strategy so that all queries within a transaction see data from the same snapshot, that is, the data as it was at the time the transaction started.

有关详细信息，请参阅 Consistent Nonlocking Reads在 MySQL 文档中。

用简单的英语来说，这意味着当您在事务中从表中SELECT时，您从表中读取的值在事务期间不会改变 ;您将继续看到事务打开时表的状态，以及在同一事务中所做的任何更改。

在您的情况下，每 3 秒更改一次是在其他 session 和事务中进行的。为了“看到”这些变化，您需要离开在发出第一个 SELECT 时开始的事务并开始一个新事务，然后它将“看到”表的新快照。

您可以使用 START TRANSACTION, COMMIT and ROLLBACK 显式管理交易在 SQL 中或通过调用 Connection.commit() and Connection.rollback() .这里更好的方法可能是利用 context managers ;例如:

conn = mysql_from_settings(settings)
with conn as cur:
    cur.execute('use database_a;')
    cur.execute('select pages from database_a_monitor where id=1;')
    result = cur.fetchone()[0]
print result
#during 15 second, I manually update the row and commit from mysql workbench
time.sleep(15)    

cur.execute('select pages from database_a_monitor where id=1;')
result = cur.fetchone()
print result
conn.close()

with 语句，当与 MySQLdb 的 Connection 对象一起使用时，返回一个游标。当您离开 with block 时，将调用 Connection.__exit__:

def __exit__(self, exc, value, tb):
    if exc:
        self.rollback()
    else:
        self.commit()

由于您所做的只是读取数据，因此无需回滚或提交；写入数据时，请记住通过异常离开 block 将导致您的更改回滚，而正常离开将导致您的更改被提交。

请注意，这并没有关闭游标，它只管理事务上下文。我在对 When to close cursors using MySQLdb 的回答中详细介绍了这个主题。但简而言之，您通常不必担心在使用 MySQLdb 时关闭游标。

您还可以通过 passing the database as a parameter to MySQLdb.connect 让您的生活更轻松一些而不是发出 USE 语句。

This answer to a very similar question提供了另外两种方法——您可以将隔离级别更改为 READ COMMITTED ，或打开 autocommit .