为什么还存在“命令不同步"的问题?您不能立即运行此命令"错误 [英] Why still has "commands out of sync; you can't run this command now" error

问题描述

我正在使用Python mysqldb库连接mysql db.我有一个带有4个工作进程的Web服务器，该进程具有 1个conn和1个游标到mysql db.因此，每个工作进程都将使用其连接/游标执行sql语句.

I am using Python mysqldb library to connect mysql db. I have a web server with 4 worker process which has 1 conn and 1 cursor to mysql db. so every worker process will use its connection/cursor to execute sql sentence.

现在，我有几个客户端要同时向服务器发送请求，服务器将查询mysql db，并将一些结果返回给客户端.我遇到错误. 2014，命令不同步；您现在不能运行此命令"
我已经检查了sql，就像SELECT a, b, c from table WHERE a = 1一样简单.没有分号或存储过程，我也尝试下面的代码作为建议.但是它仍然是同样的错误.

Now, I am have several client to simultaneously to send request to server, and server will query mysql db, and return some result to client. I encounter error. 2014, "Commands out of sync; you can't run this command now"
I have check sql, it just simple as SELECT a, b, c from table WHERE a = 1. There is no semicolon, or store procedure, and I also try below code as Python, "commands out of sync; you can't run this command now" suggest. but it still same error.

self.cursor.execute(sql, data)
self.conn.commit()
result = result + self.cursor.fetchall()
self.cursor.close()
self.cursor = self.conn.cursor() 

推荐答案

最后，我解决了这个问题.我的应用程序具有使用同一连接的多线程，似乎不是访问mysql的正确方法，因此当我不共享连接时，问题就消失了.

Finally, I fixed this issue. My app has multithread to use the same connection, it seems is not a proper way to access mysql, so when I do not share connection, the issue is gone.

在 MySQLdb用户指南的线程安全"下:

MySQL协议无法使用同一协议处理多个线程 一次连接. MySQLdb的某些早期版本利用锁定 达到2的线程安全性. 使用标准的Cursor类(使用 mysql_store_result())，由SSCursor(使用 mysql_use_result();对于后者，您必须确保所有行都具有 在执行另一个查询之前已被读取.更进一步 自交易开始以来，由于增加交易而变得复杂 当游标执行查询时，但在COMMIT或ROLLBACK为 由Connection对象执行.两个线程根本无法共享一个 正在进行交易时的连接，除了没有 能够在查询执行期间共享它.这太过分了 将代码复杂化到不值得的程度.

The MySQL protocol can not handle multiple threads using the same connection at once. Some earlier versions of MySQLdb utilized locking to achieve a threadsafety of 2. While this is not terribly hard to accomplish using the standard Cursor class (which uses mysql_store_result()), it is complicated by SSCursor (which uses mysql_use_result(); with the latter you must ensure all the rows have been read before another query can be executed. It is further complicated by the addition of transactions, since transactions start when a cursor execute a query, but end when COMMIT or ROLLBACK is executed by the Connection object. Two threads simply cannot share a connection while a transaction is in progress, in addition to not being able to share it during query execution. This excessively complicated the code to the point where it just isn't worth it.

此操作的总体结果是:不要共享彼此之间的连接 线程.确实不值得您付出我的努力，最后， 这可能会影响性能，因为MySQL服务器运行单独的 每个连接的线程.您当然可以做诸如高速缓存之类的事情 池中的连接，并将这些连接分配给一个线程 时间.如果让两个线程同时使用连接，则 MySQL客户端库可能会崩溃.你去过 警告.

The general upshot of this is: Don't share connections between threads. It's really not worth your effort or mine, and in the end, will probably hurt performance, since the MySQL server runs a separate thread for each connection. You can certainly do things like cache connections in a pool, and give those connections to one thread at a time. If you let two threads use a connection simultaneously, the MySQL client library will probably upchuck and die. You have been warned.

这篇关于为什么还存在“命令不同步"的问题?您不能立即运行此命令"错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！