blocking scenario
-
Hi All,
I am seeing a session is blocking another session which is started much later. I am saying this based on start_time and duration columns using sp_whoisactive. is it possible or its a timing issue? Can anyone explain how that's possible?
DELETE statement is blocking any UPDATE statement on a view.
Regards,
Sam
-
the first query is running on a loop, executing a series of statements ,including the delete you see. This loop has a outer transaction so until the transaction is finished all other queries will be locked.
-
I personally can't read the screenshot, but kudo's to you frederico_fonseca for reading that!
Just as a second opinion on this, I think that is normal behavior as well. If I run a poorly written UPDATE on a view and the view is not indexed OR the update is not using the index, then the update MAY need to look at ALL of the rows. If I am expecting it to update 1 row (for example) on a table with 10,000,000,000 rows, that could be incredibly slow. Lets say for arguments sake that it takes an hour for the update to complete. If I start the update and then 30 minutes later I start a DELETE, the DELETE will be blocked by the UPDATE and will need to wait for the update to complete before the delete can start. Same thing could apply if the DELETE was happening on a table used by the view. The UPDATE will block all write operations on all tables inside the view even if it only needs to update a single table. Same thing happens if you reverse the UPDATE and DELETE - if the DELETE takes an hour to complete, the UPDATE will be blocked until the DELETE completes.
So what you are seeing is 100% possible. What I would recommend is that you use stored procedures to change the data and those stored procedures should make the changes on the base tables rather than the views. The SELECT operations can be against the views, but data change operations I prefer to do at the table level. I try not to update views as it introduces extra overhead. If I need to modify (insert, update, or delete) all of the tables inside the view, then I'll use the view, but otherwise I would write the stored procedure code to ONLY look up the data I need.
The other reason that you can end up with a long running UPDATE/DELETE/INSERT operation is if your transaction is not committed. What I mean is if you have a "BEGIN TRANSACTION" statement, then you do your UPDATE/DELETE/INSERT operation and you do not put in a "COMMIT TRANSACTION" (or "ROLLBACK TRANSACTION" as the case may be). The table will be blocked from all other data changing operations. So even if your UPDATE completes in 0.01 seconds, you can have the transaction open for a long time and block some other operations on the table.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply