Error using multithreading and mysqldb

Question

getting errors while accessing data with support for multiple files.

Exception in thread Thread-2: ProgrammingError: (2014, "Commands out of sync; you can't run this command now") Exception in thread Thread-3: ProgrammingError: execute() first 

Answer 1

According to PEP 249 , data access modules have a threadsafety module level constant:

An integer constant indicating the security level of the interface stream supports. Possible values:

0 Topics may not share a module.
1 Themes can be shared by the module, but not by connections.
2 Themes can be shared by module and connections.
3 Themes can be shared by module, connections and cursors.

Sharing in the above context means that two threads can use a resource without wrapping it using a mutex semaphore to implement a lock resource. Please note: you cannot always create a stream of external resources in a secure way by controlling access using the mutex: the resource can rely on global variables or other external sources that are not subject to control.

According to the MySQLdb User Guide, the module supports level 1.

The MySQL protocol cannot handle multiple threads using the same connection at once. Some earlier versions of MySQLdb used locking to provide security for ceiling 2. Although this is not very difficult to do using the standard Cursor class (which uses mysql_store_result ()), this is complicated by SSCursor (which uses mysql_use_result (); with the latter, you need to make sure that everything rows have been read before another request can be made, which is complicated by adding transactions, because transactions begin when the cursor executes the request, but ends when the COMMIT or ROLLBACK executed by the Connecti object on. Two threads simply cannot share while the transaction is running, in addition to not being able to share it during the execution of the request, it is overly complex code to such an extent that it simply is not worth it.

Overall result: do not split the links between threads. It really is not worth your efforts or mine, and in the end is likely to hurt performance, as the MySQL server runs a separate thread for each connection. You can certainly do things like the connection cache in the pool and give these connections to one thread at a time. If you allow two threads to use the connection simultaneously, the MySQL Client Library is likely to work and die. You were warned.

Answer 2

here is the error detail: http://dev.mysql.com/doc/refman/5.0/en/commands-out-of-sync.html .

The Mysqldb manual offers the following:

Do not share connections between threads. It really is not worth your efforts or mine, and in the end, it can hurt performance, as the MySQL server starts a separate thread for each connection. You can certainly do things like caching in a pool, and transfer these connections one stream at a time. If you allow two threads to use the connection at the same time, the MySQL client library is likely to work and die. You have been warned.

For streaming applications, try using a connection pool. This can be done using the pool module.

Learn more about finding the threadsafety keyword in the MySQLdb Handbook .

Answer 3

Thanks to your little information, I can only guess.

You are probably accessing the database from multiple threads without blocking. This is bad.

When accessing the database, you must hold the threading.Lock() or threading.RLock() lock. This prevents multiple threads from interfering with other flow activities.