SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How does SQL Server handle concurrent operations?


How does SQL Server handle concurrent operations?

Author
Message
junk.mail291276
junk.mail291276
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2055 Visits: 120
Hello,

We are having an issue which I suspect is database related. We have a web application on which we can upload files. When we upload a file, data from that file gets saved to a SQL database. We also have a web API that reads from that database. Recently, we discovered that when I upload a file, during the time that file is uploading, the API responds with a general error (internal server error 500).

We are wondering if maybe our database is configured to handle only one operation at a time; that is, maybe while it is save data from the file, it is too tied up to service any requests for data from the API.

Is there any way to check for this in the database settings/configurations?
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (817K reputation)SSC Guru (817K reputation)SSC Guru (817K reputation)SSC Guru (817K reputation)SSC Guru (817K reputation)SSC Guru (817K reputation)SSC Guru (817K reputation)SSC Guru (817K reputation)

Group: General Forum Members
Points: 817889 Visits: 46322
junk.mail291276 - Tuesday, February 27, 2018 4:52 PM
Hello,

We are having an issue which I suspect is database related. We have a web application on which we can upload files. When we upload a file, data from that file gets saved to a SQL database. We also have a web API that reads from that database. Recently, we discovered that when I upload a file, during the time that file is uploading, the API responds with a general error (internal server error 500).

We are wondering if maybe our database is configured to handle only one operation at a time; that is, maybe while it is save data from the file, it is too tied up to service any requests for data from the API.

Is there any way to check for this in the database settings/configurations?


What method(s) are you using to upload the file and what kind of file is it? Also, how long is it taking to upload the file and, if it's row based, how many rows and what is the average length of each row?

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
patrickmcginnis59 10839
patrickmcginnis59 10839
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17999 Visits: 7674
junk.mail291276 - Tuesday, February 27, 2018 4:52 PM
Hello,

We are having an issue which I suspect is database related. We have a web application on which we can upload files. When we upload a file, data from that file gets saved to a SQL database. We also have a web API that reads from that database. Recently, we discovered that when I upload a file, during the time that file is uploading, the API responds with a general error (internal server error 500).

We are wondering if maybe our database is configured to handle only one operation at a time; that is, maybe while it is save data from the file, it is too tied up to service any requests for data from the API.

Is there any way to check for this in the database settings/configurations?

Database servers are typically only set to single user during times when you are trying to fix a serious problem. Normally (and especially when accepting connections from a web app), database servers can handle multiple connections. I'm betting that your app is broken.


to properly post on a forum:http://www.sqlservercentral.com/articles/61537/
GilaMonster
GilaMonster
SSC Guru
SSC Guru (860K reputation)SSC Guru (860K reputation)SSC Guru (860K reputation)SSC Guru (860K reputation)SSC Guru (860K reputation)SSC Guru (860K reputation)SSC Guru (860K reputation)SSC Guru (860K reputation)

Group: General Forum Members
Points: 860250 Visits: 48576
junk.mail291276 - Tuesday, February 27, 2018 4:52 PM
We are wondering if maybe our database is configured to handle only one operation at a time

Extremely unlikely, since SQL Server is a multi-user system capable of hundreds or thousands of concurrent connections and operations.

500 is a generic error, usually displayed to users so that they can't get internal information from error messages. Find the actual error that is being thrown, that'll give you a starting point


Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Scott Coleman
Scott Coleman
SSC-Insane
SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)

Group: General Forum Members
Points: 22482 Visits: 1963
SQL Server handles concurrent operations in whatever manner the developers choose to tell it. It can enforce very strict table locking during some operations, which is good for speeding up one transaction at the expense of allowing no other concurrent transactions. This is common for large bulk inserts, but is not mandatory. Inserting one row at a time allows a lot of concurrent operations, but the insert might take forever.

There are more imaginative ways to load tables and allow concurrency. You can enable snapshot isolation, allowing concurrent sessions to read from a snapshot of the table created when the load transaction began. There are partitioning tricks that can be used to create another table with the same structure, load it, and then swap it into the original table as a partition metadata operation. Or you might have two identical tables and a synonym that points to one of them. While the read operations read from the synonym you can import data into the other table, when the load is finished just redefine the synonym to point to the table with the new data.

Wrapping the read API calls in error recovery code that waits a bit and tries again might be helpful. A page that displays "Data refresh in progress, try again in a few minutes" would be better than one that says "Error 500 you're screwed".



Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (817K reputation)SSC Guru (817K reputation)SSC Guru (817K reputation)SSC Guru (817K reputation)SSC Guru (817K reputation)SSC Guru (817K reputation)SSC Guru (817K reputation)SSC Guru (817K reputation)

