| Author |
Topic |
|
xrum
Yak Posting Veteran
87 Posts |
 Posted - 2012-01-19 : 11:13:31
|
| when i try to run an insert within an application, it hangs and eventually times out.i ran the EXEC SP_WHO2 while it was still running and noticed that it was blocked by another process and there was a "lock" in the "wait type"what does this mean? how do i fix it? i'm so lost.the process that was in the "Blocked By" field had a simple select statement in it. i validated the statement and it works fine. |
|
|
X002548
Not Just a Number
15586 Posts |
|
|
xrum
Yak Posting Veteran
87 Posts |
Posted - 2012-01-19 : 11:40:46
|
please see screenshots attached:process that is blocked is a simple delete stored procedure. it works if i run it byitself, outside of the application process that blocks the above procedure, is a simple select count(*) function. i'm so lost :( |
 |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2012-01-19 : 12:27:27
|
| The select has locks on the data it is processing for the select and the update has to wait until the select is complete (data modifications needs exclusive locks)Optimise the queries so that they run faster and hence have less chance to interfere with each other, or consider one of the snapshot isolation levels.--Gail ShawSQL Server MVP |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2012-01-19 : 12:30:18
|
| p.s. Chapter 6 [url]http://www.simple-talk.com/books/sql-books/troubleshooting-sql-server-a-guide-for-the-accidental-dba/[/url]--Gail ShawSQL Server MVP |
|
|
|
xrum
Yak Posting Veteran
87 Posts |
Posted - 2012-01-19 : 13:41:50
|
quote:
Originally posted by GilaMonster
The select has locks on the data it is processing for the select and the update has to wait until the select is complete (data modifications needs exclusive locks)Optimise the queries so that they run faster and hence have less chance to interfere with each other, or consider one of the snapshot isolation levels.--Gail ShawSQL Server MVP
but the query that locks up the process is just a simple select count(*) from table1, where x="x"there is no way for me to make it faster.so confused :( |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2012-01-19 : 13:47:20
|
"there is no way for me to make it faster."You sure?What doesSET STATISTICS IO ON; SET STATISTICS TIME ONselect count(*) from table1, where x='x'SET STATISTICS IO OFF; SET STATISTICS TIME OFFGO give you? (in particular the Scan Counts and the Logical I/O values)And you could also look at the Actual Query Plan in SSMS and see if it recommends and indexes etc. |
|
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2012-01-19 : 14:45:40
|
| index on the column under consideration would improve that query immensely (assuming you don't have one).You may want to try READ_COMMITTED_SNAPSHOT isolation level also (Gail mentioned changing the isolation level).Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2012-01-19 : 15:08:50
|
quote:
Originally posted by xrum
but the query that locks up the process is just a simple select count(*) from table1, where x="x"there is no way for me to make it faster.so confused :(
Sure about that? You've got a nice small index on the column in the where clause and the data types match so that there's no implicit conversions and you have checked that the predicate is SARGable?Did you take a look at chapter 6 of that book.--Gail ShawSQL Server MVP |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
|
SQL Server Locks?