Deadlock on select statement

Hi,
I have WebService(.Net framework 2.0) worked with SQL2005 server. Now and then I get an error about deadlock (10 times at day), but I can not reproduce error by myself. I already use SQL Server Profile to get information about deadlock, nothing new and also notnig found on internet (anyone have reasons).
Error description: "Transaction (Process ID 233) was deadlocked on lock | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction.."

Here is pseudo code:

CONNECTION1:
BEGIN TRAN ONE
SELECT.... (can be T1 and T2)
UPDATE TABLE1 SET....
SELECT...
INSERT INTO TABLE2...
UPDATE....
COMMIT TRAN ONE
=================================
CONNECTION2:
BEGIN TRAN TWO
SELECT * FROM TABLE1 T1 WITH(nolock)
INNER JOIN TABLE2 T2 with(nolock)
where...
COMMIT TRAN TWO

and I get deadlock on CONNECTION2?!?!?!?!!?
I just have select statement(just one select) and I'm 100% sure there is no INSERT/DELETE/UPDATE and there is not store procedure call or trigger doing write in table1,2. And second, there is NOLOCK parameters!!!

I'm confused, I thought that deadlock is about two transaction lock same object used in differend order.
How can TRAN TWO lock something or have can be deadlocak victim????
Does anyone have any ideas what is this about???

Thansk's for help, tips etc...

David
hambiAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
A (nolock) within a transaction does not make sense. Why are you starting transaction TWO?
0
hambiAuthor Commented:
It is the same problem if not using (nolock) parameters in select statement.  So we can forget (nolock) parameter.

Yes I know, transaction TWO is unnecessary (in this pseudo code), but our connection base class always create transaction, even if we just read data. But anyway, why deadlock...select does not lock anything, does't it?
0
Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
Select does set a read (share) lock for each row while reading. Depending on the Isolation Level that locks are held till commit.
You could try if using Isolation Level read uncommitted would help. It MIGHT ignore the transaction status.

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
The Five Tenets of the Most Secure Backup

Data loss can hit a business in any number of ways. In reality, companies should expect to lose data at some point. The challenge is having a plan to recover from such an event.

JulianvaCommented:
Did you use the deadlock graph in Profiler trace to capture the queries that are causing the deadlock.

You need to find the queries thats causing the deadlock, a deadlock is a timing issue , once you found the queries you need to look at its execution plan to see what indexes are being used. Deadlocks occur when both the queries wants to use the same resources.it could be that the an index is the resource.
most times you either create an index for the query  or drop an index.

0
JulianvaCommented:
yes select does lock the table to prevent dirty reads. if the select is too slow (index or statistics missing) then the other query cannot access the same table until the lock is released.

0
hambiAuthor Commented:
Okej, okej. That's correct, select lock the table or result set (rows, pages...), but it need's to lock another resource too or we say need to lock more than one resource at the time. Index can be locked in select query, can you describe that?
We use READ COMMITED isolation level, there is no way to change this.

Hmmmm, maybe I see the light on the other side of tunnel.....
0
hambiAuthor Commented:
"A (nolock) within a transaction does not make sense." That's is not correct, it's dirty read and does not wait for resource locked by transaction ONE.
0
hambiAuthor Commented:
I have problem drawing graph for deadlocks from SQL profiler. I don't know why, I have turn on SQL trace (1205), profiler also catch and detect deadlock graph and chain...but there is no data in xdl file and export deadlocs also doesn't work. So I can not help with that....if I just saw a graph....
0
Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
When you start a transaction, you want a transaction. Nolocks are not part of the transaction. Of course you can do this, but the effect is very unreliable.
Here, where the transaction is not meant to be one, there is a sense, but only very small ;-)
0
JulianvaCommented:
Do you know the two queries that is causing the deadlock.?
0
hambiAuthor Commented:
"Do you know the two queries that is causing the deadlock.?": I just get a deadlock victim from my own error logs. The second one is "half unknown", but I have potencial candidate, because I log every executed query (with timestamp).

0
JulianvaCommented:
once you get the query/queries , use the database tuning advisor to tune the query , the DTA will recommend indexes and statistics.

0
hambiAuthor Commented:
Problem is, that second (write) transaction include 10 different SQL statements(select, update etc).

But I don't get it, why SELECT statemend is in deadlock, how so. It's create share lock (tables, pages etc), but secons transaction has exclusive permission, so it can get resource from first one (select).

But I thnig I can resolve this with index, bypass...if I success I'll wtite here...thanks both for tips.
0
Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
A exclusive lock can not be aquired if another transaction is locking in shared mode. Only self-owned locks can be converted from shared to exclusive. Not to allow for updates is the sense in read committed isolation level.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.