訪問數(shù)據(jù)庫的線程安全等級在下表中定義：
Integer constant stating the level of thread safety the interface supports. Possible values are:
| threadsafety | Meaning |
|---|---|
| 0 | Threads may not share the module. |
| 1 | Threads may share the module, but not connections. |
| 2 | Threads may share the module and connections. |
| 3 | Threads may share the module, connections and cursors. |
Sharing in the above context means that two threads may use a resource without wrapping it using a mutex semaphore to implement resource locking. Note that you cannot always make external resources thread safe by managing access using a mutex: the resource may rely on global variables or other external sources that are beyond your control.
pymysql的線程安全等級為1：
from ._compat import PY2
from .constants import FIELD_TYPE
from .converters import escape_dict, escape_sequence, escape_string
from .err import (
Warning, Error, InterfaceError, DataError,
DatabaseError, OperationalError, IntegrityError, InternalError,
NotSupportedError, ProgrammingError, MySQLError)
from .times import (
Date, Time, Timestamp,
DateFromTicks, TimeFromTicks, TimestampFromTicks)
VERSION = (0, 7, 11, None)
threadsafety = 1
apilevel = "2.0"
paramstyle = "pyformat"
在實際的項目中蕉斜，一種經(jīng)典的錯誤的使用方式就是全局使用一個conntction应媚，這樣如果有多個線程侯勉，同時使用一個connection來查詢或操作數(shù)據(jù)庫胯府，數(shù)據(jù)庫就會出現(xiàn)如下問題：
mysql error sql: Packet sequence number wrong - got 1 expected 2
for this sql query:
一種解決方法是使用線程池，每個線程采用獨立的connection宏粤，但是這種方式對于并發(fā)量特別大的情況下脚翘，會造成利用效率比較低的問題。
另外的一種方法是绍哎，使用多線程同步方法来农，加入鎖控制信號量。
具體來講崇堰，一個簡單的demo沃于，但是要留心死鎖問題：
#set up a mutex
mutex = 0
connection = pymysql.connect(host='xxx.xxx.xxx',
user='xxx',
password='xxxx',
db='xxx',
charset='utf8',
cursorclass=pymysql.cursors.DictCursor)
# query for a fetchall
def qurey_all_sql(sql):
while mutex == 1:
time.sleep(500)
mutex = 1
cur = connection.cursor()
cur.execute(sql)
result = cur.fetchall()
connection.commit()
cur.close()
mutex = 0
return result
# query for a result
def query_one_sql(sql):
while mutex == 1:
time.sleep(500)
mutex = 1
cur = connection.cursor()
cur.execute(sql)
result = cur.fetchone()
connection.commit()
cur.close()
mutex = 0
return result
原作者在github上給出的解決方案如下:
I use SQLAlchemy's pool.
Another way is threadpool. Each worker thread can have own connection.
更高級的方法有《基于gevent和pymysql實現(xiàn)mysql讀寫的異步非堵塞方案》，具體見鏈接海诲。
