January 8, 2010 at 1:21 am
Hello,
How do you find out your own SPID?
January 8, 2010 at 1:33 am
If your talking about within SSMS then it is displayed at the bottom of each query window you open.
Or you can run SELECT @@SPID
January 8, 2010 at 1:46 am
I am creating a C# .NET web application that displays SPIDs that are
causing locks and let users to kill selected spids.
I just wanted hide their spid to prevent users from getting SQLExceptions caused by trying to delete their own spid.
SELECT @@SPID Seems to work fine for me!
Thank you!
January 8, 2010 at 7:33 am
idiescreaming (1/8/2010)
I am creating a C# .NET web application that displays SPIDs that arecausing locks and let users to kill selected spids.
Are you sure this is something you want the users to be able to do? This can be very dangerous. Correcting the process(s) that is causing the locks would be the most appropriate solution, not allowing the users to kill SPIDs and maybe cause corruption or inconsistencies at some point. One question, are you a DBA or developer (or some combination of the two)?
-- You can't be late until you show up.
January 8, 2010 at 9:37 am
idiescreaming (1/8/2010)
I am creating a C# .NET web application that displays SPIDs that arecausing locks and let users to kill selected spids.
That's an extremely bad idea if the users can just kill spids without knowing what those connections are doing, which it seems they don't. Killing a connection is not something that should just be done whenever there are lock. What if the connection that they kill is the HR department updating your salary and bonus (as an example)
If you have blocking problems, you need to identify the queries that are causing the long-duration locks and do some optimisation. This can be changing the query, it can be tuning indexes, it's usually both.
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
January 11, 2010 at 7:52 pm
Terry and Gail, Thank you for the reply
My role is Systems Engineer developing web apps mainly in C# .NET.
As both of you pointed out that killing SPIDs is not a good idea and I do understand why, is there a way to safely unlock the process using query other than killing SPIDs?
My client's request was to unlock a process that is locking the DB from the web application by clicking a button.
This is the renovation of the previous project and was created to transfer DB between different servers using SSIS packages(DB Transfer Tasks).
This application is introversion so no random user accesses
It needs to be kicked off from the screen.mmm
January 12, 2010 at 1:05 am
idiescreaming (1/11/2010)
As both of you pointed out that killing SPIDs is not a good idea and I do understand why, is there a way to safely unlock the process using query other than killing SPIDs?
No. The SQL database supported the ACID requirements. That means atomicity, isolation, consistency, durability. Locks are used to ensure isolation, that a process cannot affect what another process is doing; and consistency, that the database appears transactionally consistent at all times. If it were possible for one connection to revoke locks that another had places, isolation (and consistency) would be out of the window
My client's request was to unlock a process that is locking the DB from the web application by clicking a button.
This implies that you're having locking problems. Is that the case? Usually, locking problems are as a result of poorly written queries and/or inadequate indexing. I strongly suggest you look at that and have a discussion with the client regarding why it's a bad idea to roll back transactions without knowing what they're doing.
Other option is to consider the DB-wide use of snapshot isolation levels. That means that readers don't take locks at all, but use row versions to get consistent results. This means that there's very little locking (writers still lock).
The downside is that this really impacts TempDB.
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
January 12, 2010 at 2:27 am
This implies that you're having locking problems. Is that the case? Usually, locking problems are as a result of poorly written queries and/or inadequate indexing. I strongly suggest you look at that and have a discussion with the client regarding why it's a bad idea to roll back transactions without knowing what they're doing.
Sorry but I am not really sure of what causing the locks and what type of locks are occurring. I'll be visiting the client tomorrow to ask several questions about this.
What if they know exactly what each processes are doing?
Would that still be no good to kill a process?
I will update with more info tomorrow.
Thank you
January 12, 2010 at 2:42 am
idiescreaming (1/12/2010)
What if they know exactly what each processes are doing?
Know and understand. That means seeing what the currently executing SQL is, what the currently executing stored proc is and where that fits in with the application and knowing exactly what the consequences of rolling that back are. Not trivial unless the person is very familiar with the app design and code and knows SQL.
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
January 12, 2010 at 9:20 am
Follow Gail's advice and find out the cause.
Think of a traffic jam in the city. You are suggesting to eliminate the traffic jam by using a crane to lift out vehicles stuck in traffic and toss them in the river. Do you care about the contents of the cars & trucks transporting people & material ?
Gail is suggesting to look at the traffic lights to see if they are timed correctly. Or maybe alternate roads are blocked and need to be fixed, or a truck is stalled and causing a blockage ....
When you see spids involved in blocking, use DBCC inputbuffer (spid#) to get an idea of what is being run. It may point you to stored procedures or ad hoc queries. You can then dig into the code with execution plans to track down the cause. Maybe a long running select could use NOLOCK, or there are missing indexes .... Performance tuning is a big area.
January 13, 2010 at 5:47 pm
Great ! Nice and true representation of real life blocking and locking scenario 🙂
Amol Naik
January 14, 2010 at 3:16 am
homebrew01 (1/12/2010)
Maybe a long running select could use NOLOCK,
Aaahhhhh!!
Why does everyone recommend nolock any time there's a long running select? How about tuning the query, tuning the index and maybe switching to snapshot isolation? Or do people like recommending methods that can return incorrect information?
See - http://sqlblog.com/blogs/andrew_kelly/archive/2009/04/10/how-dirty-are-your-reads.aspx
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
January 14, 2010 at 4:30 am
GilaMonster (1/14/2010)
homebrew01 (1/12/2010)
Maybe a long running select could use NOLOCK,Aaahhhhh!!
Why does everyone recommend nolock any time there's a long running select? How about tuning the query, tuning the index and maybe switching to snapshot isolation? Or do people like recommending methods that can return incorrect information?
See - http://sqlblog.com/blogs/andrew_kelly/archive/2009/04/10/how-dirty-are-your-reads.aspx
Yes, my bad. I was thinking at least it's better than the OPs imminent plan to kill spids. But they should take the time to fix the problems.
January 14, 2010 at 4:44 am
Better, yes, but still not a good thing to recommend without at least mentioning what nolock really means.
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
January 15, 2010 at 9:59 am
I totally agree with Gail. Find the root cause and fix that.
Killing spids will come back to haunt you. At some point, something bad (maybe really bad) is likely to happen.
And it is likely to get worse over time.
You would be the one remembered because you created and deployed the button.
No one may even know who told you to build it.
Greg E
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply