首页 > 数据库 >peewee 操作 sqlite 锁表问题分析

peewee 操作 sqlite 锁表问题分析

时间：2023-08-18 16:56:48浏览次数：42  
标签：sqlite py name peewee sql write 锁表 now

image

在使用python orm 框架 peewee 操作数据库时时常会抛出以一个异常，具体的报错就是 database is locked

初步了解是因为sqlite锁的颗粒度比较大，是库锁。当一个连接在写数据库时，另一个连接在想要写任意一张表都会报错。
为了解决这个问题，做如下的实验分析问题

理论分析

SQLite 是一个软件库，实现了自给自足的、无服务器的、零配置的、事务性的 SQL 数据库引擎。

SQLite允许多个进程/线程同时进行读操作，但在同一时刻只允许一个线程进行写操作。SQLite在进行写操作时，数据库文件会被锁定，此时任何其他的读/写操作都会被阻塞，如果阻塞超过5秒钟，就会抛出描述为“database is locked”的异常。

出现上述现象的原因是SQLite只支持库级锁，不支持并发执行写操作，即使是不同的表，同一时刻也只能进行一个写操作。
例如，事务T1在表A新插入一条数据，事务T2在表B中更新一条已存在的数据，这两个操作是不能同时进行的，只能顺序进行。

建表

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)

串行写操作不会锁库

串行执行不会锁表，同时也说明事务完成之后锁立即释放

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, 结束

两个线程同时写会锁表

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

同时一个读+一个写不会锁表

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

加锁

加锁和数据库设置：

  • 不管加什么锁，都不能解决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.5022364

解决方案

from gpt3.5
SQLite 是一种嵌入式数据库，它默认情况下不支持多个进程同时写入。然而，有几种方法可以解决这个问题：

  1. 串行化访问：通过确保只有一个进程在任何给定时间写入数据库，可以使用互斥锁或信号量来实现串行化访问。这种方法可以保证数据的一致性，但可能会影响性能。
  2. 读写锁：SQLite 提供了一种读写锁机制，可以允许多个进程同时读取数据库，但只允许一个进程写入。这种方式可以提高并发性能，但需要在应用程序中正确实现读写锁的使用。
  3. 延迟写：可以通过将写操作延迟到合适的时机来避免同时写的问题。例如，可以将写操作缓冲到内存中，然后在合适的时机一起写入数据库。这种方式可以提高性能，但需要考虑数据一致性和恢复的问题。
  4. 使用独立的数据库服务器：如果应用程序需要支持大规模并发写入，可以考虑使用独立的数据库服务器，如MySQL或PostgreSQL。这样可以通过连接池和并发控制机制来实现并发写入。
    选择哪种解决方案取决于应用程序的具体需求和性能要求。需要权衡数据一致性、并发性能和开发复杂性，并根据实际情况选择最适合的方法。

串行化访问

使用全局锁，当进行写操作之前获取锁，写操作完成释放锁。没有获取到锁抛出异常，让页面展示出来

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，退出执行")
        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，退出执行

总结

sqlite多线程无法同时写的特性并没有解决，只能通过业务层面规避这个问题。具体来说就是在需要写入的地方判断一下是否有其他写入任务，没有则获取全局写入标识，执行写操作；有其他写入任务则返回特定状态码，告诉用户其他业务逻辑正在使用数据库。虽然不优雅，but是当下最优解。
不要问为什么不用mysql，上面有人不让用~

标签：sqlite,py,name,peewee,sql,write,锁表,now
From： https://www.cnblogs.com/goldsunshine/p/17640945.html

相关文章

  • unable to open database file ， EF生成SQLite数据库
    前言：  记录下，忘了再回头看看。unabletoopendatabasefile 错误  如果使用EF生成SQLite数据库，发现报出错误unabletoopendatabasefile，请检查下“存放SQLite数据文件的文件夹是否存在”......
  • Sqlite3的入门操作
    Sqlite3的下载Sqlite3整活有点东西，直接看图吧。操作系统：windows10如果你是第一次用sqlite3，直接会给你干自闭。一般情况下你只会下载序号2的zip文件，然后写代码的时候，会发现头文件呢？没错，你又要回来下载序号1的zip文件。找了一份example代码，编译的时候有报错，链接失败。你......
  • select......for update会锁表还是锁行
    select查询语句是不会加锁的，但是select.......forupdate除了有查询的作用外，还会加锁呢，而且它是悲观锁。那么它加的是行锁还是表锁，这就要看是不是用了索引/主键。没用索引/主键的话就是表锁，否则就是是行锁。验证：建表sql//id为主键  //name 为唯一索引CREATE TABLE user ......
  • SQLite数据库实现数据增删改查
    当前文章介绍的设计的主要功能是利用SQLite数据库实现宠物投喂器上传数据的存储，并且支持数据的增删改查操作。其中，宠物投喂器上传的数据包括投喂间隔时间、水温、剩余重量等参数。实现功能：创建SQLite数据库表，用于存储宠物投喂器上传的数据。实现对数据库表中数据的插入操作，即将......
  • SQLite与Mysql的语句区别
     SQLite中，TEXT是一个通用的字符数据类型，能够存储任何长度的字符串，类似于MySQL的VARCHAR(255)。INTEGER是一个通用的整数数据类型。 此外，SQLite默认情况下是区分大小写的，而MySQL在utf8_general_ci下不区分大小写。如果需要在SQLite中进行不区分大小写的搜索，可以在LIKE操作符中使......
  • python操作SQLite数据库
    1、脚本#!/usr/local/python3.8/bin/python3#-*-coding:UTF-8-*-importsqlite3importredefdict_factory(cursor,row):#将游标获取的数据处理成字典返回#cursor.description:获取表头d={}foridx,colinenumerate(cursor.description):......
  • select......for update会锁表还是锁行
    select查询语句是不会加锁的，但是select.......forupdate除了有查询的作用外，还会加锁呢，而且它是悲观锁。 需要关闭自动提交，通过set@@autocommit=0;设置为手动提交。0代表手动提交，1代表自动提交。结果：如果查询条件用了索引/主键，那么select.....forupdate就会进行行锁。......
  • sqlite的安装和使用
    1.安装到[官网]:https://www.sqlite.org/download.html去下载对应系统版本，这里以win为例需要下载两个文件下载解压后放入同一个文件夹中将路径加入到系统变量Path中打开命令行，验证是否安装成功：输入sqlite3即成功2.使用SQLite是一个自包含的、零配置的SQL数据库引......
  • SQLServer锁表
    场景：同步数据到表的时候卡住了，导致表被锁住了解决方法：//先查找相关的IDSELECTrequest_session_idasspid,OBJECT_NAME(resource_associated_entity_id)astableNameFROMsys.dm_tran_locksWHEREresource_type='OBJECT'//然后结束对应的进程declare@spidintSet@s......
  • sqlite3 db "delete from apps where title='Typora';"&&killall Dock
    command+shift+G进入访达前往->输入/private/var/folders 搜索：com.apple.dock.launchpad  仔细看了下执行的命令就发现了sqlite3db这个东西，可以深入了解下  ......

赞助商

阅读排行

网站主页关于我们联系我们网站地图

本网站内容转载自其他媒体，侵权联系[admin##ips99.com]。

Copyright © 2020-2023 IPS99 版权所有 IPS99