[已解决]sqlalchemy会长时间占用数据库锁吗？

NZND

如题。我的项目一开始采用的是sqlalchemy，后来在部分地方改为了pymysql。
结果发现，pymysql在执行删除语句的时候，一直在等待一个锁或者类似锁的东西释放，就卡在那了。用mysql直接删也没用，只有把程序停下来才正常。
是sqlalchemy在运行期间会把MySQL的删除功能上锁吗？应该怎么解决？要把sqlalchemy的代码全部换掉吗？

最佳答案

月排行榜 / 总排行榜

kogawananari

2022-6-22 20:03:34

没用过flask_sqlalchemy   只用过sqlalchemy 没遇到过占用的情况 看了一眼你的代码 似乎是你一种保持着会话并且默认开启了事务？
建议使用会话元类来生成会话 sessionmaker(bind=engine)  每次执行完关闭会话 。

当然 还有一种解法 那就是不开启事务 。你需要开启事务的时候 手动with session.begin()就行了

跳转到最佳答案楼层

kogawananari

得看你sqlalchemy代码

NZND

kogawananari 发表于 2022-6-21 20:41
得看你sqlalchemy代码

sqlalchemy:

1. """
   
2. Routes and views for the flask application.
   
3. """
   
4. 
   
5. from flask import render_template, request
   
6. from HelloWorld_Controller import app
   
7. import os
   
8. import json
   
9. import pydub
   
10. import threading
    
11. import time
    
12. import pydub.exceptions
    
13. import copy
    
14. import re
    
15. from . import mysql_controller as mc
    
16. import datetime
    
17. import hashlib
    
18. import random
    
19. import base64
    
20. from flask_sqlalchemy import SQLAlchemy
    
21. import pymysql
    
22. import filetype
    
23. 
    
24. app.config['SQLALCHEMY_DATABASE_URI'] = "mysql+pymysql://RWUser:d774ab2befc390901bc738ed735ce256b7b07dd35175c2aace2bc3517b5264ec@localhost/HelloWorldServerDatabase"
    
25. 
    
26. app.config['SQLALCHEMY_COMMIT_TEARDOWN'] = True
    
27. app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = True
    
28. app.config['SQLALCHEMY_COMMIT_ON_TEARDOWN'] = True
    
29. 
    
30. config = {}
    
31. necessaryConfigs = ["baseDIR", "DB", "DB.cleanDB", "firstStartup"]
    
32. try:
    
33.     with open("config.json") as f:
    
34.         text = f.read()
    
35.         text = text.replace("\n", "")
    
36.         config = json.loads(text)
    
37.         #print("dee")
    
38. except Exception:
    
39.    
    
40.     config = json.loads(r"""
    
41. {
    
42.     "baseDIR": "D:\\HelloWorld_Server\",
    
43.     "DB": {
    
44.         "cleanDB": false,
    
45.         "create_new": false
    
46.     }
    
47. }""")
    
48. 
    
49. for each in necessaryConfigs:
    
50.     if ("." in each):
    
51.         splited = each.split(".")
    
52.         now = config
    
53.         for i in range(len(splited)):
    
54.             try:
    
55.                 now = now[splited[i]]
    
56.             except KeyError:
    
57.                 raise AssertionError("Cannot find necessary config named %s." % each)
    
58.     else:
    
59.         try:
    
60.             config[each]
    
61.         except KeyError:
    
62.             raise AssertionError("Cannot find necessary config named %s." % each)
    
63. db = SQLAlchemy(app)
    
64. 
    
65. class Token(db.Model):
    
66.     id = db.Column(db.BigInteger, primary_key=True)
    
67.     username = db.Column(db.String(300), nullable=False)
    
68.     token = db.Column(db.String(100), unique=True, nullable=False)
    
69.     vaildTime = db.Column(db.DateTime, unique=True)
    
70. 
    
71. class User(db.Model):
    
72.     id = db.Column(db.BigInteger, primary_key=True)
    
73.     username = db.Column(db.String(300), unique=True, nullable=False)
    
74.     password_hash = db.Column(db.String(128), nullable=False)
    
75.     salt = db.Column(db.String(10), nullable=False)
    
76. 
    
77. class Evidence(db.Model):
    
78.     id = db.Column(db.BigInteger, primary_key=True)
    
79.     username = db.Column(db.String(300), nullable=False)
    
80.     evidence = db.Column(db.String(150), unique=True, nullable=False)
    
81.     vaildTime = db.Column(db.DateTime)
    
