| View previous topic :: View next topic |
| Author |
Message |
Suj
Guest
|
 Posted: Fri Apr 15, 2005 2:23 pm Post subject: Deadlocks in SQL Server 2000 |
 |
|
We are running into a situation where the customers are getting many
deadlock errors. To troubleshoot the problem I set up a trace in sql
profiler and on analysing the trace file I noticed that each time the
deadlock is caused by an update staement on Table A and a select statement
on table A. It is always the same update and select statement. I am not sure
if I understand how a select and an update statement on the same table can
cause a deadlock.
Anyways to resolve the issue quickly I am going to add error handling to
trap the deadlock error and then instead of displaying the deadlock error I
would resubmit the select statement to SQL server again.
I was wondering if any of you could explain what could be the cause of this
deadlock and if there was a better way to handle this situation.
Thanks
|
|
| Back to top |
|
 |
Patrick
Guest
|
| Posted: Fri Apr 15, 2005 2:50 pm Post subject: Re: Deadlocks in SQL Server 2000 |
|
|
| Quote: | Anyways to resolve the issue quickly I am going to add error handling to
trap the deadlock error and then instead of displaying the deadlock error
I would resubmit the select statement to SQL server again.
I was wondering if any of you could explain what could be the cause of
this deadlock and if there was a better way to handle this situation.
|
Make sure you use the WITH (NOLOCK) directive on your selects (including
joins.)
|
|
| Back to top |
|
|
Thomas Steinmaurer
Guest
|
| Posted: Sat Apr 16, 2005 9:30 am Post subject: Re: Deadlocks in SQL Server 2000 |
|
|
| Quote: | Anyways to resolve the issue quickly I am going to add error handling to
trap the deadlock error and then instead of displaying the deadlock error
I would resubmit the select statement to SQL server again.
I was wondering if any of you could explain what could be the cause of
this deadlock and if there was a better way to handle this situation.
Make sure you use the WITH (NOLOCK) directive on your selects (including
joins.)
|
The problem with NOLOCK is that Dirty Reads (seeing uncommitted changes)
are allowed. Probably something one wants to avoid.
--
Best Regards,
Thomas Steinmaurer
LogManager Series - Logging/Auditing Suites supporting
InterBase, Firebird, Advantage Database, MS SQL Server and
NexusDB V2 (NEW!)
Upscene Productions
http://www.upscene.com
|
|
| Back to top |
|
|
|
FAQ
Search
Memberlist
Usergroups
Register
Profile
Log in to check your private messages
Log in
