Deadlock transaction

[Altman]

I have a customer using our program with SQL server and is
occasionally getting a "Transactio n (process ID xxxxx) was deadlocked
on lock resources with another process and has been chosen as the
deadlock victim." From what they are telling me, there shouldn't be
any deadlock happening as they say this happens when they invoicing in
a different program that is accessing a different database. Also the
error is happening on an SQL Select from a view and this select is
then showing data in an HTML table for the user. I don't think this
view should need to lock anything, I just want to read the data. Is
there anything I can do to fix this?

[Alex Kuznetsov]

Re: Deadlock transaction

On Jun 22, 8:17 am, Altman <balt...@easy-automation.comw rote:

I have a customer using our program with SQL server and is
occasionally getting a "Transactio n (process ID xxxxx) was deadlocked
on lock resources with another process and has been chosen as the
deadlock victim." From what they are telling me, there shouldn't be
any deadlock happening as they say this happens when they invoicing in
a different program that is accessing a different database. Also the
error is happening on an SQL Select from a view and this select is
then showing data in an HTML table for the user. I don't think this
view should need to lock anything, I just want to read the data. Is
there anything I can do to fix this?

Read "Analyzing Deadlocks with SQL Server Profiler" in BOL.

http://sqlserver-tips.blogspot.com/

[Oscar Santiesteban]

Re: Deadlock transaction

Try using
select * from table (NOLOCK)
where xxxx = xxxx
This will not lock the database as it reads.


"Altman" <baltman@easy-automation.comw rote in message
news:1182518265 .867797.118630@ k79g2000hse.goo glegroups.com.. .

>I have a customer using our program with SQL server and is
occasionally getting a "Transactio n (process ID xxxxx) was deadlocked
on lock resources with another process and has been chosen as the
deadlock victim." From what they are telling me, there shouldn't be
any deadlock happening as they say this happens when they invoicing in
a different program that is accessing a different database. Also the
error is happening on an SQL Select from a view and this select is
then showing data in an HTML table for the user. I don't think this
view should need to lock anything, I just want to read the data. Is
there anything I can do to fix this?
>

[Erland Sommarskog]

Re: Deadlock transaction

Oscar Santiesteban (o_santiesteban @bellsouth.net) writes:

Try using
select * from table (NOLOCK)
where xxxx = xxxx
This will not lock the database as it reads.

This may on the other hand lead to that the query returns incorrect
results, which may even more seroius. There are situations where NOLOCK
is called for, but you need to understand the implications. If you
don't - don't try it.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Altman]

Re: Deadlock transaction

On Jun 23, 4:10 am, Erland Sommarskog <esq...@sommars kog.sewrote:

Oscar Santiesteban (o_santieste... @bellsouth.net) writes:

Try using
select * from table (NOLOCK)
where xxxx = xxxx
This will not lock the database as it reads.

>
This may on the other hand lead to that the query returns incorrect
results, which may even more seroius. There are situations where NOLOCK
is called for, but you need to understand the implications. If you
don't - don't try it.
>
--
Erland Sommarskog, SQL Server MVP, esq...@sommarsk og.se
>
Books Online for SQL Server 2005 athttp://www.microsoft.c om/technet/prodtechnol/sql/2005/downloads/books...
Books Online for SQL Server 2000 athttp://www.microsoft.c om/sql/prodinfo/previousversion s/books.mspx

I Think that the nolock will work for me. I understand the
implications and I think that my program will be able to handle it.
What I would've liked better was something like read committed but
didn't lock records.

[Alex Kuznetsov]

Re: Deadlock transaction

On Jun 26, 10:30 am, Altman <balt...@easy-automation.comw rote:

On Jun 23, 4:10 am, Erland Sommarskog <esq...@sommars kog.sewrote:
>
>
>

Oscar Santiesteban (o_santieste... @bellsouth.net) writes:

Try using
select * from table (NOLOCK)
where xxxx = xxxx
This will not lock the database as it reads.

>

This may on the other hand lead to that the query returns incorrect
results, which may even more seroius. There are situations where NOLOCK
is called for, but you need to understand the implications. If you
don't - don't try it.

>

--
Erland Sommarskog, SQL Server MVP, esq...@sommarsk og.se

>

Books Online for SQL Server 2005 athttp://www.microsoft.c om/technet/prodtechnol/sql/2005/downloads/books...
Books Online for SQL Server 2000 athttp://www.microsoft.c om/sql/prodinfo/previousversion s/books.mspx

>
I Think that the nolock will work for me. I understand the
implications and I think that my program will be able to handle it.
What I would've liked better was something like read committed but
didn't lock records.

If you are on 2005, consider snapshot isolation.

http://sqlserver-tips.blogspot.com