[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2274
  • Last Modified:

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
0
hambi
Asked:
hambi
  • 6
  • 4
  • 4
1 Solution
 
QlemoC++ DeveloperCommented:
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
 
QlemoC++ DeveloperCommented:
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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
 
QlemoC++ DeveloperCommented:
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
 
QlemoC++ DeveloperCommented:
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

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 6
  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now