We're experiencing some slowdown, and frustrating database lockups with our current solution, which essentially consists of calling stored procedures on an MSSQL server to manipulate data. If two or more users try to hit the same table simultaneously, one is locked out and their request fails.
The proposed solution to this problem was to bring the data into python using sqlalchemy, and perform any manipulations / calculations on it in dataframes. This worked but was incredibly slow because of the network calls to the DB.
Is there a better solution which can support multiple concurrent users, without causing too much of a slowdown?
You can use nolock keyword in stored procedure to remove this problem
in your stored procedure where you specify table name in front of that write nolock keyword i hope it will be work for you
eg.
select * from tablename1 t1
join nolock tablename2 t2 on t2.id=t1.id
Replacing MSSQL Server Stored Procedures to Prevent DB Locks