March 1, 2018 at 2:05 pm
Don't fiddle with your lock escalation or page/row lock settings unless you know EXACTLY what you're doing and why. You can cause some serious problems messing with those.
If you disable both page and row locks, you're forcing SQL to lock only at the table level, which means that anything that touches the table will block EVERYONE else who wants to do anything to any row in that table. Disabling lock escalation can, in some circumstances cause a server crash (out of memory)
Ok, blocked by a session not running is possible, if something opens a transaction and doesn't commit it, it will hold locks until the transaction commits or rolls back or the session is killed. You can check sys.dm_exec_sessions for session_id 70 to see who it's logged in as and where it's coming from.
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
March 1, 2018 at 4:01 pm
Thanks for the replies Gila,
I know I'm playing with fire with the page/row locks and the lock_escalation, but I made a backup of my database before doing anything and restored it afterwards. Also, it's just our test database, so if anything bad happens to it, it's not the end of the world.
I narrowed down the exact spot where the error gets thrown and the exact message. It's when the API calls a stored procedure. The API times out waiting for the stored procedure to return. It throws an exception with this message:
"Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding."
Indeed, the stored procedure reads from a table which is being written to during the file upload, though it's still the case that there should be no overlap between the records the stored procedure reads and those that the file upload process inserts or updates.
If I run the stored procedure directly during the file upload (as opposed to calling the API), it just sits there spinning until the file upload is done, and then returns the results. No error, just waits.
Based on this, I'm not ready to rule out locking at some level.
When you say "If you disable both page and row locks, you're forcing SQL to lock only at the table level," are saying that disabling both page and row locking forces table locking to be enabled (even if it was disabled before)? If so, then maybe my tests above weren't good. Not that I really understand what they're doing nearly well enough, but this would means that if I run my script for disabling lock_escalation (which I think disabled locking at the table level... but I could be wrong), and then run my script for disallowing row and page locking, then that will RENABLE table locking... right?
March 1, 2018 at 4:23 pm
Oh, and I forgot to mention: sys.dm_exec_sessions confirms that it is our application session that's doing the blocking (where the file is uploaded).
March 1, 2018 at 4:33 pm
junk.mail291276 - Thursday, March 1, 2018 4:01 PMThanks for the replies Gila,
I know I'm playing with fire with the page/row locks and the lock_escalation, but I made a backup of my database before doing anything and restored it afterwards. Also, it's just our test database, so if anything bad happens to it, it's not the end of the world.
I narrowed down the exact spot where the error gets thrown and the exact message. It's when the API calls a stored procedure. The API times out waiting for the stored procedure to return. It throws an exception with this message:"Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding."
Indeed, the stored procedure reads from a table which is being written to during the file upload, though it's still the case that there should be no overlap between the records the stored procedure reads and those that the file upload process inserts or updates.
If I run the stored procedure directly during the file upload (as opposed to calling the API), it just sits there spinning until the file upload is done, and then returns the results. No error, just waits.
Based on this, I'm not ready to rule out locking at some level.
When you say "If you disable both page and row locks, you're forcing SQL to lock only at the table level," are saying that disabling both page and row locking forces table locking to be enabled (even if it was disabled before)? If so, then maybe my tests above weren't good. Not that I really understand what they're doing nearly well enough, but this would means that if I run my script for disabling lock_escalation (which I think disabled locking at the table level... but I could be wrong), and then run my script for disallowing row and page locking, then that will RENABLE table locking... right?
If I read this properly, you seem to think that you are disabling all locking, this is not the case. SQL Server still needs to lock at some level to preserve the ACID principles. Atomic, Consistent, Isolated, and Durable. With no locking all this goes out the window, you have no guarantee regarding the consistency of the database and the data. Disabling lock escalation simply prevents SQL Server from going from Row-level locking or page-level locking to table-level locking. If you disable row-level and page-level locking all SQL Server has left is table-level locking.
March 1, 2018 at 4:35 pm
If the problem seems to be the stored procedure that uploads the file, then you may need to look at reworking that procedure to improve its performance, especially if multiple files could be imported by multiple users at the same time.
March 1, 2018 at 4:38 pm
get sp_whoisactive and run it while the lock is happening
http://whoisactive.com/downloads/
best to do it when you manually run the proc manually as at the point you know the lock is in place.
then post here the output - it should contain both the blocking session and the blocked session and it should include the sql statement and lock type / lock wait for both sessions
can can you also give us the output of
select is_read_committed_snapshot_on
from sys.databases
where name= 'YourDatabase'
March 1, 2018 at 10:16 pm
Post the code of the file upload procedure, point out the statement which is executed when the locking happens, and the statements from the app which are blocked.
Don't forget DDL statements for all objects involved, including indexes and triggers, if any.
_____________
Code for TallyGenerator
March 2, 2018 at 2:43 am
junk.mail291276 - Thursday, March 1, 2018 4:01 PMWhen you say "If you disable both page and row locks, you're forcing SQL to lock only at the table level," are saying that disabling both page and row locking forces table locking to be enabled (even if it was disabled before)? If so, then maybe my tests above weren't good. Not that I really understand what they're doing nearly well enough, but this would means that if I run my script for disabling lock_escalation (which I think disabled locking at the table level... but I could be wrong), and then run my script for disallowing row and page locking, then that will RENABLE table locking... right?
No. Locking at a table level is always an option, it cannot be disabled. SQL locks either at row (highest concurrency), page or table (lowest concurrency). By turning off row and page, you've told SQL that it can only lock entire tables, even if it really needed to lock a single row.
If you didn't have locking problems before doing that, you definitely will after.
Lock escalation is about changing row locks to table locks to reduce memory pressure.
Indeed, the stored procedure reads from a table which is being written to during the file upload, though it's still the case that there should be no overlap between the records the stored procedure reads and those that the file upload process inserts or updates.
But since you've forced table-level locking by disabling all other options , that's irrelevant. SQL can only lock at the table level now.
Please re-enable row and page locking, put lock escalation back to table (on all tables) and then we can start looking at what's actually causing the blocking
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
March 2, 2018 at 3:19 am
junk.mail291276 - Thursday, March 1, 2018 4:23 PMOh, and I forgot to mention: sys.dm_exec_sessions confirms that it is our application session that's doing the blocking (where the file is uploaded).
Hmmmm... are you loading the file directly to the ultimate target table or are you using a staging table? If you use a staging table, nothing would be blocked during the upload and then you could blast the data into the final tables with no network latency to worry about. It would also give you the chance to validate data after the upload and before the merge to the ultimate target table without locking up that target table during the validations.
The other neat thing about doing the initial upload into a staging table is that it could actually be in a "scratch" database that uses the SIMPLE or BULK LOGGED recovery model so you could take advantage of the nearly double performance of "Minimal Logging" during the initial load.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 2, 2018 at 5:35 am
As you now know the application is hitting a timeout, you could set the timeout on the SQL command to a higher value than the default (30 seconds). In C# this would be comm.CommandTimeout = ### where ### is the number of seconds you're prepared to wait. If you do this, you'll need to indicate to the user why they're waiting and tell them what the maximum wait time is.
March 5, 2018 at 3:10 pm
Hello everyone,
Thanks for all the replies. Sorry for the late response.
On Friday, I was preparing a response to each of your posts until I got to Sergiy's request to post the code. While looking through the code, I found a few spots that I thought could be blocking. So I've been working on that. Essentially, the code was saving to the database 3 times for every row of the file it parsed. This was completely unnecessary so I got rid of all the saving, and did one save to the database after the entire file was parsed. That one save should take under a second to complete, so even if it does block, at least other processes waiting to gain access to the database won't time out.
However, I also found another culprit: before the file is parsed, a stored procedure is called to delete all previous data (from a previous file upload). The stored procedure take under a second to run, but for some reason, when it is called by the code, it seems not to release the database lock even after it's done. For example, I would put a breakpoint somewhere in the file parsing (after the stored procedure has been called but before the data from the new file has been saved to the database). Upon hitting the breakpoint, I'd attempt to run a simple SQL query directly in SSMS (any query will do) and it is blocked. Only after all the data from the file has been parsed and saved to the database will the query be unblocked.
I posted a question about this at Microsoft's Entity Framework help forum where you can see the code:
https://social.msdn.microsoft.com/Forums/en-US/8e135c61-eec1-4598-864d-5ed897677b1c/stored-procedure-locking-database-
even-after-its-done?forum=adodotnetentityframework
I don't suppose anyone would know why this is happening. I don't believe the problem is with the stored procedure itself because, like I said, I can run it in SSMS, it takes a second to run, and no blocking occurs after it's done. The blocking only persists when the stored procedure is run from the code.
March 5, 2018 at 4:51 pm
Based just on your post, I would say when run from your code that the EF is running it inside a transaction. When everything is completed and returns to your code, the transaction is committed, releasing the locks.
March 5, 2018 at 8:34 pm
junk.mail291276 - Monday, March 5, 2018 3:10 PMHello everyone,
Thanks for all the replies. Sorry for the late response.
On Friday, I was preparing a response to each of your posts until I got to Sergiy's request to post the code. While looking through the code, I found a few spots that I thought could be blocking. So I've been working on that. Essentially, the code was saving to the database 3 times for every row of the file it parsed. This was completely unnecessary so I got rid of all the saving, and did one save to the database after the entire file was parsed. That one save should take under a second to complete, so even if it does block, at least other processes waiting to gain access to the database won't time out.
However, I also found another culprit: before the file is parsed, a stored procedure is called to delete all previous data (from a previous file upload). The stored procedure take under a second to run, but for some reason, when it is called by the code, it seems not to release the database lock even after it's done. For example, I would put a breakpoint somewhere in the file parsing (after the stored procedure has been called but before the data from the new file has been saved to the database). Upon hitting the breakpoint, I'd attempt to run a simple SQL query directly in SSMS (any query will do) and it is blocked. Only after all the data from the file has been parsed and saved to the database will the query be unblocked.
I posted a question about this at Microsoft's Entity Framework help forum where you can see the code:https://social.msdn.microsoft.com/Forums/en-US/8e135c61-eec1-4598-864d-5ed897677b1c/stored-procedure-locking-database-
even-after-its-done?forum=adodotnetentityframeworkI don't suppose anyone would know why this is happening. I don't believe the problem is with the stored procedure itself because, like I said, I can run it in SSMS, it takes a second to run, and no blocking occurs after it's done. The blocking only persists when the stored procedure is run from the code.
It sounds like you're saying that the code is importing a file, parsing it, and then writing it to SQL Server. If that's correct, then why reinvent a wheel that has been tested to the max and is very, very fast. Use either BCP or BULK INSERT to load the file. If you need to delete all rows from the target table first, then just use TRUNCATE.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 6, 2018 at 6:57 am
junk.mail291276 - Monday, March 5, 2018 3:10 PMI posted a question about this at Microsoft's Entity Framework help forum where you can see the code:https://social.msdn.microsoft.com/Forums/en-US/8e135c61-eec1-4598-864d-5ed897677b1c/stored-procedure-locking-database-
even-after-its-done?forum=adodotnetentityframeworkI don't suppose anyone would know why this is happening. I don't believe the problem is with the stored procedure itself because, like I said, I can run it in SSMS, it takes a second to run, and no blocking occurs after it's done. The blocking only persists when the stored procedure is run from the code.
Compare connection settings in SSMS and the application. They might be a bit different.
You need to learn following T-SQL technic:
DELETE RI
FROM [dbo].[RecommendationSequence] RC
INNER JOIN [dbo].[ProjectRecommendationImpl] PRI ON RC.RecommendationSequenceId = PRI.[RecommendationSequenceId]
INNER JOIN [dbo].[RecommendationImpl] RI ON RI.[ProjectRecommendationImplId] = PRI.[ProjectRecommendationImplId]
WHERE RC.[ProjectId] = @projectId
DELETE PRI
FROM [dbo].[RecommendationSequence] RC
INNER JOIN [dbo].[ProjectRecommendationImpl] PRI ON RC.RecommendationSequenceId = PRI.[RecommendationSequenceId]
WHERE RC.[ProjectId] = @projectId
DELETE RC
FROM [dbo].[RecommendationSequence] RC
WHERE RC.[ProjectId] = @projectId
This does the same set of deletes as the first group of 2 cursors, but without loops and cursors.
As MS technician commented, you've got too many cursors within a transactin. It may be the problem.
Use this approach to eliminate the rest of cursors in the procedure.
And the last thing for now: what happens when one of the deletions fails?
I can see the procedure itself will return an error, but it seems the outside code will proceed with import regardless of the deletion outcome.
Breaking data integrity usually does not end well.
It would be nice to see the code of the upload procedure as well.
If it uses as many cursors - there might be an explanation for the different behaviour in SSMS and the application code.
_____________
Code for TallyGenerator
March 6, 2018 at 10:22 am
Would you say this can still be the case even if the stored procedures runs in under a second when executed in SSMS (and then releases the lock), but when called from the application, the lock is still held for several minutes after the stored procedure completes?
Viewing 15 posts - 16 through 30 (of 31 total)
You must be logged in to reply to this topic. Login to reply