Sql server and web API "lock request time out period exceeded" on stored procedure

Question

1.00/5 (1 vote)

See more:

Facing an issue in stored procedure when executed by multiple users at same time trying to insert thousand of row data in table, SQL cant handle multiple insertion at same time,is there any way to fix this issue.

What I have tried:

i am using SET "LOCK_TIMEOUT 15000" in procedure.

Posted 14-Aug-19 2:44am

Sivachandran R

Updated 16-Aug-19 4:30am

v3

Add a Solution

Comments

MadMyche 14-Aug-19 9:32am

Really hard to help you fix code we cannot see; without at least an overview of the table indexes and the procedure optimization, how are we supposed to have an answer?

Sivachandran R 14-Aug-19 11:25am

Actually syncing data from mobile to sql and sql to Mobile through web api . Table format is having clustered and non clustered index. At same time multiple devices send and receive huge amount of data from sql server. Procedure has normal insert and update into tables. I will post the sp when I reach my work place. Thanks for your reply

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Gerry Schmitz · Accepted Answer · 2019-08-14T12:24:00

Solution 1

Upload the transactions from each device as a batch to a flat file; then start a processor for each batch instead of "force feeding".

Posted 14-Aug-19 12:24pm

Gerry Schmitz

Comments

Sivachandran R 15-Aug-19 12:54pm

Thanks for your reply. Can you please give me more details regarding “batch as flat file”,since I am very new to android device application development, even if any article.

[no name] 16-Aug-19 11:20am

How many devices? Spawn multiple servers and sync for a 25 cent solution.

Sivachandran R 16-Aug-19 13:02pm

Around 10 devices each devices post around 2500 records to same table at same time. All the data in Json object post through web api.so which can be the better option for answer 2.

Vivek_Dutta · Accepted Answer · 2019-08-16T04:31:00

Solution 2

To my understanding of the issue - assuming the stored proc is doing a lot of business logic, you can do;
1) Dump the data from each user into a Db table (Not files - very hard to manage). Use a Windows service or a scheduled task to read the data from this table and execute the storeprocedure.
2) Dump the data from each user into a Db table (Not files - very hard to manage). Use a SQL job to read the data from this table and execute the storeprocedure.
3) Make your API call single threaded so it will make any subsequent call to wait using object locks. This needs a lot of managed code to achieve based on how many users are posting data. If the Stored proc is doing straight insert this is a good option.

Posted 16-Aug-19 4:31am

Vivek_Dutta

Comments

[no name] 16-Aug-19 11:13am

The "transactions" arrive as "flat records".

Whatever your feelings are about "flat files", the UPLOAD starts with a "message" FILE (XML; JSON; BINARY) that is FLAT. The SERVER updates the DB with FLAT records from the device. Whether the FLAT records get STAGED in a DB file is an INTERMEDIATE step between FLAT and DB.

Do you understand that he says the "DATABASE server is the bottle neck"? There are "file" (upload) servers (i.e. FTP) and DATABASE servers; and there is a DIFFERENCE.

Vivek_Dutta 23-Aug-19 8:37am

Thank you for emphasizing the key points. I surely have missed that. The files could be staged on the server (API can just dump the flat files to a server location) and a windows service could read each file and transact the data into the DB. Making sure no other process inserts records into these tables.