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?On Jun 22, 8:17 am, Altman <balt...@.easy-automation.comwrote:
Quote:
Originally Posted by
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/|||Try using
select * from table (NOLOCK)
where xxxx = xxxx
This will not lock the database as it reads.
"Altman" <baltman@.easy-automation.comwrote in message
news:1182518265.867797.118630@.k79g2000hse.googlegr oups.com...
Quote:
Originally Posted by
>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?
>
Quote:
Originally Posted by
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/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||On Jun 23, 4:10 am, Erland Sommarskog <esq...@.sommarskog.sewrote:
Quote:
Originally Posted by
Oscar Santiesteban (o_santieste...@.bellsouth.net) writes:
Quote:
Originally Posted by
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/downloads/books...
Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/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.|||On Jun 26, 10:30 am, Altman <balt...@.easy-automation.comwrote:
Quote:
Originally Posted by
On Jun 23, 4:10 am, Erland Sommarskog <esq...@.sommarskog.sewrote:
>
>
>
Quote:
Originally Posted by
Oscar Santiesteban (o_santieste...@.bellsouth.net) writes:
Quote:
Originally Posted by
Try using
select * from table (NOLOCK)
where xxxx = xxxx
This will not lock the database as it reads.
>
Quote:
Originally Posted by
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.
>
Quote:
Originally Posted by
--
Erland Sommarskog, SQL Server MVP, esq...@.sommarskog.se
>
Quote:
Originally Posted by
Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/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
No comments:
Post a Comment