January 14, 2008 at 5:46 am
Hi,
My first time posting to this site so hopefully I am in the right place....
We have had blocking occuring in our database for the past few months which can happen almost every day. I run various scripts to send the blocking information to our developers but they so far have not been able to solve the problem.
What I have noticed however is that out of a database user group of about 50 - it is the same user who is always the primary blocker - even though the user will run the same sp's as anyone else...
Does it make sense that a particular database user can cause blocking when another user running the same sp would not?
Any help very much appreciated,
Cheers,
Jack
January 14, 2008 at 7:31 am
A user(id) can connect multiple times.
So indeed a user can block himself (if he has more than one connection)
There have been issues with parallelism where one SPID ( a single user connection) could block itself. If I recall correct, that issue should be fixed with sp4 + the latest cumulative update for sql2000.
AFAIK there only way of setting deadlock priority is downward, so you'd state that in case of deadlocksituation you'd be the prefered victim to be kicked out.
If your users all connect using the same isolation level, there should be a fairly even distribution for deadlock victims, unless a "smart" one connects and locks some stuff at the beginning of his/her working day and doesn't commit/rollback the stuff he/she does; In that case this user may indeed be the cause for some deadlocks.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
January 14, 2008 at 8:01 am
Another thought is that if the SP that everyone is running can return a different number of rows depending on what parameters are sent into the SP then the user that is usually causing the blocking may be using parameters that return (or affect) thousands of rows where as the other users could be using parameters that only return (or affect) a few rows.
Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems
January 15, 2008 at 8:06 am
Hi Alzdba / Robert - many thanks for replying to my post - food for thought for sure - we have removed a transaction from a stored procedure that was deleting records from 2 tables and we think might be the cause of all the blocking - not sure if this will solve the mystery of the same user causing the blocking but perhaps she was calling this sp a lot more than others - I can investigate - so we shall wait and see now - thanks again....
January 15, 2008 at 8:17 am
Maybe dbcc opentran can help you a bit.
ado.connection.begintran causes sqlserver to swap to "implicit transactions". That may not be the effect you wanted because you have to add your own commit/rollback statement ..
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
January 15, 2008 at 8:34 am
Also keep in mind that blocking is normal and expected. It should just be of a short duration. If you're having longer durations, I'd trace (Profiler) the user you think is having an issue, along with another user and see if there's something different.
January 16, 2008 at 3:07 am
As Steve said, some blocking is normal.
After you install SQL 2000 SP4, you may well see blocking happening when nothing was reported in SP3. The blocking is not anything that was introduced with SP4, it always happened. It is just the SP4 is reporting it correctly where previously it was not reported.
If you get the same block lasting more than 2 refreshes of the EM screen, you may have a problem. (Do not confuse a SPID shown as blocking in 2 refreshes as the SPID having the same block showing in 2 refreshes, things may have moved on...) You then need to use profiler to identify what is really happening.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
January 16, 2008 at 12:56 pm
Also consider user behavior. Has anyone changed roles, new staff hired, etc?
Ideally any action that involves the DB issuing locks should be completed quickly but the user may not allow this. E.g. if the user opens a screen in the App and initiates an update, then goes to lunch (this used to occur regularly here) then you will see locking and potentially a situation where deadlocks might occur.
Try to ensure that all transaction-related users actions are completed quickly and that the users know where it is safe to stop, e.g. we have a rule that no-one can 'park' on anything other than the main menu of our ERP. That is to say that if they navigate away from the menu they must either cancel or complete their activity and not leave the app open on any other screen.
In my experience all locking related problems are due to one of
1/ bad coding
2/ bad performance
3/ bad user actions
The art of course is to identify which one is causing your issue. From your statement that it recently started I would start from the bottom of this list, unless you did a release the day it started, in which case I'd start from the top.
Regards
Karl
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply