sqlite multiple writes at same time

Question

i'm making a python flask app with sqlite database is there a way to make a queue for write requests so that it can run smoothly AS SQLITE doesn't support multiple concurrent writes or commits

this is my connection string

engine = create_engine('sqlite:///IT_DataBase.db',
                       connect_args={'check_same_thread': False})
Base.metadata.bind = engine
DBSession = sessionmaker(bind=engine)
session = DBSession()

and this is the commit code as example:

@app.route('/NewRequest', methods=['GET', 'POST'])
@login_required
def NewRequest():
    connUser=session.query(User).filter(User.id==Session.get('user_id')).one()
    if request.method == 'GET':
        Types = session.query(Req_Type.id,Req_Type.Type_name)
        Pr = session.query(Req_Priorities.id,Req_Priorities.Priority_name)
        return render_template('NewRequest.html',conn=connUser ,name=current_user.name, items=Types,priorities=Pr)
    else:
        name= request.form['Name']
        Description= request.form['Description']
        Type = request.form.get('Type')
        Priority = request.form.get('Priority')
        newRequest = Requests(name=name, Record_Created=datetime.now().strftime("%Y-%m-%d %H:%M"), Description=Description, Assigned_To=None, Type_Name=str(Type), Priority_Name=str(Priority), Status_Name='Opened', User_ID=Session.get('user_id') )
        session.add(newRequest)
        flash('New Request With Name %s Successfully Created' % newRequest.name)
        session.commit()
        UserRequests= session.query(Requests).filter_by(User_ID=Session.get('user_id')).filter(Requests.Status_Name!='Solved').all()
        return render_template('ReqData.html',conn=connUser , title='User Requests', rows=UserRequests)

i think that if we didn't change the database engine the solution is either to queue the commits but i don't know how or to make flask wait random time before commiting but i think this will make performance poor what should i do

Answer 1

You need to serialize the commits. Create a lock like below.

from threading import RLock

sql_lock = RLock()

Wrap session.add and session.commit like the following. lock.acquire() will block the code while another thread has acquired the lock, and is yet to release the lock. This ensures that only one thread (or none) is running between acquire() and release() at all times.

    try:
        sql_lock.acquire()
        session.add(newRequest)
        session.commit()
    finally:
        sql_lock.release()