Deadlock transaction

I have a customer using our program with SQL server and is
occasionally getting a "Transaction (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?

Re: Deadlock transaction

On Jun 22, 8:17 am, Altman wrote:
> I have a customer using our program with SQL server and is
> occasionally getting a "Transaction (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/

Re: Deadlock transaction

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


"Altman" wrote in message
news:1182518265.867797.118630@k79g2000hse.googlegroups.com.. .
>I have a customer using our program with SQL server and is
> occasionally getting a "Transaction (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?
>

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@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx

Re: Deadlock transaction

On Jun 23, 4:10 am, Erland Sommarskog wrote:
> 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...@sommarskog.se
>
> Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/down loads/books...
> Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/boo ks.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.

Re: Deadlock transaction

On Jun 26, 10:30 am, Altman wrote:
> On Jun 23, 4:10 am, Erland Sommarskog wrote:
>
>
>
> > 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...@sommarskog.se
>
> > Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/down loads/books...
> > Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/boo ks.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