Group: General Forum Members
Points: 817889 Visits: 46322
It's a 9 year old document but still VERY appropriate for high performance data loads. Please see the following article.

https://technet.microsoft.com/en-us/library/dd425070(v=sql.100).aspx

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
junk.mail291276
junk.mail291276
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2055 Visits: 120
Thanks everyone for your feedback. I will try the things you suggested.

In the meantime, I used sp_who2 active to figure out that the database session for saving the data from the uploaded file is blocking the API from accessing the database.
This article here explains what's happening:
https://blog.wsol.com/sql-server-locks-blocked-processes-and-two-easy-ways-to-find-them
It says:
An example of this would be if request A is attempting to insert a row in table T. Subsequently, before request A completes, request B submits an update on table T. If the row affected by request A is also affected by request B, then a block is encountered, because the row affected by request A has an exclusive lock issued against it for the duration of the transaction.

The funny thing is, this describes blocking as happening on a per row basis. We are experiencing blockage happening across different databases. For example, when we upload a file to our production database, the API cannot access the test database. Both are managed by the same SQL server, which tells me that blocking is happening at the level of the server itself, not specific rows.
Based on this, is there anything else anyone can suggest?
GilaMonster
GilaMonster
SSC Guru
SSC Guru (860K reputation)SSC Guru (860K reputation)SSC Guru (860K reputation)SSC Guru (860K reputation)SSC Guru (860K reputation)SSC Guru (860K reputation)SSC Guru (860K reputation)SSC Guru (860K reputation)

Group: General Forum Members
Points: 860250 Visits: 48576
Locking happens on a row, page or table basis, always within a single database. Blocking does not happen at the server or database level (unless you're doing server reconfigurations or database alterations). If you can't access test while making changes in prod, then either you're doing cross-database calls or there's something really weird going on in your code.

I suggest you narrow down exactly what's happening (and don't use sp_who2, it's about 20 years old and doesn't show all the useful information. The DMV you want for blocking is sys.dm_exec_requests). Once you know what's happening, fixes are easy.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


patrickmcginnis59 10839
patrickmcginnis59 10839
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17999 Visits: 7674
junk.mail291276 - Wednesday, February 28, 2018 2:19 PM
Thanks everyone for your feedback. I will try the things you suggested.

In the meantime, I used sp_who2 active to figure out that the database session for saving the data from the uploaded file is blocking the API from accessing the database.
This article here explains what's happening:
https://blog.wsol.com/sql-server-locks-blocked-processes-and-two-easy-ways-to-find-them
It says:
An example of this would be if request A is attempting to insert a row in table T. Subsequently, before request A completes, request B submits an update on table T. If the row affected by request A is also affected by request B, then a block is encountered, because the row affected by request A has an exclusive lock issued against it for the duration of the transaction.

The funny thing is, this describes blocking as happening on a per row basis. We are experiencing blockage happening across different databases. For example, when we upload a file to our production database, the API cannot access the test database. Both are managed by the same SQL server, which tells me that blocking is happening at the level of the server itself, not specific rows.
Based on this, is there anything else anyone can suggest?


sounds like whatever code the api is accessing. is this vendor code or locally written company code?

to properly post on a forum:http://www.sqlservercentral.com/articles/61537/
Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (366K reputation)SSC Guru (366K reputation)SSC Guru (366K reputation)SSC Guru (366K reputation)SSC Guru (366K reputation)SSC Guru (366K reputation)SSC Guru (366K reputation)SSC Guru (366K reputation)

Group: General Forum Members
Points: 366604 Visits: 41956
junk.mail291276 - Wednesday, February 28, 2018 2:19 PM
Thanks everyone for your feedback. I will try the things you suggested.

In the meantime, I used sp_who2 active to figure out that the database session for saving the data from the uploaded file is blocking the API from accessing the database.
This article here explains what's happening:
https://blog.wsol.com/sql-server-locks-blocked-processes-and-two-easy-ways-to-find-them
It says:
An example of this would be if request A is attempting to insert a row in table T. Subsequently, before request A completes, request B submits an update on table T. If the row affected by request A is also affected by request B, then a block is encountered, because the row affected by request A has an exclusive lock issued against it for the duration of the transaction.

The funny thing is, this describes blocking as happening on a per row basis. We are experiencing blockage happening across different databases. For example, when we upload a file to our production database, the API cannot access the test database. Both are managed by the same SQL server, which tells me that blocking is happening at the level of the server itself, not specific rows.
Based on this, is there anything else anyone can suggest?


Actually, you really haven't provided enough information for us to really help. Any answers we give are going to be shots in the dark since you have provided no real specifics. Understand, we can't see what you see, and you haven't provided enough details. Ask yourself, with what is just in your posts, could you answer your own question(s).

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search