Why is a SELECT preventing an INSERT?

[BlakeK]

I have a query that takes 5 minutes to run.
I already posted a question about ideas on improving the speed here:

http://www.tek-tips.com/threadminder.cfm?pid=962

But, my new problem is that it appears that whenever a user runs this query, it is preventing other users from inserting new rows into one of the tables the slow query references.

I have a screen shot of the Locks being held by the user running the SELECT query here:

http://www.corvetteforums.net/blake/block.jpg

That user is currently blocking another user who was trying to do an INSERT statement against tbl_advertisement

Why would a SELECT prevent an INSERT from occuring?

The user doing the INSERT is using an MS Access front-end, connecting to the SQL server via ODBC linked tables. The error they get is "ODBC call failed: Timeout has expired."
They can retry the INSERT statement and it will continue to fail while the other user is running the SELECT query. As soon as the SELECT completes, if you retry the INSERT it is instantly successful.

 

[BlakeK]

One note:
If you look at the other post I linked to and happen to notice that there are only two tables in that query, and then look at the screen shot and see there are several tables, that is because the query I posted was a "dumbed down" version just to post here only showing the parts that made the query slow. The actually query contains several table joins, but are mainly def tables that would have only confused things for people viewing the code here. Removing those tables made no difference in execution time versus the "dumbed down" version I posted in that other thread.

 

[Catadmin]

Are you using SQL 7 or SQL 2000?

In SQL 7, a Select statement creates a table lock on the table in question that would prevent users from inserting data.

SQL 2000 has the ability to page locks, row locks and shared locks which free up more space. However, the lock type used is dependent on how the select statement is written.

The next time this happens, open up Enterprise Manager and navigate to Management -> Current Activity -> Locks / Process ID and Locks / Object. By clicking on the items listed under these two headers, it will show what process or object has the lock and what type of lock it is.

BOL for SQL 2000 has the lock types listed under "Locks, Shared" and click on "Understanding Locking in SQL Server" when the pop up menu comes up.

After you determine what types of locks are causing the problem, you can look at re-writing the Select query to minimize the lock issue.



Catadmin - MCDBA, MCSA
Remember, "Work" is a four letter word. And you know what your mother told you about using four letter words!

 

[BlakeK]

Catadmin,
I am running SQL 2000 Standard SP3.

As for your comment:
"The next time this happens, open up Enterprise Manager and navigate to Management -> Current Activity -> Locks / Process ID and Locks / Object. By clicking on the items listed under these two headers, it will show what process or object has the lock and what type of lock it is."

Did you see the link to the screenshot I took in the original post? That is the exact section you mentioned.
If you look at that, the locks the Blocking process had where all "S" or "IS", with the exception of the "X" in tempdb at the bottom.

 

[mrdenny]

Locking can be a pain. The quickest way to prevent the blocking is to isolate the transactions that are doing the select only. That can be done is one of two ways.

1. Add "WITH (NOLOCK)" to the query hint.
2. Add "SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED" to the begining of the transaction.

Here are examples of each.

create procedure usp_test1 as
select *
from employees WITH (NOLOCK)
go

create procedure usp_test1 as
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
select *
from employees
go

What each of these commands does is tells SQL to do what are called dirty reads. Basically it doesn't care if someone else is updating the data. It's going to show what it's got available. It also has the nice benefit of preventing the select statements from locking data.

The problem is that spid 73 has a shared lock on the tbl_advertisement. This means that anyone else reading is ok (they also would want a shared lock), and anyone updating is ok (they would want an update lock), but inserting requires a "Intent exclusive" lock which is not compatible with the Shared lock.

There is a nice matrix of what locks work with what locks in BOL. Select "locking, modes" in the Index then "Lock Compatibility" from the popup.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)

 

[BlakeK]

Denny,
Thanks for the information. The query is running through an MS Access front end, so I'm not sure what options I'll be able to use. I may be able to do a pass through query and use the WIHT (NOLOCK) option you mentioned. I don't think I'll be able to anything with setting isolation levels.
If I can't use the NOLOCK hint wiht a pass through query, I guess I'll have to looking into making the query into a stored procedure and calling it from Access.
I'll be sure to read up on that section of BOL you mentioned tomorrow.
Thanks again,
Blake

 

[mrdenny]

MS Access sure isn't helping you any here. Access some some very funky locking that reaks havoc on other processes.

I'd definetly recommend making it a stored proc. That will give SQL Server a lot more control, not to mention allow you much more control.

I've got no idea if MS Access will like the WITH (NOLOCK) idea. I don't think it allows for table or index hints (which is what this is).

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)

 

[BlakeK]

Denny,
The WITH(NOLOCK) hint worked perfectly!
And it even worked through Access, as I built the SQL and passed it to the server.
That alleviated the current problem with the blocking that was occuring.
I think I will still look to make it a stored procedure on the server and call that from Access anyway.
If I am not able to get the query to run faster than the 5 minutes it takes now, I have a plan to lessen the poor experience the users are having...
Right now, they click on the report in Access and then Access is locked up until the query finishes running and returns results.
I was thinking of having the button that now opens the report, instead call a stored procedure and pass their parameters.
I would like to have Access not wait for the stored procedure to finish.
Then, the stored procedure can run the query on the server with their parameters and put the aggregate report data into another table. When that is done, have the server send them an email and let them know the report is complete. Then the user could go to another interface and see the reports they requested that are done. And pick one to view, which would then be a straight quick SELECT out of the new aggregate table.
The things I will have to learn in order to do this is:
1.) How to initiate a stored proc from Access and not have Access hang and wait for the results
2.) How to interface SQL with our Exchange server and send out emails when the proc is done

If I have any problems with those things, I'm sure I'll be back to the Tek-Tips boards to find the answers!

Anyway, thanks again for your help on this, I have definitely learned a lot from the posts I have made the last few days concerning our system. Eveyone has been very helpful, and mrdenny and Catadmin have gone above and beyond to give me great information on all my posts.

 

[Catadmin]

Glad we could help. And sorry I didn't catch the lock link on your first post.



Catadmin - MCDBA, MCSA
Beware the error of pre-emptive poultry inventory!

 

[mrdenny]

1. Not sure how to make access to this. Access probably isn't going to respond until the query is done. One possibility would be to have access generate the SQL to execute, dump that into a queueing table, then have a job on the SQL Server pull the commands from that table, and execute them against the SQL Server.

2. Check out my FAQ about setting up SQL to send email faq962-4452.

By putting this code into a stored procedure things should speed up. SQL will now be able to cache the execution plan, and cache the data into RAM. All of which will help with the speed.

You should also review your indexes, and disk usage. I'm sure you can speed it up. With the proper changes I'd pretty much be willing to put money on it.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)