python?peewee操作sqlite鎖表的問(wèn)題分析
在使用python orm 框架 peewee 操作數(shù)據(jù)庫(kù)時(shí)時(shí)常會(huì)拋出以一個(gè)異常，具體的報(bào)錯(cuò)就是 database is locked
初步了解是因?yàn)閟qlite鎖的顆粒度比較大，是庫(kù)鎖。當(dāng)一個(gè)連接在寫(xiě)數(shù)據(jù)庫(kù)時(shí)，另一個(gè)連接在想要寫(xiě)任意一張表都會(huì)報(bào)錯(cuò)。
為了解決這個(gè)問(wèn)題，做如下的實(shí)驗(yàn)分析問(wèn)題
1.理論分析
SQLite 是一個(gè)軟件庫(kù)，實(shí)現(xiàn)了自給自足的、無(wú)服務(wù)器的、零配置的、事務(wù)性的 SQL 數(shù)據(jù)庫(kù)引擎。
SQLite允許多個(gè)進(jìn)程/線(xiàn)程同時(shí)進(jìn)行讀操作，但在同一時(shí)刻只允許一個(gè)線(xiàn)程進(jìn)行寫(xiě)操作。SQLite在進(jìn)行寫(xiě)操作時(shí)，數(shù)據(jù)庫(kù)文件會(huì)被鎖定，此時(shí)任何其他的讀/寫(xiě)操作都會(huì)被阻塞，如果阻塞超過(guò)5秒鐘，就會(huì)拋出描述為“database is locked”的異常。
出現(xiàn)上述現(xiàn)象的原因是SQLite只支持庫(kù)級(jí)鎖，不支持并發(fā)執(zhí)行寫(xiě)操作，即使是不同的表，同一時(shí)刻也只能進(jìn)行一個(gè)寫(xiě)操作。
例如，事務(wù)T1在表A新插入一條數(shù)據(jù)，事務(wù)T2在表B中更新一條已存在的數(shù)據(jù)，這兩個(gè)操作是不能同時(shí)進(jìn)行的，只能順序進(jìn)行。
2.建表
import datetime
from peewee import AutoField, DateTimeField, Model, SqliteDatabase, TextField, IntegerField
db = SqliteDatabase('my_app.db', pragmas={'journal_mode': 'wal'})
class BaseModel(Model):
"""A base model that will use our Sqlite database."""
id = AutoField()
update_time = DateTimeField(default=datetime.datetime.now)
class Meta:
database = db
class User(BaseModel):
name = TextField()
age = IntegerField()
class Meta:
table_name = "user"
if __name__ == "__main__":
db.connect()
db.create_tables([User])
User.create(name="ljk", age=29)
res = User.select()
for i in res:
print(i.name, i.age)3.串行寫(xiě)操作不會(huì)鎖庫(kù)
串行執(zhí)行不會(huì)鎖表，同時(shí)也說(shuō)明事務(wù)完成之后鎖立即釋放
import time
import threading
from peewee_demo import User
def write_sql(num):
user = User.get_by_id(1)
print(f"傳入數(shù)值：{num}")
print("睡眠10s, 開(kāi)始")
time.sleep(10)
print("睡眠10s, 結(jié)束")
user.age = num
user.save()
write_sql(100)
write_sql(300)傳入數(shù)值：100
睡眠10s, 開(kāi)始
睡眠10s, 結(jié)束
傳入數(shù)值：300
睡眠10s, 開(kāi)始
睡眠10s, 結(jié)束
4.兩個(gè)線(xiàn)程同時(shí)寫(xiě)會(huì)鎖表
import time
import random
import threading
from peewee_demo import User
def write_sql(index):
users = User.select()
for user in users:
user.age = random.randint(100, 200)
print(f"in {index} , now is {time.time()}")
user.save()
if __name__ == "__main__":
p1 = threading.Thread(target=write_sql, args=(1, ))
p2 = threading.Thread(target=write_sql, args=(2, ))
p1.start()
p2.start()
p1.join()
p2.join()(idt_dev) ? peewee_sqlite python main.py
in 1 , now is 1691136403.4496074
in 2 , now is 1691136403.4499302
Exception in thread Thread-2:
Traceback (most recent call last):
File "/home/ljk/.virtualenvs/idt_dev/lib/python3.8/site-packages/peewee.py", line 3246, in execute_sql
cursor.execute(sql, params or ())
sqlite3.OperationalError: database is locked
During handling of the above exception, another exception occurred:
Traceback (most recent call last):
File "/usr/local/lib/python3.8/threading.py", line 932, in _bootstrap_inner
self.run()
File "/usr/local/lib/python3.8/threading.py", line 870, in run
self._target(*self._args, **self._kwargs)
File "main.py", line 13, in write_sql
user.save()
File "/home/ljk/.virtualenvs/idt_dev/lib/python3.8/site-packages/peewee.py", line 6785, in save
rows = self.update(**field_dict).where(self._pk_expr()).execute()
File "/home/ljk/.virtualenvs/idt_dev/lib/python3.8/site-packages/peewee.py", line 1966, in inner
return method(self, database, *args, **kwargs)
File "/home/ljk/.virtualenvs/idt_dev/lib/python3.8/site-packages/peewee.py", line 2037, in execute
return self._execute(database)
File "/home/ljk/.virtualenvs/idt_dev/lib/python3.8/site-packages/peewee.py", line 2555, in _execute
cursor = database.execute(self)
File "/home/ljk/.virtualenvs/idt_dev/lib/python3.8/site-packages/peewee.py", line 3254, in execute
return self.execute_sql(sql, params)
File "/home/ljk/.virtualenvs/idt_dev/lib/python3.8/site-packages/peewee.py", line 3246, in execute_sql
cursor.execute(sql, params or ())
File "/home/ljk/.virtualenvs/idt_dev/lib/python3.8/site-packages/peewee.py", line 3014, in __exit__
reraise(new_type, new_type(exc_value, *exc_args), traceback)
File "/home/ljk/.virtualenvs/idt_dev/lib/python3.8/site-packages/peewee.py", line 192, in reraise
raise value.with_traceback(tb)
File "/home/ljk/.virtualenvs/idt_dev/lib/python3.8/site-packages/peewee.py", line 3246, in execute_sql
cursor.execute(sql, params or ())
peewee.OperationalError: database is locked
in 1 , now is 1691136403.4617224
in 1 , now is 1691136403.467874
in 1 , now is 1691136403.475302
in 1 , now is 1691136403.4822652
in 1 , now is 1691136403.489331
in 1 , now is 1691136403.4965873
in 1 , now is 1691136403.5043068
in 1 , now is 1691136403.5117881
in 1 , now is 1691136403.5194569
in 1 , now is 1691136403.5266187
in 1 , now is 1691136403.5337832
in 1 , now is 1691136403.5410187
in 1 , now is 1691136403.5481625
in 1 , now is 1691136403.555381
in 1 , now is 1691136403.5625844
in 1 , now is 1691136403.569803
in 1 , now is 1691136403.5772254
in 1 , now is 1691136403.5843408
in 1 , now is 1691136403.5914726
5.同時(shí)一個(gè)讀+一個(gè)寫(xiě)不會(huì)鎖表
import time
import random
import threading
from peewee_demo import User
def write_sql(index):
users = User.select()
for user in users:
user.age = random.randint(100, 200)
print(f"in write {index} , now is {time.time()}")
user.save()
def read_sql(index):
users = User.select()
for user in users:
print(f"in read {index}, now is {time.time()}, name: {user.name}")
if __name__ == "__main__":
p1 = threading.Thread(target=write_sql, args=(1, ))
p2 = threading.Thread(target=read_sql, args=(2, ))
p1.start()
p2.start()
p1.join()
p2.join()in write 1 , now is 1691136578.3930526
in read 2, now is 1691136578.3933816, name: person_P0
in read 2, now is 1691136578.3934226, name: person_P1
in read 2, now is 1691136578.3934548, name: person_P2
in read 2, now is 1691136578.3934836, name: person_P3
in read 2, now is 1691136578.3935122, name: person_P4
in read 2, now is 1691136578.3935406, name: person_P5
in read 2, now is 1691136578.3935676, name: person_P6
in read 2, now is 1691136578.393595, name: person_P7
in read 2, now is 1691136578.3936222, name: person_P8
in read 2, now is 1691136578.3936503, name: person_P9
in read 2, now is 1691136578.3936775, name: person_P10
in read 2, now is 1691136578.393705, name: person_P11
in read 2, now is 1691136578.3937323, name: person_P12
in read 2, now is 1691136578.3937595, name: person_P13
in read 2, now is 1691136578.3937871, name: person_P14
in read 2, now is 1691136578.3938174, name: person_P15
in read 2, now is 1691136578.3938463, name: person_P16
in read 2, now is 1691136578.3938737, name: person_P17
in read 2, now is 1691136578.393901, name: person_P18
in read 2, now is 1691136578.3939342, name: person_P19
in write 1 , now is 1691136578.4051046
in write 1 , now is 1691136578.4108906
in write 1 , now is 1691136578.4169016
in write 1 , now is 1691136578.4225135
in write 1 , now is 1691136578.4282284
in write 1 , now is 1691136578.4340622
in write 1 , now is 1691136578.4397743
in write 1 , now is 1691136578.4456632
in write 1 , now is 1691136578.451795
in write 1 , now is 1691136578.4575145
in write 1 , now is 1691136578.463979
in write 1 , now is 1691136578.471128
in write 1 , now is 1691136578.4781554
in write 1 , now is 1691136578.4851305
in write 1 , now is 1691136578.4925086
in write 1 , now is 1691136578.4996982
in write 1 , now is 1691136578.5068758
in write 1 , now is 1691136578.5138164
in write 1 , now is 1691136578.520577
6.加鎖
加鎖和數(shù)據(jù)庫(kù)設(shè)置：
- 不管加什么鎖，都不能解決lock的問(wèn)題
- 是否設(shè)置讀寫(xiě)模式都不影響讀寫(xiě)操作
db = SqliteDatabase('my_app.db', pragmas={'journal_mode': 'wal'})def write_sql(index):
users = User.select()
# with db.atomic("IMMEDIATE"):
with db.atomic("EXCLUSIVE"):
print("user")
for user in users:
try:
user.age = random.randint(100, 200)
time.sleep(1)
print(f"in write {index} , now is {time.time()}")
user.save()
except Exception as e:
print(e)
in write 10 , now is 1691142036.4625945
in write 10 , now is 1691142037.464804
in write 10 , now is 1691142038.467277
in write 10 , now is 1691142039.4688525
Exception in thread Thread-2:
Traceback (most recent call last):
File "/home/ljk/.virtualenvs/idt_dev/lib/python3.8/site-packages/peewee.py", line 3246, in execute_sql
cursor.execute(sql, params or ())
sqlite3.OperationalError: database is locked
During handling of the above exception, another exception occurred:
Traceback (most recent call last):
File "/usr/local/lib/python3.8/threading.py", line 932, in _bootstrap_inner
self.run()
File "/usr/local/lib/python3.8/threading.py", line 870, in run
self._target(*self._args, **self._kwargs)
File "main.py", line 11, in write_sql
in write 10 , now is 1691142040.4720113
with db.atomic("EXCLUSIVE"):
File "/home/ljk/.virtualenvs/idt_dev/lib/python3.8/site-packages/peewee.py", line 4363, in __enter__
return self._helper.__enter__()
File "/home/ljk/.virtualenvs/idt_dev/lib/python3.8/site-packages/peewee.py", line 4398, in __enter__
self._begin()
File "/home/ljk/.virtualenvs/idt_dev/lib/python3.8/site-packages/peewee.py", line 4384, in _begin
self.db.begin(*args, **kwargs)
File "/home/ljk/.virtualenvs/idt_dev/lib/python3.8/site-packages/peewee.py", line 3765, in begin
self.execute_sql(statement)
File "/home/ljk/.virtualenvs/idt_dev/lib/python3.8/site-packages/peewee.py", line 3246, in execute_sql
cursor.execute(sql, params or ())
File "/home/ljk/.virtualenvs/idt_dev/lib/python3.8/site-packages/peewee.py", line 3014, in __exit__
reraise(new_type, new_type(exc_value, *exc_args), traceback)
File "/home/ljk/.virtualenvs/idt_dev/lib/python3.8/site-packages/peewee.py", line 192, in reraise
raise value.with_traceback(tb)
File "/home/ljk/.virtualenvs/idt_dev/lib/python3.8/site-packages/peewee.py", line 3246, in execute_sql
cursor.execute(sql, params or ())
peewee.OperationalError: database is locked
in write 10 , now is 1691142041.4745347
in write 10 , now is 1691142042.4767966
in write 10 , now is 1691142043.4779344
in write 10 , now is 1691142044.4796853
in write 10 , now is 1691142045.482223
in write 10 , now is 1691142046.4840803
in write 10 , now is 1691142047.4864902
in write 10 , now is 1691142048.4888134
in write 10 , now is 1691142049.491353
in write 10 , now is 1691142050.4932055
in write 10 , now is 1691142051.4950705
in write 10 , now is 1691142052.496692
in write 10 , now is 1691142053.4988236
in write 10 , now is 1691142054.500759
in write 10 , now is 1691142055.50223647.解決方案
from gpt3.5
SQLite 是一種嵌入式數(shù)據(jù)庫(kù)，它默認(rèn)情況下不支持多個(gè)進(jìn)程同時(shí)寫(xiě)入。然而，有幾種方法可以解決這個(gè)問(wèn)題：
- 串行化訪(fǎng)問(wèn)：通過(guò)確保只有一個(gè)進(jìn)程在任何給定時(shí)間寫(xiě)入數(shù)據(jù)庫(kù)，可以使用互斥鎖或信號(hào)量來(lái)實(shí)現(xiàn)串行化訪(fǎng)問(wèn)。這種方法可以保證數(shù)據(jù)的一致性，但可能會(huì)影響性能。
- 讀寫(xiě)鎖：SQLite 提供了一種讀寫(xiě)鎖機(jī)制，可以允許多個(gè)進(jìn)程同時(shí)讀取數(shù)據(jù)庫(kù)，但只允許一個(gè)進(jìn)程寫(xiě)入。這種方式可以提高并發(fā)性能，但需要在應(yīng)用程序中正確實(shí)現(xiàn)讀寫(xiě)鎖的使用。
- 延遲寫(xiě)：可以通過(guò)將寫(xiě)操作延遲到合適的時(shí)機(jī)來(lái)避免同時(shí)寫(xiě)的問(wèn)題。例如，可以將寫(xiě)操作緩沖到內(nèi)存中，然后在合適的時(shí)機(jī)一起寫(xiě)入數(shù)據(jù)庫(kù)。這種方式可以提高性能，但需要考慮數(shù)據(jù)一致性和恢復(fù)的問(wèn)題。
- 使用獨(dú)立的數(shù)據(jù)庫(kù)服務(wù)器：如果應(yīng)用程序需要支持大規(guī)模并發(fā)寫(xiě)入，可以考慮使用獨(dú)立的數(shù)據(jù)庫(kù)服務(wù)器，如MySQL或PostgreSQL。這樣可以通過(guò)連接池和并發(fā)控制機(jī)制來(lái)實(shí)現(xiàn)并發(fā)寫(xiě)入。
選擇哪種解決方案取決于應(yīng)用程序的具體需求和性能要求。需要權(quán)衡數(shù)據(jù)一致性、并發(fā)性能和開(kāi)發(fā)復(fù)雜性，并根據(jù)實(shí)際情況選擇最適合的方法。
8.串行化訪(fǎng)問(wèn)
使用全局鎖，當(dāng)進(jìn)行寫(xiě)操作之前獲取鎖，寫(xiě)操作完成釋放鎖。沒(méi)有獲取到鎖拋出異常，讓頁(yè)面展示出來(lái)
import time
import random
import threading
from base_model import User, db
Lock = False
def write_sql(index):
time.sleep(random.randint(1, 4))
global Lock
if Lock:
print(f"i am {index}, 數(shù)據(jù)庫(kù)被lock，退出執(zhí)行")
return
else:
print(f"i am {index}, 數(shù)據(jù)庫(kù)可以使用")
Lock = True
user = User.get_by_id(10)
user.age = 200
user.save()
Lock = False
if __name__ == "__main__":
data = []
for i in range(20):
p = threading.Thread(target=write_sql, args=(i, ))
data.append(p)
for i in data:
i.start()
for i in data:
i.join()(dev) ? peewee_sqlite python main.py
i am 6, 數(shù)據(jù)庫(kù)可以使用
i am 4, 數(shù)據(jù)庫(kù)可以使用
i am 8, 數(shù)據(jù)庫(kù)可以使用
i am 19, 數(shù)據(jù)庫(kù)可以使用
i am 16, 數(shù)據(jù)庫(kù)可以使用
i am 1, 數(shù)據(jù)庫(kù)可以使用
i am 0, 數(shù)據(jù)庫(kù)可以使用
i am 10, 數(shù)據(jù)庫(kù)可以使用
i am 2, 數(shù)據(jù)庫(kù)可以使用
i am 11, 數(shù)據(jù)庫(kù)可以使用
i am 7, 數(shù)據(jù)庫(kù)可以使用
i am 9, 數(shù)據(jù)庫(kù)可以使用
i am 3, 數(shù)據(jù)庫(kù)可以使用
i am 17, 數(shù)據(jù)庫(kù)可以使用
i am 12, 數(shù)據(jù)庫(kù)可以使用
i am 14, 數(shù)據(jù)庫(kù)可以使用
i am 5, 數(shù)據(jù)庫(kù)可以使用
i am 15, 數(shù)據(jù)庫(kù)可以使用
i am 13, 數(shù)據(jù)庫(kù)可以使用
i am 18, 數(shù)據(jù)庫(kù)被lock，退出執(zhí)行
9.總結(jié)
sqlite多線(xiàn)程無(wú)法同時(shí)寫(xiě)的特性并沒(méi)有解決，只能通過(guò)業(yè)務(wù)層面規(guī)避這個(gè)問(wèn)題。具體來(lái)說(shuō)就是在需要寫(xiě)入的地方判斷一下是否有其他寫(xiě)入任務(wù)，沒(méi)有則獲取全局寫(xiě)入標(biāo)識(shí)，執(zhí)行寫(xiě)操作；有其他寫(xiě)入任務(wù)則返回特定狀態(tài)碼，告訴用戶(hù)其他業(yè)務(wù)邏輯正在使用數(shù)據(jù)庫(kù)。雖然不優(yōu)雅，but是當(dāng)下最優(yōu)解。
不要問(wèn)為什么不用mysql，上面有人不讓用~
到此這篇關(guān)于python peewee操作sqlite鎖表的問(wèn)題分析的文章就介紹到這了,更多相關(guān)eewee操作sqlite內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
相關(guān)文章
Pytorch 實(shí)現(xiàn)數(shù)據(jù)集自定義讀取
今天小編就為大家分享一篇Pytorch 實(shí)現(xiàn)數(shù)據(jù)集自定義讀取，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。一起跟隨小編過(guò)來(lái)看看吧2020-01-01
這篇文章主要介紹了基于 Python 實(shí)踐感知器分類(lèi)算法的教程，幫助大家更好的利用python進(jìn)行機(jī)器學(xué)習(xí)，感興趣的朋友可以了解下2021-01-01
python寫(xiě)日志文件操作類(lèi)與應(yīng)用示例
這篇文章主要介紹了python寫(xiě)日志文件操作類(lèi)與應(yīng)用,結(jié)合實(shí)例形式分析了Python日志文件操作類(lèi)的定義與使用相關(guān)操作技巧,需要的朋友可以參考下2019-07-07
python檢查目錄文件權(quán)限并修改目錄文件權(quán)限的操作
這篇文章主要介紹了python檢查目錄文件權(quán)限并修改目錄文件權(quán)限的操作，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。一起跟隨小編過(guò)來(lái)看看吧2020-03-03
使用pygame模塊編寫(xiě)貪吃蛇的實(shí)例講解
下面小編就為大家分享一篇使用pygame模塊編寫(xiě)貪吃蛇的實(shí)例講解，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。一起跟隨小編過(guò)來(lái)看看吧2018-02-02
python數(shù)據(jù)分析:關(guān)鍵字提取方式
今天小編就為大家分享一篇python數(shù)據(jù)分析:關(guān)鍵字提取方式，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。一起跟隨小編過(guò)來(lái)看看吧2020-02-02
VSCode配置python.analysis.extraPaths作用詳解
本文主要介紹了VSCode配置python.analysis.extraPaths作用,文中通過(guò)示例代碼介紹的非常詳細(xì),對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值,需要的朋友們下面隨著小編來(lái)一起學(xué)習(xí)學(xué)習(xí)吧2025-12-12
python標(biāo)準(zhǔn)庫(kù)中inspect模塊的簡(jiǎn)單說(shuō)明
這篇文章主要介紹了python標(biāo)準(zhǔn)庫(kù)中inspect模塊的簡(jiǎn)單介紹,inspect模塊提供了幾個(gè)有用的函數(shù)來(lái)幫助獲取有關(guān)活動(dòng)對(duì)象的信息，例如模塊，類(lèi)，方法，函數(shù)，回溯，框架對(duì)象和代碼對(duì)象,需要的朋友可以參考下2023-08-08
Python利用字典樹(shù)實(shí)現(xiàn)獵詞游戲
獵詞（word hunt）是一類(lèi)很常見(jiàn)的游戲，給你一張字母組成的表，然后讓你在這些字母中盡可能多的去尋找單詞。這類(lèi)游戲用字典樹(shù)就能輕松完成，本文就來(lái)具體講講實(shí)現(xiàn)步驟，需要的可以參考一下2022-06-06
利用Python的Twisted框架實(shí)現(xiàn)webshell密碼掃描器的教程
這篇文章主要介紹了利用Python的Twisted框架實(shí)現(xiàn)webshell密碼掃描器的教程,用到了Twisted框架的異步通信機(jī)制,需要的朋友可以參考下2015-04-04