82. #db.create_all()
    
83. 
    
84. BASE_PATH = config["baseDIR"]
    
85. temps = []
    
86. try:
    
87.     config["DB"]["create_new"]
    
88. except:
    
89.     config["DB"]["create_new"] = False
    
90. 
    
91. if (config["DB"]["cleanDB"] or config["firstStartup"]):
    
92.     db.drop_all()
    
93.     db.create_all()
    
94.     if (config["firstStartup"]):
    
95.         config["firstStartup"] = False
    
96.         with open("config.json", "w") as f:
    
97.             json.dump(config, f, indent=4)
    
98. elif (config["DB"]["create_new"]):
    
99.     db.create_all()
    
100. 
     
101. def new_token(username : str):
     
102.     lst = [e for e in username]
     
103.     res = ""
     
104.     for i in range(len(lst)):
     
105.         index = random.randint(0, len(lst) - 1)
     
106.         res += lst[index]
     
107.         lst.pop(index)
     
108.         lst.reverse()
     
109.     res += str(time.time()) + "efj;"
     
110.     res += str(random.randint(0, 5023))
     
111.     res = res.encode(random.choice(['utf-32', 'utf-16', 'utf-8']))
     
112.     return str(int("0x" + hashlib.sha256(res).hexdigest(), 16))
     
113. 
     
114. def compute_evidence(username):
     
115.     evidence = hashlib.sha512(str(time.time() + random.randint(-100, 100) + random.random() * random.choice([-1, 1])).encode()).hexdigest()
     
116.     info = Evidence(evidence=evidence, username=username, vaildTime=datetime.datetime.now() + datetime.timedelta(0, 0, 0, 0, 25.5, 0, 0))
     
117.     db.session.add(info)
     
118.     return evidence
     
119. 
     
120. def eff(db):
     
121.     lock = threading.RLock()
     
122.     while True:
     
123.         lock.acquire()
     
124.         db.session.commit()
     
125.         lock.release()
     
126.         time.sleep(5)
     
127. 
     
128. thread2 = threading.Thread(target=lambda:eff(db))
     
129. thread2.start()
     
130. 
     
131. @app.route("/python/account/loginState", methods=["GET", "POST"])
     
132. def LoginStateCheck():
     
133.     data = ""
     
134.     if (request.method == "POST"):
     
135.         data = request.form.get("token")
     
136.     elif (request.method == "GET"):
     
137.         data = request.args.get("token")
     
138.     retsder = Token.query.filter_by(token=data).first()
     
139.     if (retsder == None):
     
140.         return "invaild"
     
141.     #retsder = retsder#.first()
     
142.     if (retsder.vaildTime <= datetime.datetime.now()):
     
143.             db.session.delete(retsder)
     
144.             return "invaild"
     
145.     else:
     
146.         _token = new_token(retsder.username)
     
147.         _username = retsder.username
     
148.         info = Token(username=_username, token=_token, vaildTime=retsder.vaildTime)
     
149.         db.session.delete(retsder)
     
150.         db.session.add(info)
     
151.         return json.dumps({"token": _token, "username": _username,
     
152.                            "evidence": compute_evidence(_username)})
     
153. 
     
154. class DictTool:
     
155.     @staticmethod
     
156.     def reverse(dic):
     
157.         nd = {}
     
158.         for k, v in dic.items():
     
159.             nd[v] = k
     
160.         return nd
     
161.     @staticmethod
     
162.     def keys(dic):
     
163.         return [k for k, v in dic.items()]
     
164.     @staticmethod
     
165.     def vals(dic):
     
166.         return [v for k, v in dic.items()]
     
167. 
     
168. @app.route("/python/account/checkEvidence", methods=["GET", "POST"])
     
169. def CheckEvi():
     
170.     global evi
     
171.     data = ""
     
172.     if (request.method == "POST"):
     
173.         data = request.form.get("evidence")
     
174.     elif (request.method == "GET"):
     
175.         data = request.args.get("evidence")
     
176.     test = Evidence.query.filter_by(evidence=data).first()
     
177.     if (test == None):
     
178.         return "false"
     
179.     else:
     
180.         return "true"
     
181. 
     
182. @app.route("/python/account/login", methods=["GET", "POST"])
     
183. def Login():
     
184.     global evi, db
     
185.     username = ""
     
186.     password = ""
     
187.     withToken = False
     
188.     if (request.method == "GET"):
     
189.         username = request.args.get("username")
     
190.         password = request.args.get("password")
     
