How does SQL Server handle concurrent operations?
-
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?
- junk.mail291276 - Tuesday, February 27, 2018 4:52 PM
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.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) - junk.mail291276 - Tuesday, February 27, 2018 4:52 PM
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.
- junk.mail291276 - Tuesday, February 27, 2018 4:52 PM
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 recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
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".
-
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.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
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? -
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 recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass - junk.mail291276 - Wednesday, February 28, 2018 2:19 PM
sounds like whatever code the api is accessing. is this vendor code or locally written company code?
- junk.mail291276 - Wednesday, February 28, 2018 2:19 PM
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).
- GilaMonster - Wednesday, February 28, 2018 3:15 PM
Thanks Gila,
I'll try with sys.dm_exec_requests and see what I get.is this vendor code or locally written company code?
It's locally written.
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).
While it is true that more detailed information is useful for troubleshooting, it is equally true that I need to know what information you need to know. Since I don't know what's causing the problem, I can only guess what information is relevant to the problem (IOW, I have to take shots in the dark). I'm running SQL Server 2014, for example. The API is running on a Windows Server 2016 VM, for example. I had a sausage biscuit sandwich for breakfast this morning, for example.
^ Does any of that help?
- junk.mail291276 - Thursday, March 1, 2018 9:00 AM
Nope, still totally vague (and your breakfast choice though interesting isn't really relevant 😉 ). Ask yourself, what would I need from someone in this situation. The process you go through setting up the scenario for others can actually help you figure out what may be going on in your system. You briefly mention that it looks like blocking between separate databases, how do you come to that conclusion? Have you shared this information here? Have you done anything to capture blocking reports to see what is blocking what? There are quite a few things that you could be doing and providing us to help you trouble shoot the problem. Waiting for us to tell you what we need is counter productive to helping you. You need to be proactive in providing us information. Even if it is incomplete it could help us make more thoughtful and targeted questions seeking additional information.
This is your problem, and we are mostly volunteers willing to help you, but we also have our own jobs to do as well. We don't necessarily have the time to ask a lot of questions to try and figure out what may be the problem. We rely on you to provide us with the information needed. This could be DDL for the table(s) (including defined indexes), sample data representative of the problem domain (in other words, not production data), actual execution plans of the queries, blocking reports or deadlock graphs, maybe even configuration information about the SQL instance and/or the server.
- junk.mail291276 - Thursday, March 1, 2018 9:00 AM
AHAH! Tomorrow have oatmeal and things should start working again 🙂
I would start with the app, and get the exact nature of the failure. It could be something as simple as a timeout and the web code should catch whatever and return some sort of notice rather than throwing everything to the web server and getting that rather general error message.
2 cents!
- I gave you a couple of suggestions.GilaMonster - Wednesday, February 28, 2018 12:03 PMGilaMonster - Wednesday, February 28, 2018 3:15 PM
You need, at minimum, the actual error that the application is encountering, the actual commands being run on the DB and, if it is blocking, on what resource and what type of locks.
But it may well not be blocking, so the actual error must be your starting point. I mean, there's no reason to go looking at exec_requests if the actual error the app is getting is something like 'cannot run command, connection is closed'Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Ok, I think you guys are right about getting the exact error from the API instead of plowing ahead with the assumption that it's a locking issue. I say this not only because you guys are smart but because I've went down the "locking issue" rabbit hole about as far as I could and the results I'm getting are suggesting that maybe it's not a locking issue.
In the meantime though, let me post the results I'm getting (this will hopefully make SSC Guru happy 🙂 ).
First, I'm attaching a spreadsheet that shows the output of: select * from sys.dm_exec_requests
It is showing that the API is being block in the line colored red (the weird thing being it's being blocked by session 70 which isn't in the list at all).
We re-tried doing the test across different database and could not replicate the error... so the cross-database case might have been a red herring.
Secondly, I did some research into table, page, and row level locking. I did this because there is no possible way the rows being affected by the file upload overlap with the rows that the API is trying to read (they are completely different sets of data), which made me think the locking has to be at the table level (the only sense in which this isn't entirely true is that both the file upload process and the API read process might be querying common lookup tables for specific codes, but these would both be read operations; locking wouldn't occur if they're both read operations, would it?)
I read these articles here:
https://www.sqlpassion.at/archive/2016/10/31/disabling-row-and-page-level-locks-in-sql-server/
https://www.sqlshack.com/locking-sql-server/The first article tells me that the row and page level locks are set in the table indexes. For example, here are the settings for ALLOW_PAGE_LOCKS and ALLOW_ROW_LOCKS for the PK_Projects index (clustered) in the Project table:
I then ran the query below to find that all tables (system or otherwise) had both lock settings set to true:
SELECT object_name(object_id), name, index_id, allow_row_locks, allow_page_locks FROM sys.indexes
All tables, that is, except for queue_messages_*** whose queue_secondary_index index had their allow_page_locks set to false.
The second article explains how lock escalation works, and says you can disable lock escalation for tables using the query below (I don't know if "lock escalation" is exactly the same as just "locking", so I'm not sure if setting LOCK_ESCALATION to DISABLED on a table is the same as disabling table locking on that table):
ALTER TABLE Table_name SET (LOCK_ESCALATION = DISABLE)
To see which tables had lock_escalation set to table, I ran the following query and found that they all had lock_escalation set to table:
select name, lock_escalation, lock_escalation_desc from sys.tables
After experimenting with these for a bit, I wrote the following scripts to disable locking at all levels (assuming that's what these do):
-- script for turning off ALLOW_PAGE_LOCKS and ALLOW_ROW_LOCKS on all indexes on all tables
Declare @t table(query varchar(1000))
insert into @t
Select 'Alter index all on [dbo].['+table_name+'] rebuild with (ALLOW_PAGE_LOCKS = OFF, ALLOW_ROW_LOCKS = OFF)' from
information_schema.tables
where table_name<>'dtProperties'Declare @sql varchar(1000)
Set @sql=''While exists
(
Select * from @t where query>@sql
)Begin
Select @sql=min(query) from @t where query>@sql
EXEC(@sql)
End-- script for disabling lock_escalation on all tables
Declare @t table(query varchar(1000))
insert into @t
Select 'Alter table [dbo].['+table_name+'] set (lock_escalation = DISABLE)' from information_schema.tables
where table_name<>'dtProperties'Declare @sql varchar(1000)
Set @sql=''While exists
(
Select * from @t where query>@sql
)Begin
Select @sql=min(query) from @t where query>@sql
EXEC(@sql)
EndI re-ran the test (uploading a file then calling the API) and it's still failing.
This is why I say I'm starting to doubt it has anything to do with locking (even though sys.dm_exec_requests says it's being blocked). I'll look at the exact error message now and get back to you.
Viewing 15 posts - 1 through 15 (of 31 total)
You must be logged in to reply to this topic. Login to reply