版本:
mysql5.7.39
问题:
flask项目在编辑报错后,导致mysql发生行锁定,无法查询对应的表数据。
错误信息如下:
sqlalchemy.exc.OperationalError: (raised as a result of Query-invoked autoflush; consider using a session.no_autoflush block if this flush is occurring prematurely) (pymysql.err.OperationalError) (1205, 'Lock wait timeout exceeded; try restarting transaction') [SQL: 'UPDATE video SET create_time=%(create_time)s, frame_audit_time=%(frame_audit_time)s, detect_date=%(detect_date)s WHERE video.id = %(video_id)s'] [parameters: {'create_time': '2022-08-31 09:35:01', 'frame_audit_time': '2022-08-31 10:43:04', 'detect_date': '2022-08-31 10:44:57', 'video_id': '16619132725566782'}]
sqlalchemy.exc.OperationalError: (raised as a result of Query-invoked autoflush; consider using a session.no_autoflush block if this flush is occurring prematurely) (pymysql.err.OperationalError) (1205, 'Lock wait timeout exceeded; try restarting transaction') [SQL: 'UPDATE video SET create_time=%(create_time)s, frame_audit_time=%(frame_audit_time)s, detect_date=%(detect_date)s WHERE video.id = %(video_id)s'] [parameters: {'create_time': '2022-08-31 09:35:01', 'frame_audit_time': '2022-08-31 10:43:04', 'detect_date': '2022-08-31 10:44:57', 'video_id': '16619132725566782'}]
必须重启服务, 才可以正常访问列表页面。
show processlist
原因:
锁等待超时,Mysql的 InnoDB存储引擎是支持事务的,事务开启后没有被主动Commit。导致该资源被长期占用,其他事务在抢占该资源时,因上一个事务的锁而导致抢占失败!因此出现 Lock wait timeout exceeded
解决方法:
db = SQLAlchemy(query_class=Query, session_options={'autocommit': True})
后续:
用上面方法解决上面问题后,又引入了新的问题。
处理方法:
mysql+pymysql://user:password@100.0.40.22:3306/pipeline_monitor_new?charset=utf8&autocommit=true
既解决了异常时,数据库行死锁的问题,又解决了增删改数据对事务的要求。
主要修改两个地方:
db = SQLAlchemy(query_class=Query)
SQLALCHEMY_DATABASE_URI = 'mysql+pymysql://user:password@100.0.40.22:3306/pipeline_monitor_new?charset=utf8&autocommit=true'