Replacing MSSQL Server Stored Procedures to Prevent DB Locks

Question

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?

Are writers blocking other writers, or are writers blocking readers? — MJH, Apr 05 '18 at 15:10
In memory OLTP is designed to solve problems with locking & latching. Depending on what kind of operations you're doing with the data, it might help a lot -- or be totally useless. There's no locking, so you can't update the data on the same row at the same time. — James Z, Apr 05 '18 at 15:19
When you say users, are these any Tom, Dick, and Jane in the business, or are these IT users? Is it possible to bring all of these stored procedure calls into one stored procedure and then let it run? SQL Server will run the SPROCS in sequence. — J Weezy, Apr 05 '18 at 15:20
@JWeezy, They would be separate people yes, Tom Dick and Jane. Are you saying there is some way to queue up stored procedure calls? I'm not sure what you mean by bringing all the stored procedure calls into one? They are separate requests, usually to the same stored procedure which cause blocking — Dportology, Apr 05 '18 at 15:25
@Dportology Why would multiple users be running the same stored procedure that writes data simultaneously? Are they providing any specific data that is unavailable to everyone else? Yes, you can queue up multiple stored procedures inside a stored procedure, a SPROC of SPROCs so to speak. So, you have a SPROC that calls other SPROCS - it quite literally has EXEC in order. In this case, SQL Server will see this and execute each one, individually, in ordered sequence, and it will wait for a SPROC to finish before calling the next. — J Weezy, Apr 05 '18 at 15:29
Yes, the stored procedures are ran if the users enter in some new data / variables from a front end system, and need the data in the tables to be updated with these new values as part of the equation. It seems when two people try to do something like this, one persons request will fail. The ideal solution would probably be to fully execute one request as a transaction, then the next. Sorry I'm somewhat vague on the details of the system, I did not create it — Dportology, Apr 05 '18 at 15:32
@Dportology Can you post the code segment that is causing the deadlock along with the destination table primary key? — J Weezy, Apr 05 '18 at 15:34
@JWeezy it doesn't make sense to duplicate procedures for each user. — S3S, Apr 05 '18 at 15:37
@scsimon I think what Dportology is saying is that business users are using a web application that calls a stored procedure to modify the data. In that sense, the users can unknowingly call the same proc at the same time. — J Weezy, Apr 05 '18 at 15:43
If you need to limit that the procedure can be running only for one person at the time, you could probably use sp_getapplock — James Z, Apr 05 '18 at 15:45
@JWeezy, Yes that's correct, users on a web application unknowingly calling the same proc at the same time. — Dportology, Apr 05 '18 at 15:49
@Dportology There are two solutions to this problem that I can think of. Either prevent users from viewing records that other users are viewing or allow for the use of READ UNCOMITTED. http://satvasolutions.com/deadlock-entity-framework-sql-server/ — J Weezy, Apr 05 '18 at 15:59

score 1 · Answer 1 · answered Apr 06 '18 at 01:51

You can alter the webapp to check if the proc is running already, and either abort the run event (on click), or proactively prevent it by disabling the button all together (timer to re-enable?).

SELECT * 
FROM (SELECT * FROM sys.dm_exec_requests WHERE sql_handle IS NOT NULL) A 
CROSS APPLY sys.dm_exec_sql_text(A.sql_handle) T 
WHERE T.text LIKE 'dbo.naughty_naughty_proc_name%'

Then perhaps alter the proc as a safeguard to prevent multiple instances using sp_getapplock.

I would not blindly change to read uncommitted as your isolation level. In my opinion that is very bad advice when we don't have any context surrounding how important this system/data is and you clearly state the data is "being manipulated". You really need to understand the data, and the system you're impacting before doing this!

Some reading:

https://www.mssqltips.com/sqlservertip/3202/prevent-multiple-users-from-running-the-same-sql-server-stored-procedure-at-the-same-time/

Why use a READ UNCOMMITTED isolation level?

score 1 · Accepted Answer · answered Apr 06 '18 at 02:28

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

2 Answers2