Python多线程共享Mysql连接出错？

Question

为什么会出现下面的错误呢？

#!/usr/bin/python2.7
#coding:utf-8
'''
Created on 2016年8月20日

@author: litten
'''
import MySQLdb
from threading import Thread
from time import sleep
conn = MySQLdb.connect(
                        host = '127.0.0.1',
                        port = 3306,
                        user = 'root',
                        passwd = 'root',
                        db = 'test',
                        charset = 'utf8'
                        )
def insert(name):
    #如果将conn = MySqldb.connect(...)放到这里来就可以了，为什么
    #多线程不是可以共享同一个连接吗
    cursor = conn.cursor()
    sql = 'insert into student (name) values("%s")' % (name)
    while True:
        print sql
        cursor.execute(sql)
        sleep(0.1)

if __name__ == '__main__':
    t = Thread(target=insert, args=('s1',))
    t.start()
    t2 = Thread(target=insert, args=('s2',))
    t2.start()
    t.join()
    t2.join()

下面是报错信息。

insert into student (name) values("s1")
insert into student (name) values("s2")
insert into student (name) values("s1")
insert into student (name) values("s2")
Exception in thread Thread-2:
Traceback (most recent call last):
  File "/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/threading.py", line 810, in __bootstrap_inner
    self.run()
  File "/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/threading.py", line 763, in run
    self.__target(*self.__args, **self.__kwargs)
  File "/Users/litten/Documents/workspace/DB/test_mysql_with_thread.py", line 24, in insert
    cursor.execute(sql)
  File "build/bdist.macosx-10.6-intel/egg/MySQLdb/cursors.py", line 173, in execute
    self.errorhandler(self, exc, value)
  File "build/bdist.macosx-10.6-intel/egg/MySQLdb/connections.py", line 36, in defaulterrorhandler
    raise errorclass, errorvalue
OperationalError: (2013, 'Lost connection to MySQL server during query')

insert into student (name) values("s1")
Exception in thread Thread-1:
Traceback (most recent call last):
  File "/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/threading.py", line 810, in __bootstrap_inner
    self.run()
  File "/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/threading.py", line 763, in run
    self.__target(*self.__args, **self.__kwargs)
  File "/Users/litten/Documents/workspace/DB/test_mysql_with_thread.py", line 24, in insert
    cursor.execute(sql)
  File "build/bdist.macosx-10.6-intel/egg/MySQLdb/cursors.py", line 173, in execute
    self.errorhandler(self, exc, value)
  File "build/bdist.macosx-10.6-intel/egg/MySQLdb/connections.py", line 36, in defaulterrorhandler
    raise errorclass, errorvalue
OperationalError: (2006, 'MySQL server has gone away')

Traceback (most recent call last):
  File "/Users/litten/Documents/workspace/DB/test_mysql_with_thread.py", line 35, in <module>
    conn.commit()
_mysql_exceptions.OperationalError: (2006, 'MySQL server has gone away')

Answer 1

这个连接不是线程安全的，什么情况都会出现，参见官方文档：

threadsafety
Integer constant stating the level of thread safety the interface supports. This is set to 1, which means: Threads may share the module.

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.

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.

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

Answer 2

cursor = conn.cursor()创建了，但是没有关闭
cursor.close()