当sqlalchemy/pymysql遇到多任务(多线程/多进程)访问mysql，session.close() ，engine.dispose，事务session.rollback()

当sqlalchemy/pymysql遇到多任务(多线程/多进程/协程)

sqlalchemy和pymysql的使用方法不在此做过多介绍，不了解的可以访问我的另外两篇文章

• sqlalchemy ：flask-sqlalchemy和sqlalchemy（flask-sqlalchemy使用最需要注意的地方）–> https://editor.csdn.net/md/?articleId=116299608，此链接只是flask-sqlalchemy与sqlalchemy的不同解释，具体想了解sqlalchemy还请搜索别的博客
• pymysql ：pymysql详解（connect连接、游标cursor、获取查询结果集、insert into需要注意的地方）https://editor.csdn.net/md/?articleId=108546128

​ 这里将sqlalchemy/pymysql与多线程/多进程的使用单独说明
​ 首先我们在使用sqlalchemy或者pymysql的时候都会先通过不同的引擎连接方式与对应的数据库建立连接，然后通过建立好的连接进行一系列的增删改查操作，以MySQL数据库为例，我们都知道，查询的运行速度要远远大于增删改的速度，如果业务逻辑不涉及多任务执行的方式，那么只创建一个连接，去操作数据库是完全没有问题的。

​ 但如果是多任务执行的时候呢，以sqlalchemy举个例子

​

def func1():
	user_data = db.session.query(User).all()
	for user in user_data:
		user.sex = "man"
		db.session.add(user)
		db.session.commit()

​ 这是一个简单的修改数据表数据的例子，我们假设User中有100条数据，修改每条数据需要花费0.5秒（实际不会这么慢）

​

def func2():
	for _ in range(100):
		user_data = db.session.query(User).all()
		db.session.close()

​ 这是一个简单的查询数据表数据的例子，只不过在此处做了100次循环，我们假设100次查询需要1秒（实际不会这么慢）

t1 = threading.Thread(target=func1)
t1.start()
t2 = threading.Thread(target=func2)
t2.start()

​ 用两个线程将两个函数并行执行

​ 提示：在程序查询结束时最好将连接归还给连接池，不仅可以避免资源的浪费，还可以避免一些bug的发生，具体会发生什么bug感兴趣的可以去查查，归还可以调用close() 方法，这里的close方法只是将当前连接归还给了建立的连接池，并不是将整个engine连接关闭，如果需要将engine连接关闭，则需要engine.dispose() 方法关闭连接，这个方法也很重要

close() 和 dispose() 是有着本质上的区别
close() 不会将通过engine与mysql建立的连接关闭，而是将当前使用的连接归还给engine所建立的连接的连接池中
dispose() 则是直接将整个engine与mysql建立的连接关闭，当使用dispose()方法后，如果没有新的连接，是没有办法继续操作数据库的

因为mysql允许建立的连接数量是有限的，记得最大连接数好像是3400个，感兴趣的可以去查一下，默认允许建立的连接数好像是100个，可以在/etc/mysql/my.cnf中去设置允许建立的最大连接数，方法如下：

interactive_timeout=600
wait_timeout=600
这两个是设置连接的保持时间的，以秒未单位，慎用

…
max_connections=10000
这个是设置允许的最大连接数的
这个如果my.cnf文件中没有的话直接写在最下面即可

如果超出最大连接数则会报如下错误：

(pymysql.err.OperationalError) (1040， ‘ny connections)
我这里是通过pymysql创建的连接，这个错误也很明显，建立连接失败

​

提出问题

​ 上面这两个函数方法公用同一个连接，即同一个db，那么上述例子会发生什么样的结果呢

第一种结果就是一切正常

第二种结果是 pymysql.err.OperationalError: (2013, xxxx) 错误，发生这个错误是因为数据库连接异常

第三种结果是 NodeType object has no attribute sex 发生这个错误是因为没有查询到User数据

好像还有别的错误，但忘记记录了

​ ****

问题解析

​ python中线程的执行是抢占式的

​ 发生上面错误的主要原因就是因为任务并行执行，因为共用一个连接，当func2执行查询后，执行了db.session.close() 断开了当前访问，但任务是并行的，如果此时func1正在执行db.session.commit()，提交事务的时候，连接被断开了，那么就会发生第二种结果异常

​ 当func1正在提交数据，func2在查询，func1完成提交，func2还未查完数据，事务被提交，func2可能什么也没查到，或者查到了脏数据

​

解决方法

​ 其实到这里很多人应该已经有想法了

​ 此处博主提出一种解决方案：

​ 既然一个连接会发生这样的异常，那么不同的方法可以建立属于自己的连接，多个并行任务种每个单独的任务都建立属于自己的连接，在单独的任务中连接就不会被抢占，不会被别的任务关闭

​ 那么可以将连接封装为一个方法:

​ 此方法主要是利用连接池，对每个需要单独启动的线程单建一个连接，供该线程自己使用，达到多线程/多进场 下各连接互不干扰。
但如果执行的是commit操作，那么rollback()事务回滚就显得格外重要了。结合try except 对commit提交事务的进行异常捕获，若捕获到异常，则rollback(）。不然会导致该条数据被锁死，rollback事务没有回滚，不论是单独查询该事务，还是修改该事务，得到的结果都是

	```
	This Session's transaction has been rolled back due to a previous exception during flsh. To begin a new transaction with this Session, xxx Session.rollback().xxxxxxxxx
	```

engine = create_engine(url)
def db():
	session = sessionmaker(bind=engine)
	dbSession = session()
	return dbSession

​ 此处用到的式sqlalchemy建立连接，不是flask-sqlalchemy， 上述举例中的func1与func2的db.session是flask-sqlalchemy建立连接的方式，具体区别可以访问：flask-sqlalchemy和sqlalchemy（flask-sqlalchemy使用最需要注意的地方）–> [https://editor.csdn.net/md/?articleId=116299608]

​ 上述func1和func2则可以改为

	def func1():
		dbSession = db()
	    try:
	    	user_data = dbSession.query(User).all()
		    for user in user_data:
		        user.sex = "man"
		        dbSession.add(user)
		        dbSession.commit()
	    except Exception as e:
	    	dbSession.rollback()
	    	print(str(e))

def func2():
	dbSession = db()
	try:
		for _ in range(100):
			user_data = dbSession.query(User).all()
			dbSession.close()
   except Exception as e:
    	print(str(e))

t1 = threading.Thread(target=func1)
t1.start()
t2 = threading.Thread(target=func2)
t2.start()

​ 用两个线程将两个函数并行执行则不会在发生上述问题了
第二种解决方法则是通过锁机制了，但也失去了多任务并行的意义