|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
DeadlocksHi,
I have recently directed a query that used to run on my main server to another server that is a Pull Subscriber to a Transactional Replication from the main server. I now face deadlocks from time to time when the query is being run. The deadlocked process is the proccess executing the query. I never faced such a deadlock when the query was running on the main server. Any ideas? I was thinking to add either NOLOCK or set the transaction isolation level to READUNCOMITED. Yaniv Yaniv ,shalom
http://www.sql-server-performance.com/deadlocks.asp Show quote "Yaniv" <yan***@rediffmail.com> wrote in message news:O63ZcRGDGHA.376@TK2MSFTNGP10.phx.gbl... > Hi, > > I have recently directed a query that used to run on my main server to > another server that is a Pull Subscriber to a Transactional Replication > from the main server. > > I now face deadlocks from time to time when the query is being run. The > deadlocked process is the proccess executing the query. I never faced such > a deadlock when the query was running on the main server. > > Any ideas? > > I was thinking to add either NOLOCK or set the transaction isolation level > to READUNCOMITED. > > > > Yaniv > > > > NOLOCK or READ UNCOMMITTED will make your queries return incorrect results
at lightning speed. It would be better to find out what is wrong with the query and to fix it. READ COMMITTED SNAPSHOT is very good for reporting, but shouldn't be used to calculate modifications of the database because the results can become stale between the read and the write. It's too bad that it's not available in SQL 2000. READ COMMITTED is usually sufficient for reporting, unless aggregates are used, in which case you should use SERIALIZABLE. If your query is used to calculate modifications of the database, then READ COMMITTED is not sufficient. REPEATABLE READ is insufficient if the query used to calculate modifications of the database includes aggregates. All of these statements depend on how tolerant your queries (and users) are to error (in the scientific sense). For example, if you have 1 million rows and you're calculating an average, then it really doesn't matter if a few rows change, appear, or disappear during the calculation, so READ COMMITTED should be OK in that case. The bottom line is that the ISOLATION LEVEL should be determined on a case-by-case basis. Show quote "Yaniv" <yan***@rediffmail.com> wrote in message news:O63ZcRGDGHA.376@TK2MSFTNGP10.phx.gbl... > Hi, > > I have recently directed a query that used to run on my main server to > another server that is a Pull Subscriber to a Transactional Replication > from the main server. > > I now face deadlocks from time to time when the query is being run. The > deadlocked process is the proccess executing the query. I never faced such > a deadlock when the query was running on the main server. > > Any ideas? > > I was thinking to add either NOLOCK or set the transaction isolation level > to READUNCOMITED. > > > > Yaniv > > > > In my case I can afford to issue this query in READUNCOMITTED but thinking
again I find READPAST more suitable. As per finding the cause to the problem I have no progress yet. This query have been used for a long time on my main server without experiencing a dealock. ------------- ------------- Show quote "Brian Selzer" <br***@selzer-software.com> wrote in message news:exay9MHDGHA.1280@TK2MSFTNGP10.phx.gbl... > NOLOCK or READ UNCOMMITTED will make your queries return incorrect results > at lightning speed. It would be better to find out what is wrong with the > query and to fix it. > > READ COMMITTED SNAPSHOT is very good for reporting, but shouldn't be used > to calculate modifications of the database because the results can become > stale between the read and the write. It's too bad that it's not > available in SQL 2000. > > READ COMMITTED is usually sufficient for reporting, unless aggregates are > used, in which case you should use SERIALIZABLE. > > If your query is used to calculate modifications of the database, then > READ COMMITTED is not sufficient. > > REPEATABLE READ is insufficient if the query used to calculate > modifications of the database includes aggregates. > > All of these statements depend on how tolerant your queries (and users) > are to error (in the scientific sense). For example, if you have 1 > million rows and you're calculating an average, then it really doesn't > matter if a few rows change, appear, or disappear during the calculation, > so READ COMMITTED should be OK in that case. The bottom line is that the > ISOLATION LEVEL should be determined on a case-by-case basis. > > "Yaniv" <yan***@rediffmail.com> wrote in message > news:O63ZcRGDGHA.376@TK2MSFTNGP10.phx.gbl... >> Hi, >> >> I have recently directed a query that used to run on my main server to >> another server that is a Pull Subscriber to a Transactional Replication >> from the main server. >> >> I now face deadlocks from time to time when the query is being run. The >> deadlocked process is the proccess executing the query. I never faced >> such a deadlock when the query was running on the main server. >> >> Any ideas? >> >> I was thinking to add either NOLOCK or set the transaction isolation >> level to READUNCOMITED. >> >> >> >> Yaniv >> >> >> >> > >  |
|||||||||||||||||||||||
Other interesting topics