191.         withToken = request.args.get("withtoken")
     
192.     else:
     
193.         username = request.form.get("username")
     
194.         password = request.form.get("password")
     
195.         withToken = request.form.get("withtoken")
     
196.     username = base64.decodebytes(username.encode()).decode()
     
197.     password = base64.decodebytes(password.encode()).decode()
     
198.     withToken = True if withToken == "true" else False
     
199. 
     
200.     query = User.query.filter_by(username=username).first()
     
201.     if (query == None):
     
202.         return "nouser"
     
203.     elif (str(int("0x" + hashlib.sha256((username + query.salt).encode()).hexdigest(), 16)) != query.password_hash):
     
204.         return "wrongpassword"
     
205.     evidence = compute_evidence(username)
     
206.     if (withToken):
     
207.         token = new_token(username)
     
208.         info = Token(username=username, token=token, vaildTime=datetime.datetime.now() + datetime.timedelta(5, 0, 0, 0, 0, 0, 0))
     
209.         db.session.add(info)
     
210.         return json.dumps({"username": username, "token": token, "evidence": evidence})
     
211.     return json.dumps({"username": username, "evidence": evidence})
     
212. 
     
213. @app.route("/python/account/signup", methods=["POST", "GET"])
     
214. def Signup():
     
215.     global db
     
216.     rf = request.args
     
217.     if (request.method == "POST"):
     
218.         rf = request.form
     
219.     username = base64.decodebytes(rf.get("username").encode()).decode()
     
220.     password = base64.decodebytes(rf.get("password").encode()).decode()
     
221. 
     
222.     if (User.query.filter_by(username=username).first() != None):
     
223.         return "userexisted"
     
224.     d = "abcdefghijklmnopqrstuvwxyz"
     
225.     d += d.upper()
     
226.     d += "1234567890!@#$%^&*()~-=_+{}[]\\|:";\'<>?,./ "
     
227.     d = [e for e in d]
     
228.     salt = "".join([random.choice(d) for i in range(random.randint(4, 10))])
     
229.     hashobj = hashlib.sha256((password + salt).encode())
     
230.     hexhash = hashobj.hexdigest()
     
231.     integerhash = int("0x" + hexhash, 16)
     
232.     decimalhash = str(integerhash)
     
233.     info = User(username=username, salt=salt, password_hash=integerhash)
     
234.     db.session.add(info)
     
235.     return "Completed.Username:%s, Password:%s" % (username, password)
     
236. 
     
237. 
     
238. def getPhyhicsPath(uri):
     
239.     global BASE_PATH
     
240.     return BASE_PATH + uri[1:].replace("/", "\")
     

复制代码

然后是pymysql的代码：

1. conn = pymysql.connect(host="127.0.0.1", user="recordCleaner", password="faf76827f1634a501544bb6f668efe4a6882101f9783f33ca790308173be0ec5", database="helloworldserverdatabase")
   
2. cur = conn.cursor(pymysql.cursors.DictCursor)
   
3. while True:
   
4.     cur.execute("delete from evidence where vaildTime <= CURTIME()")
   
5.     cur.fetchall()
   
6.     cur.execute("select id, evidence from evidence order by id")
   
7.     ret = cur.fetchall()
   
8.     lastid = 0
   
9.     for each in ret:
   
10.         if (each["id"] - lastid != 1):
    
11.             cur.execute("update evidence set id=%d where id=%d and evidence='%s'" % (lastid + 1, each["id"], each['evidence']))
    
12.         lastid = lastid + 1
    
13.     cur.execute("delete from token where vaildTime <= CURTIME()")
    
14.     cur.fetchall()
    
15.     cur.execute("select id, token from token order by id")
    
16.     ret = cur.fetchall()
    
17.     lastid = 0
    
18.     for each in ret:
    
19.         if (each["id"] - lastid != 1):
    
20.             cur.execute("update token set id=%d where id=%d and token='%s'" % (lastid + 1, each["id"], each['token']))
    
21.         lastid = lastid + 1
    
22.     time.sleep(2)
    

复制代码

用的是flask。

kogawananari

没用过flask_sqlalchemy   只用过sqlalchemy 没遇到过占用的情况 看了一眼你的代码 似乎是你一种保持着会话并且默认开启了事务？
建议使用会话元类来生成会话 sessionmaker(bind=engine)  每次执行完关闭会话 。

当然 还有一种解法 那就是不开启事务 。你需要开启事务的时候 手动with session.begin()就行了