python?peewee操作sqlite鎖表的問題分析
在使用python orm 框架 peewee 操作數(shù)據(jù)庫時(shí)時(shí)常會拋出以一個(gè)異常，具體的報(bào)錯(cuò)就是 database is locked
初步了解是因?yàn)閟qlite鎖的顆粒度比較大，是庫鎖。當(dāng)一個(gè)連接在寫數(shù)據(jù)庫時(shí)，另一個(gè)連接在想要寫任意一張表都會報(bào)錯(cuò)。
為了解決這個(gè)問題，做如下的實(shí)驗(yàn)分析問題
1.理論分析
SQLite 是一個(gè)軟件庫，實(shí)現(xiàn)了自給自足的、無服務(wù)器的、零配置的、事務(wù)性的 SQL 數(shù)據(jù)庫引擎。
SQLite允許多個(gè)進(jìn)程/線程同時(shí)進(jìn)行讀操作，但在同一時(shí)刻只允許一個(gè)線程進(jìn)行寫操作。SQLite在進(jìn)行寫操作時(shí)，數(shù)據(jù)庫文件會被鎖定，此時(shí)任何其他的讀/寫操作都會被阻塞，如果阻塞超過5秒鐘，就會拋出描述為“database is locked”的異常。
出現(xiàn)上述現(xiàn)象的原因是SQLite只支持庫級鎖，不支持并發(fā)執(zhí)行寫操作，即使是不同的表，同一時(shí)刻也只能進(jìn)行一個(gè)寫操作。
例如，事務(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.串行寫操作不會鎖庫
串行執(zhí)行不會鎖表，同時(shí)也說明事務(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, 開始")
time.sleep(10)
print("睡眠10s, 結(jié)束")
user.age = num
user.save()
write_sql(100)
write_sql(300)傳入數(shù)值：100
睡眠10s, 開始
睡眠10s, 結(jié)束
傳入數(shù)值：300
睡眠10s, 開始
睡眠10s, 結(jié)束
4.兩個(gè)線程同時(shí)寫會鎖表
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è)寫不會鎖表
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ù)庫設(shè)置：
- 不管加什么鎖，都不能解決lock的問題
- 是否設(shè)置讀寫模式都不影響讀寫操作
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ù)庫，它默認(rèn)情況下不支持多個(gè)進(jìn)程同時(shí)寫入。然而，有幾種方法可以解決這個(gè)問題：
- 串行化訪問：通過確保只有一個(gè)進(jìn)程在任何給定時(shí)間寫入數(shù)據(jù)庫，可以使用互斥鎖或信號量來實(shí)現(xiàn)串行化訪問。這種方法可以保證數(shù)據(jù)的一致性，但可能會影響性能。
- 讀寫鎖：SQLite 提供了一種讀寫鎖機(jī)制，可以允許多個(gè)進(jìn)程同時(shí)讀取數(shù)據(jù)庫，但只允許一個(gè)進(jìn)程寫入。這種方式可以提高并發(fā)性能，但需要在應(yīng)用程序中正確實(shí)現(xiàn)讀寫鎖的使用。
- 延遲寫：可以通過將寫操作延遲到合適的時(shí)機(jī)來避免同時(shí)寫的問題。例如，可以將寫操作緩沖到內(nèi)存中，然后在合適的時(shí)機(jī)一起寫入數(shù)據(jù)庫。這種方式可以提高性能，但需要考慮數(shù)據(jù)一致性和恢復(fù)的問題。
- 使用獨(dú)立的數(shù)據(jù)庫服務(wù)器：如果應(yīng)用程序需要支持大規(guī)模并發(fā)寫入，可以考慮使用獨(dú)立的數(shù)據(jù)庫服務(wù)器，如MySQL或PostgreSQL。這樣可以通過連接池和并發(fā)控制機(jī)制來實(shí)現(xiàn)并發(fā)寫入。
選擇哪種解決方案取決于應(yīng)用程序的具體需求和性能要求。需要權(quán)衡數(shù)據(jù)一致性、并發(fā)性能和開發(fā)復(fù)雜性，并根據(jù)實(shí)際情況選擇最適合的方法。
8.串行化訪問
使用全局鎖，當(dāng)進(jìn)行寫操作之前獲取鎖，寫操作完成釋放鎖。沒有獲取到鎖拋出異常，讓頁面展示出來
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ù)庫被lock，退出執(zhí)行")
return
else:
print(f"i am {index}, 數(shù)據(jù)庫可以使用")
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ù)庫可以使用
i am 4, 數(shù)據(jù)庫可以使用
i am 8, 數(shù)據(jù)庫可以使用
i am 19, 數(shù)據(jù)庫可以使用
i am 16, 數(shù)據(jù)庫可以使用
i am 1, 數(shù)據(jù)庫可以使用
i am 0, 數(shù)據(jù)庫可以使用
i am 10, 數(shù)據(jù)庫可以使用
i am 2, 數(shù)據(jù)庫可以使用
i am 11, 數(shù)據(jù)庫可以使用
i am 7, 數(shù)據(jù)庫可以使用
i am 9, 數(shù)據(jù)庫可以使用
i am 3, 數(shù)據(jù)庫可以使用
i am 17, 數(shù)據(jù)庫可以使用
i am 12, 數(shù)據(jù)庫可以使用
i am 14, 數(shù)據(jù)庫可以使用
i am 5, 數(shù)據(jù)庫可以使用
i am 15, 數(shù)據(jù)庫可以使用
i am 13, 數(shù)據(jù)庫可以使用
i am 18, 數(shù)據(jù)庫被lock，退出執(zhí)行
9.總結(jié)
sqlite多線程無法同時(shí)寫的特性并沒有解決，只能通過業(yè)務(wù)層面規(guī)避這個(gè)問題。具體來說就是在需要寫入的地方判斷一下是否有其他寫入任務(wù)，沒有則獲取全局寫入標(biāo)識，執(zhí)行寫操作；有其他寫入任務(wù)則返回特定狀態(tài)碼，告訴用戶其他業(yè)務(wù)邏輯正在使用數(shù)據(jù)庫。雖然不優(yōu)雅，but是當(dāng)下最優(yōu)解。
不要問為什么不用mysql，上面有人不讓用~
到此這篇關(guān)于python peewee操作sqlite鎖表的問題分析的文章就介紹到這了,更多相關(guān)eewee操作sqlite內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
相關(guān)文章
使用python實(shí)現(xiàn)學(xué)生信息管理系統(tǒng)
這篇文章主要為大家詳細(xì)介紹了使用python實(shí)現(xiàn)學(xué)生信息管理系統(tǒng)，文中示例代碼介紹的非常詳細(xì)，具有一定的參考價(jià)值，感興趣的小伙伴們可以參考一下2021-02-02
Python內(nèi)置模塊ConfigParser實(shí)現(xiàn)配置讀寫功能的方法
這篇文章主要介紹了Python內(nèi)置模塊ConfigParser實(shí)現(xiàn)配置讀寫功能的方法,涉及Python使用ConfigParser模塊進(jìn)行配置讀、寫、修改、刪除等操作的相關(guān)實(shí)現(xiàn)技巧,需要的朋友可以參考下2018-02-02
Python使用flask作為web服務(wù)器的代碼實(shí)現(xiàn)
Python Flask 框架是一個(gè)輕量級的 Web 框架,它簡單易用,靈活多變,非常適合用于構(gòu)建小型到中型規(guī)模的 Web 應(yīng)用程序,本文給大家介紹了Python使用flask作為web服務(wù)器的代碼實(shí)現(xiàn),需要的朋友可以參考下2024-06-06
利用Python統(tǒng)計(jì)Jira數(shù)據(jù)并可視化
目前公司使用 Jira 作為項(xiàng)目管理工具，在每一次迭代完成后的復(fù)盤會上，我們都需要針對本次迭代的 Bug 進(jìn)行數(shù)據(jù)統(tǒng)計(jì)，以幫助管理層能更直觀的了解研發(fā)的代碼質(zhì)量。本篇文章將介紹如何利用統(tǒng)計(jì) Jira 數(shù)據(jù)，并進(jìn)行可視化，需要的可以參考一下2022-07-07
這篇文章主要介紹了Python2.7編程中SQLite3基本操作方法,涉及Python2.7操作sqlite3數(shù)據(jù)庫的增刪改查及防注入等相關(guān)技巧,需要的朋友可以參考下2017-08-08
利用Python開發(fā)Markdown表格結(jié)構(gòu)轉(zhuǎn)換為Excel工具
在數(shù)據(jù)管理和文檔編寫過程中,我們經(jīng)常使用 Markdown 來記錄表格數(shù)據(jù),但它沒有Excel使用方便,所以本文將使用Python編寫一個(gè)轉(zhuǎn)換工具,希望對大家有所幫助2025-03-03
利用jupyter網(wǎng)頁版本進(jìn)行python函數(shù)查詢方式
這篇文章主要介紹了利用jupyter網(wǎng)頁版本進(jìn)行python函數(shù)查詢方式，具有很好的參考價(jià)值，希望對大家有所幫助。一起跟隨小編過來看看吧2020-04-04
這篇文章主要介紹了python之OpenCV的作用以及安裝案例教程,本篇文章通過簡要的案例,講解了該項(xiàng)技術(shù)的了解與使用,以下就是詳細(xì)內(nèi)容,需要的朋友可以參考下2021-07-07
基于Python實(shí)現(xiàn)的微信好友數(shù)據(jù)分析
這篇文章主要介紹了基于Python實(shí)現(xiàn)的微信好友數(shù)據(jù)分析的相關(guān)知識，非常不錯(cuò)，具有參考借鑒價(jià)值,需要的朋友可以參考下2018-02-02
Django中反向生成models.py的實(shí)例講解
今天小編就為大家分享一篇Django中反向生成models.py的實(shí)例講解，具有很好的參考價(jià)值，希望對大家有所幫助。一起跟隨小編過來看看吧2018-05-05