deadlock on a single Select not in a transaction

Hi,
We have a web application developped in asp.net (I think it's not relevant,
but well, it's so you know)... Yesterday, we received the following message
"Transaction (Process ID 69) was deadlocked on lock resources with another
process and has been chosen as the deadlock victim. Rerun the transaction. "
The thing is, the query was a simple select with inner joins between 3
tables (like select fields from table1 inner join table2... inner join
table3...). This command is not in a transaction, so the deadlock seems
impossible. And moreover, the deadlock occurs on the DataReader.Read() not
on the Command.ExecuteReader(...).
Can someone explain why the deadlock can have occured and what could be the
cause and solution to it? Can it be a bug in SQL Server or in the .net
framework (I really doubt about it)?
thanks
ThunderMusic
What you're seeing is not concerning a Transaction as you are imagining it.
In essence, every operation performed by SQL Server is a transaction.
However, a Transaction (capital T) is a grouping of transactions (or
operations) into a single atomic unit which either fails or succeeds as a
whole. In this case, the reference is to to a (small t) transaction, which
is the operation of your query.
The exception occurs when 2 transactions (or processes) are trying to access
the same database object (such as a row in a table) at the same time. Each
process tries to get a lock on the object, and only one can. The other is
therefore killed. Here's a good article about this, and how to deal with it.
Notice that the most common tactic is simply to try again:
http://www.sql-server-performance.com/deadlocks.asp
HTH,
Kevin Spencer
Microsoft MVP
Printing Components, Email Components,
FTP Client Classes, Enhanced Data Controls, much more.
DSI PrintManager, Miradyne Component Libraries:
http://www.miradyne.net
"ThunderMusic" <NoSpAmdanlatathotmaildotcom@.NoSpAm.com> wrote in message
news:ewkig6PiHHA.2028@.TK2MSFTNGP03.phx.gbl...
> Hi,
> We have a web application developped in asp.net (I think it's not
> relevant, but well, it's so you know)... Yesterday, we received the
> following message "Transaction (Process ID 69) was deadlocked on lock
> resources with another process and has been chosen as the deadlock victim.
> Rerun the transaction. " The thing is, the query was a simple select with
> inner joins between 3 tables (like select fields from table1 inner join
> table2... inner join table3...). This command is not in a transaction, so
> the deadlock seems impossible. And moreover, the deadlock occurs on the
> DataReader.Read() not on the Command.ExecuteReader(...).
> Can someone explain why the deadlock can have occured and what could be
> the cause and solution to it? Can it be a bug in SQL Server or in the .net
> framework (I really doubt about it)?
> thanks
> ThunderMusic
>
|||You might enjoy reading the three articles starting at:
http://blogs.msdn.com/bartd/archive/2006/09/09/Deadlock-Troubleshooting_2C00_-Part-1.aspx
The bottom line is that a select is a single statement transaction that can
hold locks, and a conflicting desire for locks is what causes deadlocks.
(The Deadly Embrace, where each process is holding something the other
process wants.)
RLF
"ThunderMusic" <NoSpAmdanlatathotmaildotcom@.NoSpAm.com> wrote in message
news:ewkig6PiHHA.2028@.TK2MSFTNGP03.phx.gbl...
> Hi,
> We have a web application developped in asp.net (I think it's not
> relevant, but well, it's so you know)... Yesterday, we received the
> following message "Transaction (Process ID 69) was deadlocked on lock
> resources with another process and has been chosen as the deadlock victim.
> Rerun the transaction. " The thing is, the query was a simple select with
> inner joins between 3 tables (like select fields from table1 inner join
> table2... inner join table3...). This command is not in a transaction, so
> the deadlock seems impossible. And moreover, the deadlock occurs on the
> DataReader.Read() not on the Command.ExecuteReader(...).
> Can someone explain why the deadlock can have occured and what could be
> the cause and solution to it? Can it be a bug in SQL Server or in the .net
> framework (I really doubt about it)?
> thanks
> ThunderMusic
>
|||Kevin Spencer wrote:
...
> The exception occurs when 2 transactions (or processes) are trying to access
> the same database object (such as a row in a table) at the same time. Each
> process tries to get a lock on the object, and only one can. The other is
> therefore killed. Here's a good article about this, and how to deal with it.
> Notice that the most common tactic is simply to try again:
...
It's bit simplified, what you describe is not a deadlock situation. One
transaction will happily wait for lock to be released. Typical deadlock
situation is:
Transaction1 holds lock on A
Transcation2 holds lock on B
Transaction1 wants lock on B
Transcation2 wants lock on A
This cannot be resolved by waiting, so one transaction has to be killed.
Just a clarification.
Regards,
Goran

DB-LIB and SQL 2005

Hello,

I've an application developped in VC ++ 6.0 using Db library for bulk copying flat files records to a SQL server (SQL server 7) table. We're trying to migrate to MSSQL 2005. While performing tests, the bcp command fails telling that "The primary key constraint has been violated'. All other Db lib command works with no problem. I've even changed the format file (.fmt) in eliminating the Primary key field as well as in the flat file (source file). But the bcp_exec command succeeds with no problem if i delete the primary constraint. Where's the problem with this new version of SQL SERVER 2005 ?

Thanks for your help

Let me see if I understand you correctly.

You have an application that copies a flat file into a SQL Server 2005 table using bcp_exec in the DB-Lib. When trying to do this, you receive the error "The primary key constraint has been violated". If you delete the primary key constraint on the table, bcp_exec then succeeds. Is this correct? Did the table in SQL Server 7 contain the primary key constraint as well but imports worked anyways?

You probably know this, but I'll state it anyways -- primary keys must be unique, including that they cannot be set to NULL. If I understand correctly, simply deleting the columns from the flat file and the format file will not work as that would try to put a default value of NULL in the primary key column, something not allowed.

Does the table in SQL Server 7 already have unique values in this column? If so, I'm surprised that there would be a problem, unless there's a collision with the data already in the table on SQL Server 2005.

I would suggest comparing the schemas. If they are identical, including constraints, then see if there is something amiss in the data, a NULL or something else. If there isn't, then try looking at what's already in the SQL Server 2005 table and compare that to what's in the flat file. Is there a conflict or collision there preventing the import?

If none of these is the case, please reply and we can see what else we can find.

|||

Thanks a lot for your reply.

The sql server 7.0 table contains the primary key constraint and the bcp_exec via DB-Lib works fine. The format file references this primary key and in the flat file that column contains 0 for each record. The field separator is ;. The table was created on SQLSERVER 2005 using the same commands as for SQL 7.

CREATE TABLE [dbo].[Table1] (
[Field1] [int] IDENTITY (1, 1) NOT NULL ,
etc
etc...
)
GO

ALTER TABLE [dbo].[Table1] WITH NOCHECK ADD
PRIMARY KEY CLUSTERED
(
[Field1]
) ON [PRIMARY]
GO

As i've explained earlier that when i found that the bcp_exec did'nt work with SQL 2005, i've complety removed the primary key (field1 in our ex.) from the format file and the Zros from the flat file. Nothing doing... this time bcp exec fails with no message.

Thanks a lot again