python?peewee操作sqlite鎖表的問題分析
在使用python orm 框架 peewee 操作數據庫時時常會拋出以一個異常，具體的報錯就是 database is locked
初步了解是因為sqlite鎖的顆粒度比較大，是庫鎖。當一個連接在寫數據庫時，另一個連接在想要寫任意一張表都會報錯。
為了解決這個問題，做如下的實驗分析問題
1.理論分析
SQLite 是一個軟件庫，實現了自給自足的、無服務器的、零配置的、事務性的 SQL 數據庫引擎。
SQLite允許多個進程/線程同時進行讀操作，但在同一時刻只允許一個線程進行寫操作。SQLite在進行寫操作時，數據庫文件會被鎖定，此時任何其他的讀/寫操作都會被阻塞，如果阻塞超過5秒鐘，就會拋出描述為“database is locked”的異常。
出現上述現象的原因是SQLite只支持庫級鎖，不支持并發(fā)執(zhí)行寫操作，即使是不同的表，同一時刻也只能進行一個寫操作。
例如，事務T1在表A新插入一條數據，事務T2在表B中更新一條已存在的數據，這兩個操作是不能同時進行的，只能順序進行。
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.串行寫操作不會鎖庫
串行執(zhí)行不會鎖表，同時也說明事務完成之后鎖立即釋放
import time
import threading
from peewee_demo import User
def write_sql(num):
user = User.get_by_id(1)
print(f"傳入數值：{num}")
print("睡眠10s, 開始")
time.sleep(10)
print("睡眠10s, 結束")
user.age = num
user.save()
write_sql(100)
write_sql(300)傳入數值：100
睡眠10s, 開始
睡眠10s, 結束
傳入數值：300
睡眠10s, 開始
睡眠10s, 結束
4.兩個線程同時寫會鎖表
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.同時一個讀+一個寫不會鎖表
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.加鎖
加鎖和數據庫設置：
- 不管加什么鎖，都不能解決lock的問題
- 是否設置讀寫模式都不影響讀寫操作
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 是一種嵌入式數據庫，它默認情況下不支持多個進程同時寫入。然而，有幾種方法可以解決這個問題：
- 串行化訪問：通過確保只有一個進程在任何給定時間寫入數據庫，可以使用互斥鎖或信號量來實現串行化訪問。這種方法可以保證數據的一致性，但可能會影響性能。
- 讀寫鎖：SQLite 提供了一種讀寫鎖機制，可以允許多個進程同時讀取數據庫，但只允許一個進程寫入。這種方式可以提高并發(fā)性能，但需要在應用程序中正確實現讀寫鎖的使用。
- 延遲寫：可以通過將寫操作延遲到合適的時機來避免同時寫的問題。例如，可以將寫操作緩沖到內存中，然后在合適的時機一起寫入數據庫。這種方式可以提高性能，但需要考慮數據一致性和恢復的問題。
- 使用獨立的數據庫服務器：如果應用程序需要支持大規(guī)模并發(fā)寫入，可以考慮使用獨立的數據庫服務器，如MySQL或PostgreSQL。這樣可以通過連接池和并發(fā)控制機制來實現并發(fā)寫入。
選擇哪種解決方案取決于應用程序的具體需求和性能要求。需要權衡數據一致性、并發(fā)性能和開發(fā)復雜性，并根據實際情況選擇最適合的方法。
8.串行化訪問
使用全局鎖，當進行寫操作之前獲取鎖，寫操作完成釋放鎖。沒有獲取到鎖拋出異常，讓頁面展示出來
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}, 數據庫被lock，退出執(zhí)行")
return
else:
print(f"i am {index}, 數據庫可以使用")
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, 數據庫可以使用
i am 4, 數據庫可以使用
i am 8, 數據庫可以使用
i am 19, 數據庫可以使用
i am 16, 數據庫可以使用
i am 1, 數據庫可以使用
i am 0, 數據庫可以使用
i am 10, 數據庫可以使用
i am 2, 數據庫可以使用
i am 11, 數據庫可以使用
i am 7, 數據庫可以使用
i am 9, 數據庫可以使用
i am 3, 數據庫可以使用
i am 17, 數據庫可以使用
i am 12, 數據庫可以使用
i am 14, 數據庫可以使用
i am 5, 數據庫可以使用
i am 15, 數據庫可以使用
i am 13, 數據庫可以使用
i am 18, 數據庫被lock，退出執(zhí)行
9.總結
sqlite多線程無法同時寫的特性并沒有解決，只能通過業(yè)務層面規(guī)避這個問題。具體來說就是在需要寫入的地方判斷一下是否有其他寫入任務，沒有則獲取全局寫入標識，執(zhí)行寫操作；有其他寫入任務則返回特定狀態(tài)碼，告訴用戶其他業(yè)務邏輯正在使用數據庫。雖然不優(yōu)雅，but是當下最優(yōu)解。
不要問為什么不用mysql，上面有人不讓用~
到此這篇關于python peewee操作sqlite鎖表的問題分析的文章就介紹到這了,更多相關eewee操作sqlite內容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家！
相關文章
今天小編就為大家分享一篇Pytorch 實現數據集自定義讀取，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧2020-01-01
這篇文章主要介紹了基于 Python 實踐感知器分類算法的教程，幫助大家更好的利用python進行機器學習，感興趣的朋友可以了解下2021-01-01
這篇文章主要介紹了python寫日志文件操作類與應用,結合實例形式分析了Python日志文件操作類的定義與使用相關操作技巧,需要的朋友可以參考下2019-07-07
這篇文章主要介紹了python檢查目錄文件權限并修改目錄文件權限的操作，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧2020-03-03
下面小編就為大家分享一篇使用pygame模塊編寫貪吃蛇的實例講解，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧2018-02-02
今天小編就為大家分享一篇python數據分析:關鍵字提取方式，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧2020-02-02
VSCode配置python.analysis.extraPaths作用詳解
本文主要介紹了VSCode配置python.analysis.extraPaths作用,文中通過示例代碼介紹的非常詳細,對大家的學習或者工作具有一定的參考學習價值,需要的朋友們下面隨著小編來一起學習學習吧2025-12-12
這篇文章主要介紹了python標準庫中inspect模塊的簡單介紹,inspect模塊提供了幾個有用的函數來幫助獲取有關活動對象的信息，例如模塊，類，方法，函數，回溯，框架對象和代碼對象,需要的朋友可以參考下2023-08-08
獵詞（word hunt）是一類很常見的游戲，給你一張字母組成的表，然后讓你在這些字母中盡可能多的去尋找單詞。這類游戲用字典樹就能輕松完成，本文就來具體講講實現步驟，需要的可以參考一下2022-06-06
利用Python的Twisted框架實現webshell密碼掃描器的教程
這篇文章主要介紹了利用Python的Twisted框架實現webshell密碼掃描器的教程,用到了Twisted框架的異步通信機制,需要的朋友可以參考下2015-04-04